Microsoft Office Tutorials and References
In Depth Information
Using pass-through queries
Pass-through queries let you send commands directly to an ODBC database
server. By using a pass-through query, you work directly with the server
tables instead of having the Microsoft Jet Database Engine process the data.
Sometimes, however, the Jet Database Engine can’t process the information
or criteria you’re entering in the query window. A pass-through query lets
you interact directly with SQL Server (or any other ODBC-linked database).
To create a pass-through query, click the Query Design button in the
Queries group on the Create tab of the Ribbon. When the Show Table dialog
box appears, click Close without selecting any tables. Then click the
PassThrough button in the Query Type group on the (Query Tools) Design tab of
the Ribbon, as shown in Figure 2-10.
Access gets rid of the fancy query-builder interface and shows you a blank
area where you can type any SQL statement you want. You must know the
native syntax of SQL Server (or the ODBC database you’re working with) to
have a successful result.
The following example SQL Select statement shows all the records from the
SQL table tblContacts in which the LastName field starts with J:
SELECT * FROM tblContacts WHERE LastName Like ‘J%’
Notice that this query uses the name of the table from SQL Server, not the
name assigned in the Access Navigation Pane. Because SQL Server processes
this query, it doesn’t care what you called the query in Access. Also, instead
of using an asterisk (*) for a wildcard character, this query uses a percent