Using Pass-Through Queries

3 4

Some data sources don't have a native SQL interface, so the Microsoft Jet database engine translates queries and transforms them into a format your data source can then interpret. However, the Jet interpretation might not be compatible with the data source, so you might need to bypass the Jet interpretation. For this purpose, Access provides SQL pass-through queries, which send uninterpreted SQL statements to a server database-specifically, an ODBC data source. Uninterpreted in this case means that Access doesn't perform any kind of syntax check or translation before sending the statement, so you must know the specific SQL dialect that the server database uses.

When using a pass-through query, you work directly with the table on the server instead of creating a link to the table. You'll use pass-through queries to execute stored procedures on the server, join databases on the server, run SQL Data Definition Language (DDL) commands that modify the schema of a server database, and more. But there are trade-offs. For example, a recordset returned by a pass-through query is read-only. You can't use the built-in Access functions or any user-defined functions that you've added to the application.

To create a SQL pass-through query, open a blank query design grid, and choose Query, SQL Specific, Pass-Through. Doing this adds three new properties to the query, so it's important not to bypass this step and go directly to the SQL window. Table 11-1 lists the three pass-through properties.

Table 11-1 Pass-through properties

Property	Description	Default
ODBCConnectStr	Specifies the ODBC connection string	ODBC;
ReturnsRecords	Specifies whether the query will return records	Yes
LogMessages	Specifies whether Access will log warning and informational messages from the server to a local table	No

The ODBCConnectStr property has several optional arguments. The default is ODBC;, which will prompt you for a connection string each time you run the query. You can avoid this extra step by simply specifying the connection string in the following form:

 ODBC;DSN=datasource;SERVER=servername;UID=username;PWD=password 

tip - Use the ODBC Connection String Builder to create the ODBC connection string

---

You can use the ODBC Connection String Builder to create the ODBC connection string and thereby create the appropriate property setting. This builder establishes a connection to the SQL database server, displays a series of data source and system options, and then ends the connection after the ODBC connection string is created.

To create a pass-through query, follow these steps:

1. Click Queries on the Objects bar in the Database window, click New on the Database Window toolbar, accept the default Design View, and then click OK in the New Query dialog box.
2. Close the Show Table dialog box without adding any tables or queries.
3. Choose Query, SQL Specific, Pass-Through.
4. Click Properties to display the query's properties sheet, and set the ODBCConnectStr property. This property will specify the connection information Access needs to execute the query. Enter the setting yourself, or click Build to glean the necessary information about the server you're connecting to.
5. The ReturnsRecord property is set to Yes by default. Change this to No if the query won't return records.
6. Type the query in the SQL Pass-Through Query window. You'll need to review documentation for the SQL database server to which you're connecting for the appropriate statements and syntax. You'll also need to know all the table and field names because Access won't be able to help you with that.
7. Click Run to execute the query. If you don't specify a connection string (see step 4), Access will prompt you for one.

Troubleshooting - I ran a pass-through query as a select query, and now the pass-through query is gone

Although Access will allow you to convert a pass-through query, do so with care. The change is permanent-you can't temporarily change a pass-through query to another query type. If you convert a pass-through query and then run it, you must re-create the pass-through query manually. This is true of all SQL-specific queries-pass-through, data-definition, and union.