Set up pass-through queries

A pass-through query sends its SQL unchanged to the database behind the service—for joins, groupings across several tables, changes to many rows, or procedure calls that can't be expressed as an OData request. Queries over linked tables that Access sends as a statement of its own (joins, subqueries) take the same route.

The driver passes a statement on to the address for custom statements. This address is named either by the service in its schema document—then nothing needs to be set on the workstation—or by the connection with PASSTHROUGH. Whether the statement may run is decided by the service; the driver doesn't check it.

1. Find out from the operator which statements the service accepts. That's up to the service, not the driver. The data service Wkl.Data.Gateway has two kinds:

   Kind	What runs	Requirement in the service
   Checked statements	SELECT with joins, subqueries, UNION, grouping; INSERT, UPDATE, DELETE; a procedure call. The service checks every object the statement names against the caller's rights	on by default (AllowCheckedStatements); any caller whose rule names the alias. A read-only rule allows only reading statements
   Unchecked statements	any SQL, including schema changes, with the permissions of the login it runs under	AllowPassThrough: true on the alias and a rule that isn't read-only; with a service key or under the alias's login, also the sql right

   Queries over linked tables that Access generates itself are checked statements; they usually need no enablement. Details are in “Aliases, catalog, and the central rule” (data service documentation).

2. Create a pass-through query in Access: select Create > Query Design, close the tables dialog, select Pass-Through on the ribbon, and enter the SQL.

3. Set the connection of the query: in the Property Sheet, under ODBC Connect Str, enter the same string as for the linked tables of the same file, for example

   ODBC;DRIVER={Wkl OData};URL=https://daten.example.com/odata/v4/fachdaten/dbo/;AUTH=WINDOWS

   If the statement returns no result (UPDATE, EXEC without a return), set Returns Records to No.

   

4. Run the query. Access takes the fields and types from the description that the service returns.

   

With VBA:

Dim qd As DAO.QueryDef
Set qd = CurrentDb.CreateQueryDef("qryUmsatzJeKunde")
qd.Connect = "ODBC;DRIVER={Wkl OData};URL=https://daten.example.com/odata/v4/fachdaten/dbo/;AUTH=WINDOWS"
qd.SQL = "SELECT k.Name, SUM(u.Betrag) AS Summe FROM dbo.Umsatz u JOIN dbo.Kunde k ON k.Id = u.Kunde GROUP BY k.Name"
qd.ReturnsRecords = True

Result: The query returns the same rows as the same statement run directly in the database.

• One address for all connections of the same file. Access shares an open connection with every query whose connection string carries the same DATABASE value – even if it differs in PASSTHROUGH. If a table was linked without the address first, the pass-through query reaches the driver through that connection and is rejected. Therefore, name PASSTHROUGH in every connection of the file or in none (then the service's address applies).
• Access doesn't show the row count of a change. After a pass-through query, RecordsAffected in Access reports 0; Access doesn't even ask the driver for it. Directly over ODBC, the driver reports the correct number.
• Bound values (? from an application) go to the database as parameters, never as text. A binary value travels base64-encoded; a statement that has to compare a binary value is better off writing it out itself.
• No next pages. A passed-through statement is read in full; the service's row limit caps it. The driver reads the response row by row as it arrives. The data service Wkl.Data.Gateway streams a result of 500 rows or more; if such a stream breaks off midway, the application receives 08S01 instead of a truncated result.
• Without an address for custom statements, the driver rejects statements it can't translate with 42000; see "ODBC--call failed" for totals or joins.

• How the driver translates statements
• Driver connection string: `PASSTHROUGH`