VBA 代码优化：使用参数化查询将数据插入 Access 数据库

Private Sub CommandButton3_Click() '将连接字符串提取为常量 Const strConn As String = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\检测设备\门窗\zwh.mdb'

Dim conn As ADODB.Connection Set conn = New ADODB.Connection conn.Open strConn

Dim cmd As ADODB.Command Set cmd = New ADODB.Command Set cmd.ActiveConnection = conn cmd.CommandText = 'insert into 气密原始记录负压1(序号,试验编号,检测日期,zfj10up1,样品编号1,样品编号2,样品编号3,zfj10up2,zfj10up3,zfj10down1,zfj10down2,zfj10down3,zfj30up1,zfj30up2,zfj30up3,zfj30down1,zfj30down2,zfj30down3,zfj50up1,zfj50up2,zfj50up3,zfj50down1,zfj50down2,zfj50down3,zfj70up1,zfj70up2,zfj70up3,zfj70down1,zfj70down2,zfj70down3,zfj100up1,zfj100up2,zfj100up3,zfj100down1,zfj100down2,zfj100down3,zfj1501,zfj1502,zfj1503,zfj10avg1,zfj10avg2,zfj10avg3,zfj30avg1,zfj30avg2,zfj30avg3,zfj50avg1,zfj50avg2,zfj50avg3,zfj70avg1,zfj70avg2,zfj70avg3,zfj100avg1,zfj100avg2,zfj100avg3,zfj150avg1,zfj150avg2,zfj150avg3) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'

