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
To create a pass-through query, follow these steps:
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.