Microsoft Access Pass Through Query and SQL Server Stored Procedures

A Microsoft Access Pass Through query is a great way to harness the power of SQL Server stored procedures.  You can actually use a pass through query with any ODBC-compliant database such as Oracle, MySql, SQL Server… passing most any valid T-SQL statement and of course a common T-SQL statement, in the case of stored procedures, is to simply have it contain the name of a stored procedure with or without parameters.

This type of query is not as common as the regular Access query since it’s not as intuitive and doesn’t support the Query By Example interface. You must use the SQL query view and type (or paste) the SQL in yourself. To create a pass through query in Access simply select Query Design and set he query type to Pass Through and then you see the SQL query view window.

If you right-click in the SQL query view window and click Properties, you’ll see the ODBC Connect Str Property. Pass through queries use ODBC to execute the queries against the back-end server (such as SQL Server or Oracle) so you need to setup the connection string in the query’s property window.

If the stored procedure accepts or requires parameters it is common to edit the pass through query definition on the fly in VBA to add the parameter values dynamically similar to the below.

Set qd = CurrentDb.QueryDefs(“MSSQL_Sproc_GetCustomerAging”)
qd.SQL = “sp_GetCustomerAging, 12345”   <<example passing a CustomerID to the stored procedure >>
qd.Connect = sMSSQLConnect   <<ODBC connection string stored in an Access VBA variable >>
qd.Close

You can then execute the above Access pass through query and it will bring back aging information for customer 12345.