VBA 代码优化：使用参数化查询更新数据库

Dim conn As Object Set conn = CreateObject('ADODB.Connection') conn.Open 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\检测设备\力学\VBA测试' & destinationDatabase

' 设置SQL语句和参数 Dim strSQL As String, paramName As String, paramValue As String paramName = '试样编号' paramValue = Range('J6').Value strSQL = 'UPDATE UserParam SET ParamValue=? WHERE ParamName=?' ' 执行SQL语句 With CreateObject('ADODB.Command') .ActiveConnection = conn .CommandType = adCmdText .CommandText = strSQL .Parameters.Append .CreateParameter('ParamValue', adVarChar, adParamInput, Len(paramValue), paramValue) .Parameters.Append .CreateParameter('ParamName', adVarChar, adParamInput, Len(paramName), paramName) .Execute End With

paramName = '试样初始厚度h0 [mm]' paramValue = Range('F13').Value ' 设置SQL语句和参数 With CreateObject('ADODB.Command') .ActiveConnection = conn .CommandType = adCmdText .CommandText = strSQL .Parameters.Append .CreateParameter('ParamValue', adVarChar, adParamInput, Len(paramValue), paramValue) .Parameters.Append .CreateParameter('ParamName', adVarChar, adParamInput, Len(paramName), paramName) .Execute End With

paramName = '受压面宽度b [mm]' paramValue = Range('J6').Value ' 设置SQL语句和参数 With CreateObject('ADODB.Command') .ActiveConnection = conn .CommandType = adCmdText .CommandText = strSQL .Parameters.Append .CreateParameter('ParamValue', adVarChar, adParamInput, Len(paramValue), paramValue) .Parameters.Append .CreateParameter('ParamName', adVarChar, adParamInput, Len(paramName), paramName) .Execute End With

paramName = '受压面长度L [mm]' paramValue = Range('E13').Value ' 设置SQL语句和参数 With CreateObject('ADODB.Command') .ActiveConnection = conn .CommandType = adCmdText .CommandText = strSQL .Parameters.Append .CreateParameter('ParamValue', adVarChar, adParamInput, Len(paramValue), paramValue) .Parameters.Append .CreateParameter('ParamName', adVarChar, adParamInput, Len(paramName), paramName) .Execute End With

' 关闭数据库连接 conn.Close