Send commands to an SQL database using a pass-through query

Microsoft Access pass-through queries send commands directly to an ODBC database server (such as Microsoft FoxPro). With pass-through queries, you work directly with the tables on the server instead of linking to them.

1. In the Database window, click Queries under Objects, and then click New on the Database window toolbar.
   
   
2. In the New Query dialog box, click Design View, and then click OK.
   
   
3. Without adding tables or queries, click Close in the Show Table dialog box.
   
   
4. On the Query menu, point to SQL Specific, and then click Pass-Through.
   
   
5. On the toolbar, click Properties to display the query property sheet.
   
   
6. In the query property sheet, set the ODBCConnectStr property to specify information on the database to which you want to connect. You can type the connection information, or click Build , and then enter information about the server you're connecting to.

   For details on the syntax for your query, see the documentation for the SQL database server to which you're sending the query.

7. If the query isn't the type that returns records, set the ReturnsRecords property to No.
   
   
8. In the SQL Pass-Through Query window, type your pass-through query.
   
   
9. To run the query, click Run on the toolbar. (For a pass-through query that returns records, you can click View on the toolbar, instead.)

   If necessary, Microsoft Access prompts you for information about your Microsoft SQL server database.

   Caution   If you convert a pass-through query to another type of query, such as a select query, you'll lose the SQL statement that you entered.

Notes

• Pass-through queries are useful for running stored procedures on an ODBC server.
  
  
• If you don't specify a connection string in the ODBCConnectStr property, or if you delete an existing string, Microsoft Access uses the default string "ODBC;". With this setting, Microsoft Access prompts you for connection information each time you run the query.
  
  
• Some pass-through queries can return messages in addition to data. If you set the query's LogMessages property to Yes, Microsoft Access creates a table that contains any returned messages. The table name is the user name concatenated with a hyphen ( - ) and a sequential number starting at 00. For example, the default user name is ADMIN so the tables returned would be named "ADMIN - 00," "ADMIN - 01," and so on.

  If you create a pass-through query that returns multiple result sets, you can create a separate table from each result. For more information, click .