If you’re using a SQL Server database with an Access frontend, than this article is for you. Leverging the power of Stored Procedures from Access has numerous benefits: your server can perform transactions that would take far longer in Access. Recently I came accross the following issue when working on a client’s database.
Stored Procedures with Action Queries can be a problem
I created a stored procedure that does multiple updates using action queries, and I wanted the results of the procedure returned back to Access so that the user can now how many records were updated. I wanted the results inserted into a local Access table for a historical reference, and I had decided on using a pass-through query with another insert query in Access to accomplish the task. The insert query would kick off the pass-through query that would in turn kick off the stored procedure on the server.
Pass-Through Query + Insert: A dangerous combo!
I used this Microsoft article to create the pass through query in my code. (If your stored procedure does not take arguments then you don’t have to this, rather, just create the pass-throgh query once with dummy code, then do your insert query, then finalyze your pass-through). Then I tried using the QBE window in Access, but in doing so the system wanted to execute my pass-through query to get the column names from my stored procedure. Not good, since it would kick-off my action queries on the server and cause havoc to my data! Instead I wrote my insert insert query using SQL view in Access, when I went to save the query it still wanted to execute my pass-through, but I had not saved the connection string, so there was no harm done.
* Create a dummy pass-through query in Access that will NOT execute the stored procedure, don’t bother with a connection string at this point.
* Create an insert query using the SQL mode in access, not the Query by Example grid.
* Modify your code to create the pass-through query on the fly if needed. (You will need to do so if your stored procedure requires parameters)
* Kick off the whole process in your code by first creating the pass-through and then executing the insert query:
CreateSPT “qrySQLCompleteImport”, “Exec sp_CompleteImport ” & Me.txtFranchiseID, strSQL