How do I use QueryDef to get data from a pass-through query?

Question

I have a SQL pass-through query that returns me a table based on a variable in a "FROM... WHERE='variable'" structure. I want to use that pass-through query on a VBA code and get the variable from a form. In resume: I'll enter the variable on a form, run the VBA code with the pass through query and bring back the result to the same form. How do I do that?

Answer 1

Dim rst      As DAO.Recordset

With CurrentDb.QueryDefs("qryPass")
  .SQL = "select * from dbo.tblCustomers where City = '" & Me!City & "'"
  .ReturnsRecords = True
  Set rst = .OpenRecordset
End With

The above would use the current forms text box called City. To prevent SQl injection, then you could use this:

  .SQL = "select * from dbo.tblCustomers where City = " & qu(me!City)

And you have a general purpose routine to place quotes around the expression with:

Function qu(vText As Variant) As String

  qu = Chr$(34) & vText & Chr$(34)

  qu = Replace(qu, ";", "")
  qu = Replace(qu, "(", "")
  qu = Replace(qu, ")", "")
  qu = Replace(qu, "=", "")

End Function