Go to Top

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

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.

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

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.

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”

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
[gravityform id="16" title="false" description="false"]