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

Private Sub CommandButton3_Click() '将连接字符串提取为常量 Const connStr 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 connStr

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) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'

'使用With语句来设置参数
With cmd
    .Parameters.Append .CreateParameter('value0', adVarChar, adParamInput, 255, Range('AG12').Value) '序号
    .Parameters.Append .CreateParameter('value1', adVarChar, adParamInput, 255, Range('AB15').Value) '试验编号
    .Parameters.Append .CreateParameter('value2', adVarChar, adParamInput, 255, Range('AA13').Value) '检测日期
    .Parameters.Append .CreateParameter('value3', adVarChar, adParamInput, 255, Range('F13').Value) 'zfj10up1
    .Parameters.Append .CreateParameter('value4', adVarChar, adParamInput, 255, Range('AB16').Value) '样品编号1
    .Parameters.Append .CreateParameter('value5', adVarChar, adParamInput, 255, Range('AB16').Value) '样品编号2
    .Parameters.Append .CreateParameter('value6', adVarChar, adParamInput, 255, Range('AB16').Value) '样品编号3
    .Parameters.Append .CreateParameter('value7', adVarChar, adParamInput, 255, Range('AG12').Value) '工程压力
    .Parameters.Append .CreateParameter('value8', adVarChar, adParamInput, 255, Range('L13').Value) 'zfj10up2
    .Parameters.Append .CreateParameter('value9', adVarChar, adParamInput, 255, Range('R13').Value) 'zfj10up3
    .Parameters.Append .CreateParameter('value10', adVarChar, adParamInput, 255, Range('F14').Value) 'zfj10down1
    .Parameters.Append .CreateParameter('value11', adVarChar, adParamInput, 255, Range('L14').Value) 'zfj10down2
    .Parameters.Append .CreateParameter('value12', adVarChar, adParamInput, 255, Range('R14').Value) 'zfj10down3
    .Parameters.Append .CreateParameter('value13', adVarChar, adParamInput, 255, Range('G13').Value) 'zfj30up1
    .Parameters.Append .CreateParameter('value14', adVarChar, adParamInput, 255, Range('M13').Value) 'zfj30up2
    .Parameters.Append .CreateParameter('value15', adVarChar, adParamInput, 255, Range('S13').Value) 'zfj30up3
    .Parameters.Append .CreateParameter('value16', adVarChar, adParamInput, 255, Range('G14').Value) 'zfj30down1
    .Parameters.Append .CreateParameter('value17', adVarChar, adParamInput, 255, Range('M14').Value) 'zfj30down2
    .Parameters.Append .CreateParameter('value18', adVarChar, adParamInput, 255, Range('S14').Value) 'zfj30down3
    .Parameters.Append .CreateParameter('value19', adVarChar, adParamInput, 255, Range('H13').Value) 'zfj50up1
    .Parameters.Append .CreateParameter('value20', adVarChar, adParamInput, 255, Range('N13').Value) 'zfj50up2
    .Parameters.Append .CreateParameter('value21', adVarChar, adParamInput, 255, Range('T13').Value) 'zfj50up3
    .Parameters.Append .CreateParameter('value22', adVarChar, adParamInput, 255, Range('H14').Value) 'zfj50down1
    .Parameters.Append .CreateParameter('value23', adVarChar, adParamInput, 255, Range('N14').Value) 'zfj50down2
    .Parameters.Append .CreateParameter('value24', adVarChar, adParamInput, 255, Range('T14').Value) 'zfj50down3
    .Parameters.Append .CreateParameter('value25', adVarChar, adParamInput, 255, Range('I13').Value) 'zfj70up1
    .Parameters.Append .CreateParameter('value26', adVarChar, adParamInput, 255, Range('O13').Value) 'zfj70up2
    .Parameters.Append .CreateParameter('value27', adVarChar, adParamInput, 255, Range('U13').Value) 'zfj70up3
    .Parameters.Append .CreateParameter('value28', adVarChar, adParamInput, 255, Range('I14').Value) 'zfj70down1
    .Parameters.Append .CreateParameter('value29', adVarChar, adParamInput, 255, Range('O14').Value) 'zfj70down2
    .Parameters.Append .CreateParameter('value30', adVarChar, adParamInput, 255, Range('U14').Value) 'zfj70down3
End With

cmd.Execute
conn.Close
Set conn = Nothing

End Sub