parameterized query in ms access 2003 using vba

Question

Ok. I want to use parameterized queries to avoid dealing with embedded double or single quotes (" or ') in my data.

As a simple example, what would the VBA code look like for the parameterized verion of this?

Dim qstr as String

Dim possiblyDangerousString as String

qstr = "SELECT MyTable.LastName from MyTable WHERE MyTable.LastName = '" & possiblyDangerousString & "';"

I did not cut and paste this from my code (on a different box right now), so there might be a typo.

Once I figure out this simple example, I need to move on to more complex statements (multiple parameters and joins). Thanks for any advice

Answer 1

In VBA, you can use something like:

Dim db As DAO.Database
Dim qdf As QueryDef
Dim strSQL as String

Set db = CurrentDb
strSQL = "PARAMETERS txtLastName Text(150); " _
    & "SELECT LastName FROM MyTable " _
    & "WHERE LastName=txtLastName"

''Create a temporary query 
Set qdf = db.CreateQueryDef("", strSQL)

qdf.Parameters!txtLastName = Trim(possiblyDangerousString)

This example is not much use, because what are you going to do with the query now? Note that you can store parameter queries and assign the parameters in VBA. Note also that memo fields become a problem because a parameter can only accept 255 characters.

Answer 2

Consider whether doubling up any single quotes within your possiblyDangerousString would work for you as a simple alternative to parameterized queries.

qstr = "SELECT MyTable.LastName from MyTable WHERE MyTable.LastName = '" & _
Replace(possiblyDangerousString,"'","''") & "';"

Answer 3

The only trouble with using the Replace function is that anywhere there is "'" it will replace with "''" even if you have already qualified the single quotation with another single quotation after it: "''" becomes "''''" (and so on).

You could create a procedure or function to check for "'[!']" strings and replace those using a Like:

Public Function QualifySingleQuote(myStr as string) As String

If myStr Like "*'[!']*" Then
    QualifySingleQuote = Replace(myStr, "'", "''")
Else
    QualifySingleQuote = myStr
EndIf

End Function