- Sql Server Execute Stored Procedure In Query
- Sql Execute Stored Procedure With Paramet…
- Ms Access Vba Execute Sql Stored Procedure
Active2 years, 2 months ago
Calling Stored Procedure while passing parameters from Access Module in VBA. Put stored procedure name here' cmd.Execute conn.Close Set conn = Nothing Set cmd. Calling the SQL Stored Procedure in MS Access. SQL Server Stored Procedures can be called via MS Access SQL Passthrough or SQL Execute queries via DAO (to set the SQL code as shown below).
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 withSet Result = CurrentDb.OpenRecordset('myProcName')
What I am trying to achieve is the following:
Sql Server Execute Stored Procedure In Query
- 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:
But how would I build my SQL definition?
does NOT work. If I try
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?
Francisco Couzo6,9193 gold badges24 silver badges34 bronze badges
lhiapgpeonklhiapgpeonk
1 Answer
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
ADO Parameterized Query
DAO Dynamic Pass-Through Query
Here, you can build the querydef's
.SQL
statement dynamically, but will not be parameterized:Native American flute has achieved some measure of fame for its distinctive sounds used in a variety of new age and world music recordings.
ParfaitParfaitSql Execute Stored Procedure With Paramet…
63.1k10 gold badges59 silver badges77 bronze badges