Microsoft KB Archive/303968

Knowledge Base

How to create an SQL pass-through query in Access

Article ID: 303968

Article Last Modified on 4/6/2007

---

APPLIES TO

• Microsoft Office Access 2007
• Microsoft Office Access 2003
• Microsoft Access 2002 Standard Edition

---

This article was previously published under Q303968

Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb or .accdb).

SUMMARY

SQL pass-through queries are used to send commands directly to an ODBC database server. By using an SQL pass-through query, you work directly with the server tables instead of having the Microsoft Jet database engine process the data.

MORE INFORMATION

To create an SQL pass-through query, you must first create a system data source name (DSN,) and then you can create the SQL pass-through query.

Creating the System DSN on a Microsoft Windows 2000-Based or a Windows 2003-Based Computer

1. Click Start, point to Settings, and then Click Control Panel.
2. In Control Panel, double-click Administrative Tools.
3. Double-click Data Sources (ODBC).
4. In the ODBC Data Source Administrator dialog box, click the System DSN tab.
5. Click Add.
6. Select the appropriate driver.
7. Click Finish, and then provide other required information for the selected driver.

Creating the System DSN on a Microsoft Windows Millennium Edition-Based Computer

1. Click Start, point to Settings, and then Click Control Panel.
2. In Control Panel, double-click Data Sources (ODBC 32-bit).
3. Click the System DSN tab.
4. Click Add.
5. Select the appropriate driver, and then click OK.
6. Click Finish, and then provide other required information for the selected driver.

Creating the SQL Pass-Through Query

Access 2002 or Access 2003

1. In the Database window, click Queries under Objects, and then click New.
2. In the New Query dialog box, click Design View, and then click OK.
3. Click Close in the Show Table dialog box without adding any tables or queries.
4. On the Query menu, point to SQL Specific, and then click Pass-Through.
5. On the toolbar, click Properties to display the property sheet for the query.
6. In the query property sheet, place the mouse pointer in the ODBC Connect Str property, and then, click the Build (...) button.
   
   With the ODBC Connect Str property, you specify information about the database to which you want to connect. You can type the connection information, or click Build, and then enter information about the server to which you are connecting.
7. When you are prompted to save the password in the connection string, click Yes if you want the password and logon name to be stored in the connection string information.
8. If the query is not the type that returns records, set the ReturnsRecords property to No.

9. In the SQL Pass-Through Query window, type your pass-through query. For example, the following pass-through query uses the Microsoft SQL Server TOP operator in the SELECT statement to return only the first 25 orders in the Orders table from the sample Northwind database:

   SELECT TOP 25 orderid from orders
                       

10. To run the query, click Run on the toolbar. (For a SQL pass-through query that returns records, click View on the toolbar.)
11. If necessary, Microsoft Access prompts you for information about your server database.

Access 2007

1. On the Create tab, click Query Design in the Other group.
2. Click Close in the Show Table dialog box without adding any tables or queries.
3. On the Design tab, click Pass-Through in the Query Type workgroup.
4. Click Property Sheet in the Show/Hide workgroup to display the property sheet for the query.
5. In the query property sheet, place the mouse pointer in the ODBC Connect Str property, and then, click the Build (...) button.
   
   With the ODBC Connect Str property, you specify information about the database to which you want to connect. You can type the connection information, or click Build, and then enter information about the server to which you are connecting.
6. When you are prompted to save the password in the connection string, click Yes if you want the password and logon name to be stored in the connection string information.
7. If the query is not the type that returns records, set the ReturnsRecords property to No.

8. In the SQL Pass-Through Query window, type your pass-through query. For example, the following pass-through query uses the Microsoft SQL Server TOP operator in the SELECT statement to return only the first 25 orders in the Orders table from the sample Northwind database:

   SELECT TOP 25 orderid from orders
                       

9. To run the query, click Run in the Results group on the Design tab. For a SQL pass-through query that returns records, click Datasheet view on the status bar.
10. If necessary, Microsoft Access prompts you for information about your server database.

REFERENCES

For more information about pass-through queries, see the "Create a pass-through query that sends commands to an SQL database" topic in the "Create an SQL-specific query" section in Access Help.

For more information about creating DSNs, see the Help file for your operating system or see the ODBC Help file.

Additional query words: ACC2002 OfficeKBHowTo inf pass-through system dsn data source name sql ACC2003 ACC2007

Keywords: kbquery kbsqlprog kbhowto KB303968

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.