Execute MySQL stored procedure with parameters in Access frontend via VBA

Question

So, database hotchpotch.

I have a MySQL database which has stored procedures.
I have an Access document which is linked to the MySQL database (I can survey tables) serving as a frontend (with forms and stuff)
I have successfully created a ODBC Pass Through Query (I think that's what they are called) to call one of the stored procedures from the database. The query in Access actually is just CALL myProcName; and gives me the results I expect when I call it from VBA with Set Result = CurrentDb.OpenRecordset("myProcName")

What I am trying to achieve is the following:

Have a stored procedure in MySQL that takes one parameter => check!
Have that stored procedure working as expected => checked in phpMyAdmin => check!
Have a saved query in Access which links to this parametric stored procedure with a fixed parameter value given => check! (CALL myProcName('myParameterValue'), I can call that from VBA just as I do the unparametric query)
Have the ability to specify 'myParameterValue' everytime I execute the query => not check

I need to somehow specify a parameter placeholder in the SQL definition of the saved query and set that parameter in VBA. For the VBA part I have and idea I would find rather elegant:

Private Sub ParametricQuery()
  Dim QDef As QueryDef
  Set QDef = CurrentDb.QueryDefs("myProcName")
  QDef.Parameters(*insert parameter name here*) = parameter value
  Dim Result As Recordset
  Set Result = QDef.OpenRecordset

  Do While Not Result.EOF
    MsgBox Result.Fields(1) 'Display a field from the results
  Loop
End Sub

But how would I build my SQL definition?

PARAMETERS in_param TEXT;
CALL myProcName(in_param);

does NOT work. If I try

Set QDef = CurrentDb.QueryDefs("myProcName")
MsgBox QDef.Parameters.Count

I get a Messagebox telling me there is a total of 0 parameters in my query definition, so that doesn't work.

What I have found online is a lot of people building the actual SQL in VBA via string operations. That makes me shudder for so many reasons (security, maintainability and elegance among them). I firmly believe that there is a better way, hopefully along the lines I have sketched above. The only problem is: How to do it?

Can you post the CREATE PROCEDURE line in MySQL (no need for whole proc)? The named param and its type is important to note. — Parfait
– Parfait, Commented Nov 27, 2016 at 15:17
Hmmm I created the procedure via the rather nice "create procedure" interface in myphpadmin. It has a parameter of direction "IN" (obviously) with type VARCHAR and length 254 (max length of email adress). And the procedure does a simple SELECT * FROM table WHERE field=param — lhiapgpeonk
– lhiapgpeonk, Commented Nov 27, 2016 at 16:14

Parfait · Accepted Answer · 2016-11-27 16:55:50Z

2

Consider using ADO for a parameterized query to call your stored procedure. Currently, you are using DAO (Access' default database API) to access a pass-thru query (a saved Access querydef). However, this type of query does not see anything in the frontend, only the backend RDMS particularly the MySQL SQL dialect and its connected database objects. Hence, you cannot bind local parameter values to it. And PARAMETERS clause is only part of the Access SQL dialect and will fail MySQL syntax.

MySQL Stored Procedure

CREATE PROCEDURE `mystoredproc`(IN param VARCHAR(254))
BEGIN
   SELECT * FROM table WHERE field=param;
END

ADO Parameterized Query

Public Sub CallMySQLProc()    
    Dim conn As Object, cmd As Object, rst As Object
    Const adCmdStoredProc = 4, adParamInput = 1, adVarChar = 200

    Set conn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")

    ' DSN-LESS CONNECTION
    conn.Open "Driver={MySQL ODBC 5.3 Unicode Driver};host=hostname;database=databasename;" _
                & "UID=username;PWD=****"

    ' CONFIGURE ADO COMMAND
    Set cmd = CreateObject("ADODB.Command")
    With cmd
        .ActiveConnection = conn
        .CommandText = "mystoredproc"
        .CommandType = adCmdStoredProc
        .CommandTimeout = 15
    End With

    ' APPEND NAMED PARAM
    cmd.Parameters.Append cmd.CreateParameter("param", adVarChar, _
                                              adParamInput, 254, "[email protected]")
    Set rst = cmd.Execute

    ' FREE RESOURCES
    rst.Close
    Set rst = Nothing
    Set cmd = Nothing
    Set conn = Nothing

End Sub

DAO Dynamic Pass-Through Query

Here, you can build the querydef's .SQL statement dynamically, but will not be parameterized:

Private Sub ParametricQuery()
  Dim QDef As QueryDef
  Dim Result As Recordset

  Set QDef = CurrentDb.QueryDefs("PassThruQ")
  QDef.SQL = "CALL mystoredproc('[email protected]')"

  Set Result = QDef.OpenRecordset

  Do While Not Result.EOF
    Debug.Print Result.Fields(1)    'Print to immediate window a field from the results
  Loop

  Result.close
  Set Result = Nothing
  Set QDef = Nothing
End Sub

answered Nov 27, 2016 at 16:55

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

lhiapgpeonk Over a year ago

OK, so it cannot be done easily :). Is there a way to avoid giving the connectionstring in code? Acces knows how to connect to the database, it can show me the tables. So why would I need to specify the password again and in plain sight for everyone? How safe is dynamically building SQL? I see a potential for loads of SQL injections, which I'm trying to avoid. Doing things (hopefully) properly and stuff. but thanks so far!

Parfait Over a year ago

Simply use a file DSN and no credentials are shown in code. You can build one (stored text file) tailored to your MySQL database in same dialog of ODBC pass through or External Tools / ODBC or obdcad32.exe. FYI - ADO would probably be the only way in VBA to run paramaterized query!

lhiapgpeonk Over a year ago

Just having read the article on DSN: I have specified my database as a ODBC datasource in window's control panel, that's where Access knows how to connect to the database from, so that should be a System DSN. Any chance I can use that in VBA? The info is there alright...

Parfait Over a year ago

Then just specify it in connection string: conn.Open "DSN=DSN_Name"

lhiapgpeonk Over a year ago

Sorry for getting back to you so late, it works! Thank you very much!

Collectives™ on Stack Overflow

Execute MySQL stored procedure with parameters in Access frontend via VBA

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related