'使用参数化查询，避免SQL注入攻击 cmd.Parameters.Append cmd.CreateParameter('value0', adVarChar, adParamInput, 255, Range('AG12').Value) '序号 cmd.Parameters.Append cmd.CreateParameter('value1', adVarChar, adParamInput, 255, Range('AB15').Value) '试验编号 cmd.Parameters.Append cmd.CreateParameter('value2', adVarChar, adParamInput, 255, Range('AA13').Value) '检测日期 cmd.Parameters.Append cmd.CreateParameter('value3', adVarChar, adParamInput, 255, Range('F13').Value) 'zfj10up1 cmd.Parameters.Append cmd.CreateParameter('value4', adVarChar, adParamInput, 255, Range('AB16').Value) '样品编号1 cmd.Parameters.Append cmd.CreateParameter('value5', adVarChar, adParamInput, 255, Range('AB16').Value) '样品编号2 cmd.Parameters.Append cmd.CreateParameter('value6', adVarChar, adParamInput, 255, Range('AB16').Value) '样品编号3 cmd.Parameters.Append cmd.CreateParameter('value7', adVarChar, adParamInput, 255, Range('AG12').Value) '工程压力 cmd.Parameters.Append cmd.CreateParameter('value8', adVarChar, adParamInput, 255, Range('L13').Value) 'zfj10up2 cmd.Parameters.Append cmd.CreateParameter('value9', adVarChar, adParamInput, 255, Range('R13').Value) 'zfj10up3 cmd.Parameters.Append cmd.CreateParameter('value10', adVarChar, adParamInput, 255, Range('F14').Value) 'zfj10down1 cmd.Parameters.Append cmd.CreateParameter('value11', adVarChar, adParamInput, 255, Range('L14').Value) 'zfj10down2 cmd.Parameters.Append cmd.CreateParameter('value12', adVarChar, adParamInput, 255, Range('R14').Value) 'zfj10down3 cmd.Parameters.Append cmd.CreateParameter('value13', adVarChar, adParamInput, 255, Range('G13').Value) 'zfj30up1 cmd.Parameters.Append cmd.CreateParameter('value14', adVarChar, adParamInput, 255, Range('M13').Value) 'zfj30up2 cmd.Parameters.Append cmd.CreateParameter('value15', adVarChar, adParamInput, 255, Range('S13').Value) 'zfj30up3 cmd.Parameters.Append cmd.CreateParameter('value16', adVarChar, adParamInput, 255, Range('G14').Value) 'zfj30down1 cmd.Parameters.Append cmd.CreateParameter('value17', adVarChar, adParamInput, 255, Range('M14').Value) 'zfj30down2 cmd.Parameters.Append cmd.CreateParameter('value18', adVarChar, adParamInput, 255, Range('S14').Value) 'zfj30down3 cmd.Parameters.Append cmd.CreateParameter('value19', adVarChar, adParamInput, 255, Range('H13').Value) 'zfj50up1 cmd.Parameters.Append cmd.CreateParameter('value20', adVarChar, adParamInput, 255, Range('N13').Value) 'zfj50up2 cmd.Parameters.Append cmd.CreateParameter('value21', adVarChar, adParamInput, 255, Range('T13').Value) 'zfj50up3 cmd.Parameters.Append cmd.CreateParameter('value22', adVarChar, adParamInput, 255, Range('H14').Value) 'zfj50down1 cmd.Parameters.Append cmd.CreateParameter('value23', adVarChar, adParamInput, 255, Range('N14').Value) 'zfj50down2 cmd.Parameters.Append cmd.CreateParameter('value24', adVarChar, adParamInput, 255, Range('T14').Value) 'zfj50down3 cmd.Parameters.Append cmd.CreateParameter('value25', adVarChar, adParamInput, 255, Range('I13').Value) 'zfj70up1 cmd.Parameters.Append cmd.CreateParameter('value26', adVarChar, adParamInput, 255, Range('O13').Value) 'zfj70up2 cmd.Parameters.Append cmd.CreateParameter('value27', adVarChar, adParamInput, 255, Range('U13').Value) 'zfj70up3 cmd.Parameters.Append cmd.CreateParameter('value28', adVarChar, adParamInput, 255, Range('I14').Value) 'zfj70down1 cmd.Parameters.Append cmd.CreateParameter('value29', adVarChar, adParamInput, 255, Range('O14').Value) 'zfj70down2 cmd.Parameters.Append cmd.CreateParameter('value30', adVarChar, adParamInput, 255, Range('U14').Value) 'zfj70down3 cmd.Parameters.Append cmd.CreateParameter('value31', adVarChar, adParamInput, 255, Range('J13').Value) 'zfj100up1 cmd.Parameters.Append cmd.CreateParameter('value32', adVarChar, adParamInput, 255, Range('O13').Value) 'zfj100up2 cmd.Parameters.Append cmd.CreateParameter('value33', adVarChar, adParamInput, 255, Range('V13').Value) 'zfj100up3 cmd.Parameters.Append cmd.CreateParameter('value34', adVarChar, adParamInput, 255, Range('J14').Value) 'zfj100down1 cmd.Parameters.Append cmd.CreateParameter('value35', adVarChar, adParamInput, 255, Range('O14').Value) 'zfj100down2 cmd.Parameters.Append cmd.CreateParameter('value36', adVarChar, adParamInput, 255, Range('V14').Value) 'zfj100down3 cmd.Parameters.Append cmd.CreateParameter('value37', adVarChar, adParamInput, 255, Range('K13').Value) 'zfj1501 cmd.Parameters.Append cmd.CreateParameter('value38', adVarChar, adParamInput, 255, Range('Q13').Value) 'zfj1502 cmd.Parameters.Append cmd.CreateParameter('value39', adVarChar, adParamInput, 255, Range('W13').Value) 'zfj1503 cmd.Parameters.Append cmd.CreateParameter('value40', adVarChar, adParamInput, 255, Range('F15').Value) 'zfj10avg1 cmd.Parameters.Append cmd.CreateParameter('value41', adVarChar, adParamInput, 255, Range('L15').Value) 'zfj10avg2 cmd.Parameters.Append cmd.CreateParameter('value42', adVarChar, adParamInput, 255, Range('R15').Value) 'zfj10avg3 cmd.Parameters.Append cmd.CreateParameter('value43', adVarChar, adParamInput, 255, Range('G15').Value) 'zfj30avg1 cmd.Parameters.Append cmd.CreateParameter('value44', adVarChar, adParamInput, 255, Range('M15').Value) 'zfj30avg2 cmd.Parameters.Append cmd.CreateParameter('value45', adVarChar, adParamInput, 255, Range('S15').Value) 'zfj30avg3 cmd.Parameters.Append cmd.CreateParameter('value46', adVarChar, adParamInput, 255, Range('H15').Value) 'zfj50avg1 cmd.Parameters.Append cmd.CreateParameter('value47', adVarChar, adParamInput, 255, Range('N15').Value) 'zfj50avg2 cmd.Parameters.Append cmd.CreateParameter('value48', adVarChar, adParamInput, 255, Range('T15').Value) 'zfj50avg3 cmd.Parameters.Append cmd.CreateParameter('value49', adVarChar, adParamInput, 255, Range('I15').Value) 'zfj70avg1 cmd.Parameters.Append cmd.CreateParameter('value50', adVarChar, adParamInput, 255, Range('O15').Value) 'zfj70avg2 cmd.Parameters.Append cmd.CreateParameter('value51', adVarChar, adParamInput, 255, Range('U15').Value) 'zfj70avg3 cmd.Parameters.Append cmd.CreateParameter('value52', adVarChar, adParamInput, 255, Range('J15').Value) 'zfj100avg1 cmd.Parameters.Append cmd.CreateParameter('value53', adVarChar, adParamInput, 255, Range('O15').Value) 'zfj100avg2 cmd.Parameters.Append cmd.CreateParameter('value54', adVarChar, adParamInput, 255, Range('V15').Value) 'zfj100avg3 cmd.Parameters.Append cmd.CreateParameter('value55', adVarChar, adParamInput, 255, Range('K15').Value) 'zfj150avg1 cmd.Parameters.Append cmd.CreateParameter('value56', adVarChar, adParamInput, 255, Range('Q15').Value) 'zfj150avg2 cmd.Parameters.Append cmd.CreateParameter('value57', adVarChar, adParamInput, 255, Range('W15').Value) 'zfj150avg3

cmd.Execute conn.Close Set conn = Nothing End Sub