Access reports "ODBC--call failed" for totals or joins
A query over the driver's linked tables fails with error 3146 “ODBC–call failed”, while simple
queries on the same tables run. Typical cases: a totals query (Sum, Count, Group By), a join of
two linked tables, a subquery.
Environment
Microsoft Access and Access Runtime with tables linked through {Wkl OData}; likewise any other ODBC
application that sends such statements.
Cause
Access sends totals, groups, and joins over linked tables to the driver as one statement and doesn't compute them itself if the driver refuses (even if the driver reports no SQL conformance). The driver doesn't compute anything locally either; it translates, passes through, or refuses:
| Statement | Runs if … |
|---|---|
Totals, groups, HAVING, DISTINCT over one table | the service announces grouping ($apply) or there's an address for custom statements |
Join of two tables, subquery, calculating between aggregates (SUM(a) - SUM(b)) | there's an address for custom statements |
If both are missing, the driver refuses with SQLSTATE 42000, and Access shows only 3146. The data
service Wkl.Data.Gateway names the address to every caller whose rule names the alias, as long as
checked statements are on for the alias (the default). If it refuses a statement because an object
it names isn't approved, the 403 arrives as 28000, and Access again shows only 3146.
Resolution
- Read the reason in the driver's trace: set
TRACEFILE=in the connection or the environment variableWKL_ODATA_ODBC_TRACE, restart Access, and repeat the query. If it says that the service doesn't announce grouping or that the statement can't be translated, continue with step 2. - Have the operator check whether the service accepts custom statements from your caller: with the data service Wkl.Data.Gateway, checked statements on the alias and a rule that approves the alias and every object in the query; see Set up pass-through queries, step 1. If the service reports the address itself, nothing needs to change on the workstation. If the connections of the file name
PASSTHROUGHexplicitly, every connection of the file must name it. - If the service doesn't accept custom statements, restructure the query: totals over a single table still run against a service with
$apply; create a join as a view in the database, and link the view. - Restart Access after the service has been changed: Access shares one connection per
DATABASEvalue and reads the service's settings when connecting.
Verification
The same query returns rows. Instead of the rejection, the trace shows a request with $apply=
(grouping) or a request to the address for custom statements (…/sql.json).