Using a SQL Server stored procedure with an insert query in Access

If you’re using a SQL Server database with an Access frontend, then this article is for you. Leveraging 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 across 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-through query once with dummy code, then do your insert query, then finalize 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 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.

Summary Steps
* 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
DoCmd.SetWarnings False
DoCmd.OpenQuery “qryCompleteImport”