Creating Pass-Through queries in Access 2003
A pass-through query is one where your command is passed from MS Access over to the server to execute. Sounds a little tricky, I know, but you’ll get the hang of it.
OK, lets start with the scenario that you want to access a stored procedure (sometimes called a sproc) called sp_this_one on a SQL Server somewhere in your organisation.
In order to run this sproc you’d normally issue the command EXEC sp_this_one in Query Analyser or some such tool.
In order to run this sproc in access, you just need to make a pass-through query with that exec statement. Easy as.
Assuming you’ve already set up a Data Source Name (or DSN) to your SQL Server’s database (don’t worry, I’ll be writing about how to do that in another post) the process is straightforward.
- Create a query
- Close the Tables box
- Go to the Query Menu
- Select SQL Specific > Pass-Through Query
- Click the Properties button on the toolbar
- ODBC Connection String
- … button
- Select data source
And that’s it. All you need to do then is type into the query window the command you’d like to pass through to the server. In our case EXEC sp_this_one.
I realise I’ve been using some rather broad strokes in these instructions, please feel free to email me if you’d like a slightly better description.
