Parameterized query in MS Access 2003 using vba

Question

Ok I want to use parameterized queries to avoid using inline double or single quotes ("or") in my data.

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

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

I did not cut or paste this from my code (in another window right now), so there may be a typo.

As soon as I find out this simple example, I need to move on to more complex operators (a few parameters and associations). 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 very useful, because what are you going to do with the request now? Please note that you can store parameter requests and assign parameters in VBA. Also note that memo fields are becoming a problem because the parameter can only accept 255 characters.

Answer 2

The only problem with using the Replace function is that wherever there is ", it is replaced by" ", even if you have already qualified a single quote with another separate quote after it:" "" becomes "" '' '( and so on).

You can create a procedure or function to check the strings "[!" ] and replace those used with Like:

Public function QualifySingleQuote (myStr as string) As String

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

Final function