How to avoid VBA errors with single quotes in a string being passed to a SQL Insert or Update Error. Includes how to passing parameters for an unknown or variable number of variables.
Microsoft Access, SQL Server, ADO
I hadn’t used this in a few years. When I ran across the problem I did a quick google search and found the solution – here on this blog. However, I give all the code here and expand upon the solution.
I build a data import and cleanup routine for a client – data eventually bound for a PowerBI dashboard. As part of the solution, my client needs to consolidate records where data (names) are similar but not exact. Basically, in a given import of several thousand records, it is possible that, “ABC Company” was also imported as, “ABC Co” and even, “ABC Company, Inc.”
I created a dual listbox where my client select the master record and then can multi-select one or more records they want consolidated. Consolidated really mean updated to match the master record.
As it turns out, some of the records might have been imported with apostrophes in the name, as in, “Jim’s Automotive”. Apostrophes and building SQL String in MS Access can be problematic. Rather than doubling, tripling, or “‘”‘””‘”‘”” to fix this, passing parameters to SQL is a better method. Sticklers will tell you parameters help you avoid exposure to SQL injection attacks. Not really much of a concern for this utility – but duly noted.
Because the client can select multiple records in my listbox, I needed a way to pass a dynamic number of parameters. Here is the code with some explanation. If this end up helping you, let me know.
The form actually has three listboxes. There are actually two different fields that may need consolidation.
Listbox 1: lstConsolidateField (select which field you are consolidating)
ListBox 2: lstToMatch (when you select field to consolidate, this displays unique entries from that field)
ListBox 3: lstToConsolidate (also displays unique entries based on the field selected in ListBox 1 (lstConsolidateField)
Passing Parameters from MS Access to SQL
I’m not going into a big explanation. Read the other article (linked above). In short, you pass parameters to a SQL command using ADO simply by including a “? in the SQL command. You then pass an array of values, in the same order as the “?” and those question marks are replaced with the parameters in the array.
If you have a SQL string:
“Update tblTableName set Field1 = ?, Field2 = ? where field3 = ?”
You could create an array:
aryParms = Array(“John”,”Manufacturing”,2)
Use the ADO command execute
Set rs = cmd.execute( ,aryParms)
The resulting SQL will be
“Update tblTableName set Field1 = ‘John’, Field2 = ‘Manufacturing’ where field3 = 2”
In the code below, because the listbox, lstToConsolidate, is multi-select, the where string could be something like:
“WHERE import_ID = ? and (custname = ? OR custname = ? or custname = ? or custname = ?)”
Passing parameters in order using an array becomes an elegant solution for dynamically built queries.
'retrieve the field we are consolidating strConField = Me.lstConsolidateField.Column(0) 'retrieve the value for the master field - the field value that is being matched strToMatch = Me.lstToMatch.Column(0) 'intImportID is the import ID of a given set of records intImportID = Me.import_ID ' the number of items selected. I dimension a blank array and then redim to this size. ' note: I pass the name of the field to match as a parameter as well, so the number of parameters is ' one greater than the ItemsSelected.Count property below. Because arrays are base 0, this works. intArySz = Me.lstToConsolidate.ItemsSelected.Count Dim aryParms() ReDim aryParms(intArySz) aryParms(0) = strToMatch ap = 1 ' loop through the selected items, building a where clause with a "field = ? OR field = ? or field = ?" string For Each sel In Me.lstToConsolidate.ItemsSelected If Len(strToCon) > 0 Then strToCon = strToCon & " OR " End If strPrompt = strPrompt & Me.lstToConsolidate.ItemData(sel) & vbCrLf aryParms(ap) = Me.lstToConsolidate.ItemData(sel) ap = ap + 1 strToCon = strToCon & strConField & " = ?" Next strPrompt = strPrompt & "===========================" & vbCrLf & _ "will be updated to:" & vbCrLf & strToMatch & vbCrLf & "DO YOU WANT TO CONTINUE?" chc = MsgBox(strPrompt, vbYesNo, "Consolidate Records") If chc = 6 Then strSQL = "Update tblImportMaster SET " & strConField & " = ? WHERE Import_ID = " & intImportID & " AND (" & strToCon & ")" Dim cn As ADODB.Connection Dim cmd As ADODB.Command Dim rs As ADODB.Recordset Set cn = CurrentProject.Connection Set cmd = New ADODB.Command cmd.ActiveConnection = cn cmd.CommandType = adCmdText cmd.CommandText = strSQL Set rs = cmd.Execute(, aryParms) Set rs = Nothing Set cmd = Nothing Set cn = Nothing End If
Once again, let me know if this help you. And if you are so inclined, consider sharing with a friend or two – or 1,000.