How the driver translates statements

graph TD
    A["SQL statement of the application (Access, ADO.NET, VBA)"] --> B{"Can it be expressed as an OData request?"}
    B -->|yes| C["OData request to the service: $select, $filter, $orderby, $top, $skip, $count, $apply, POST/PATCH/DELETE"]
    B -->|no| D{"Does the connection or the service name an address for custom statements?"}
    D -->|yes| E["Unchanged to the database (pass-through, with bound values)"]
    D -->|no| F["Rejected with SQLSTATE 42000 and a message that names the reason"]

The driver computes nothing itself. Every statement is either translated into an OData request, passed through to the database, or rejected – it never loads a whole table to filter, group, or join locally. That explains why a query in Access runs against one service and ends with „ODBC–call failed“ against another.

• Reading, filtering, sorting, and counting on the service. Conditions, sorting, column selection, and COUNT(*) go to the service as query options; only the rows found travel.
• Editing as with a SQL Server table. INSERT, UPDATE, and DELETE from datasheets and forms become POST, PATCH, and DELETE, with write conflict detection through If-Match.
• Totals and groups without pass-through, if the service announces grouping.
• Paging with OFFSET … FETCH as in SQL Server.
• Long conditions in the body instead of the address.
• Everything else unchanged to the database, if the connection offers that – otherwise a clear rejection instead of a wrong result.

An address that would become longer than MAXURLLENGTH goes as POST …/$query with the options in the body. The complete rules are under Supported SQL: Grouping and summarizing and Supported SQL: Paging with OFFSET/FETCH.

What can't be translated – a join of two tables, a subquery, a change to many rows, a schema statement, calculating between aggregates – the driver sends unchanged to the address for own statements. Bound values remain values: the ? placeholders become named parameters. The service describes the columns of the result; the driver doesn't guess types.

The address is named either by the connection (PASSTHROUGH) or by the service in its schema document. Whether the statement may be executed is decided by the service, not by the driver; the driver doesn't check or classify it. The reason is under Security model and trust boundaries; the setup under Set up pass-through queries.

One exception: statements over tables with the prefix MSys – the application's system tables, for example the Access query for MSysConf on every connection – are rejected by the driver itself. They don't exist in the service, and Access expects exactly this answer.

Without an address for custom statements, the driver rejects everything that can't be translated with SQLSTATE 42000. Access shows this as error 3146 „ODBC–call failed“ and then doesn't compute the query itself – not even for totals and joins that it could compute itself over a local table. See "ODBC--call failed" for totals or joins.

Likewise rejected instead of approximated – and, with an address, likewise passed through: LIKE with _ or a character class ([a-c]), functions other than SUM, MIN, MAX, AVG, COUNT in a grouping, changing or deleting a table without a row version over the old values of all columns if the service declares neither a concurrency property nor the actions for tables without a key for it. The reason is always the same: a silently simplified statement would return a different result that would look like the answer.

Every statement is a separate request to the service. The driver brackets several of them only if the service offers transactions: it then names the address Store.Transactions in $metadata (the data service Wkl.Data.Gateway does this).

Access and DAO decide solely by SQL_TXN_CAPABLE whether to pass BeginTrans on to the driver. Without transactions from the service, they keep the transaction to themselves: every change is saved on the service immediately, and a Rollback takes nothing back there – without an error. With them, VBA code with BeginTrans/Rollback behaves as it does through the SQL Server driver: Rollback takes back changes, additions, and update queries, CommitTrans saves them, and an error in one statement leaves the transaction in place.

Differences from the SQL Server driver: the transaction holds a connection of its own on the service; if the application doesn't use it for a while, the service rolls it back, and CommitTrans then reports 40001 – none of it is saved. How long the service waits is up to the service; for the data service Wkl.Data.Gateway, the default is two minutes.

If an application relies on Rollback – for example an operation that creates a header and its line items together – check with the service it's going to run against:

1. Query the capability: in DAO CurrentDb.OpenRecordset("<verknüpfte Tabelle>").Transactions (must be True), in ODBC SQLGetInfo(SQL_TXN_CAPABLE) (must be SQL_TC_ALL).
2. Try it on a test row: BeginTrans, change the row, Rollback, read the row again. It must carry the old value.

If step 1 shows False, Rollback takes nothing back on the service, without an error. An operation that may save several records only together then belongs in a database procedure that it calls through pass-through, or it needs a service with transactions.

If the database rejects a statement and the service passes on its messages (error.details with the message number as code, as the data service Wkl.Data.Gateway does), the driver reports them as the SQL Server driver does: one diagnostic record per message, the message number as the native error number, and the SQLSTATE that the service names for it (23000 for a violated key, reference, or required value, 22001 for a value that's too long, 01000 for an accompanying informational message).

Code that branches on the number over a direct connection (If DBEngine.Errors(0).Number = 2627 Then) therefore runs unchanged. The service passes the text on only if it names nothing the caller isn't allowed to see; otherwise a text of the service appears there, and the number and SQLSTATE are still correct. Without details, the driver reports a single record with the HTTP status as the number.

The division into translating, passing through, and rejecting is a design decision. Three alternatives would have distributed correctness, cost, or security very differently:

The chosen form combines the last two: whatever can be expressed as an OData request runs against any OData v4 service and under its per-table rules; only the rest needs the address for own statements. Its price: a query that neither route can carry fails visibly instead of running slowly or inaccurately. Approximating individual parts, for example LIKE 'a_c' as contains, is rejected as well; the reason is under Rejected.

• Security model and trust boundaries – who allows a passed-through statement
• Driver connection string – PASSTHROUGH, MAXURLLENGTH
• Set up pass-through queries
• "ODBC--call failed" for totals or joins
• Limits and known restrictions