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

You can use Microsoft Office Access 2007 as a tool in which to create and modify a database and work with its data, but you can also use Office Access 2007 as a front end (interface) for a server database management system, such as Microsoft SQL Server. Generally, when you use Office Access 2007 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.

What do you want to do?

Connect to a server database management system

Create a pass-through query

Connect to a server database management system

To connect to a server database management system, you can configure the server database management system as an ODBC data source.

Note: You must be a member of the Administrators group on your local machine in order to configure an ODBC data source.

Configure an ODBC data source in Windows Vista

1. Click the Start button, click Control Panel, and then do one of the following:

   • In Category View, click System and Maintenance, and then click Administrative Tools.

   • In Classic View, click Administrative Tools.

2. Double-click Data Sources (ODBC).

   The User Account Control confirmation dialog box appears.

3. Click Continue.

4. Click the System DSN tab.

5. Do one of the following:

   • To define a new data source for an installed driver, click Add.

   • To modify the definition of an existing data source, click the name of that data source, and then click Configure.

6. Change the information in the dialog boxes as needed.

   For more information about the different options, click the Help button in each dialog box.

Configure an ODBC data source in Microsoft Windows XP

1. Click the Start button, click Control Panel, and then do one of the following:

   • In Category View, click Performance and Maintenance, and then click Administrative Tools.

   • In Classic View, click Administrative Tools.

2. Double-click Data Sources (ODBC).

   The ODBC Data Source Administrator dialog box appears.

3. Click the System DSN tab.

4. Do one of the following:

   • To define a new data source for an installed driver, click Add.

   • To modify the definition of an existing data source, click the name of that data source, and then click Configure.

5. Change the information in the dialog boxes as needed.

   For more information about the different options, click the Help button in each dialog box.

Top of Page

Create a pass-through query

1. On the Create tab, in the Other 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 .

   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 in the preceding procedure, 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.

Top of Page