Process SQL on a database server by using a pass-through query

You can use Access as a tool in which to create and modify a database and work with its data, but you can also use Access as a front end (interface) for a server database management system, such as Microsoft SQL Server. Generally, when you use Access as a front end application, you link to tables that are in the server database management system, and then use those linked tables as if they were located in the Access database. The Structured Query Language (SQL) processing is performed on your local machine by Access.

In some situations, however, you may want SQL processing to be performed by the server computer. For example, you may have a relatively slow desktop computer and a powerful database server computer, in which case, processing queries on the server computer may improve performance. Or, perhaps you want to run a stored procedure that is located on the server computer, which is not possible when Access processes SQL on your local machine. To process SQL on a database server computer, use a pass-through query. When you use a pass-through query, Access needs to send less data across the connection which can also improve overall performance.

Note: This article assumes you've already connected to a server database management system. For more information about linking to other data sources, see Introduction to importing, linking, and exporting data in Access.

Create a pass-through query

  1. On the Create tab, in the Queries group, click Query Design.

  2. Close the Show Table dialog box.

  3. On the Design tab, in the Query Type group, click Pass-Through.

    Access hides the query design grid and displays the SQL View object tab.

  4. If the query property sheet does not appear, press F4 to display it.

  5. On the property sheet, click the ODBC Connect Str property box, and then click Build Builder button .

    The Select Data Source dialog box appears.

  6. Click the Machine Data Source tab.

  7. Under Data Source Name, click the name of the server computer that you configured when you connected to your server database management system, and then click OK.

    Note: If you have not already configured an ODBC data source, click New, and then follow the steps in the Create New Data Source Wizard.

  8. If you are prompted to log on, enter your user name and password.

  9. If you are prompted to save your password in the connection string, do not save your password.

    Not saving your password helps keep your server database system more secure.

  10. Type your query in the SQL View object tab.

    Note: Remember to use SQL syntax that is appropriate for your database management system, which may differ from Access SQL.

  11. When you finish typing your query, on the Design tab, in the Results group, click Run.

    Your query is sent to the database server computer for processing.

    Notes: 

    • Some pass-through queries are not meant to return data. For example, you may want to run a stored procedure that does not return any data to Access, such as a script that grants database privileges to a group or a user. If your pass-through query is not meant to return data to Access, you should change the value of the Returns Records property (in the property sheet for the query) to No.

    • Some pass-through queries may return server processing messages to Access. If you want to collect these messages in a table for later review, change the value of the Log Messages property (in the property sheet for the query) to Yes. The name of the table that stores these messages is the user name concatenated with a hyphen (-) and a sequential number, starting with 00.

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×