Going further, passing parameters (1st attempt)

[Marc Ozin]

OK, now I've got that working here's a quick and nasty way to pass parameters using vba to our Pass Through Query


In the pass though query above the following sql statement is run:
CALL spTest(123,456);

I now want to be able to call this query using parameters instead of the fixed values 123 and 456.

The only way i can figure out how to do this so far is for access to edit the sql for me.

I start with editing the SQL in the query, putting my parameter names in so it now looks like this:
Call spTest([PARAM1],[PARAM2]);

I know that's not valid MySQL syntax but bear with me.

Next, as a proof of concept, I've created a sub in MS Access that will pass parameters to the Pass-through and display the results.

here it is:



Public Sub QParamsDAO(ByVal sQueryName As String, _
ByVal sParamList As String, _
Optional sDelim As String = "|", _
Optional vConnect As Variant, _
Optional bRetRecords As Boolean = True)

' sQueryName : name of query to modify
' sParamList : list of parameters, followed by their values seperated by |
' sDelim : Optional, change the | delimiter to something different
' vConnect : Optional connection string
' bRetRecords : Optional set return records on / off. Default: on

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim sConnect As String
Dim aParameters
Dim sOriginalSQL As String
Dim iLoop As Integer

aParameters = Split(sParamList, sDelim)

Set db = CurrentDb
Set qdf = db.querydefs(sQueryName)
If IsMissing(vConnect) Then
sConnect = qdf.Connect
Else
sConnect = CStr(vConnect)
End If
sOriginalSQL = qdf.SQL

'replace the parameters with values
For iLoop = LBound(aParameters) To UBound(aParameters) - 1 Step 2
qdf.SQL = Replace(qdf.SQL, aParameters(iLoop), aParameters(iLoop + 1))
Next iLoop

qdf.Connect = sConnect
qdf.ReturnsRecords = bRetRecords

'do something
DoCmd.OpenQuery sQueryName
MsgBox "click OK when done"
DoCmd.Close acQuery, sQueryName

'change everything back to normal
qdf.SQL = sOriginalSQL

End Sub


This Sub will edit the SQL, replacing text given to it in a list.
The list is delimted by default with "|" characters and should be written like this:
ParameterName1|Value1|ParameterName2|Value2|ParameterName3|Val...


I've written it using DAO, so you'll have to go into (in the VB editor): Tools > references > Microsoft DAO 3.6 Object Library.


to use the Sub above with my Q_spTest passthrough query call it like this:
QParamsDAO "Q_spTest", "[PARAM1]|123|[PARAM2]|456"

You'll notice the parameter names have square brackets round them. I've done this as a precaution on the replace as the vba replace statement will replace that text anywhere, not just complete words.

e.g. replace("Text anywhere an problem", "an", "a")
result: "Text aywhere a problem" - notice it replaced 2 instances of "an"!

NB: there is NO error checking on the sub above. It's just a proof of concept.
You should definately put some in, to at least change the sql back to normal if anything goes wrong. Also you should make sure that your delimiter is not present in values you want to pass!

Security should be thought about too. If not checked, it's possible malicious use could cause unwanted SQL statements to be executed on the MySQL server!!!

All the best

Marc