MS Access – Passing a Variable and Unknown Number of Parameters to SQL Server

Summary

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.

Technologies

Microsoft Access, SQL Server, ADO

Something funny

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.

The problem

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.

Three listboxes

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.

Posted in Microsoft Access, SQL Server, Tips and Tools and tagged , , .

Leave a Reply

Your email address will not be published. Required fields are marked *