Limits and known restrictions (reference)

What the OData ODBC driver can't do or does differently from the SQL Server ODBC driver, and why. Each limit names where the remedy is described.

• Only OData v4 with JSON. OData v2/v3 and services without $metadata aren't supported.
• Bitness: A 32-bit application loads only the x86 driver, a 64-bit application only the x64 driver. The installation package installs both; it requires 64-bit Windows.
• Transactions only where the service offers them. If the service announces an address for transactions in $metadata (Store.Transactions, as the data service Wkl.Data.Gateway does, for example), BeginTrans, CommitTrans, and Rollback in VBA/DAO and BeginTransaction in ADO.NET work as they do through the SQL Server driver. Without this announcement, the driver reports no transactions: ADO.NET receives the error HYC00 on BeginTransaction, and Access then keeps BeginTrans to itself – a Rollback takes nothing back on the service. If the connection breaks during CommitTrans, it's unknown whether anything was saved. How to check this before use and what happens on errors is described under Transactions.
• The driver waits for a busy service. If the service answers a read with 429 (too many requests) or 503 (temporarily unavailable), the driver waits as long as Retry-After says and asks again; without it, it waits 1, 2, 4, 8 and 16 seconds. It doesn't wait out a single wait over 60 seconds or more than 120 seconds in total, but reports HYT00 with the service's time. A busy service doesn't open the Connect dialog. RETRY=OFF also turns the waiting off.
• Retry after a dropped connection is turned on. The driver retries reads and writes after 1 and 3 seconds; for writes, that's safe only if the service honors Idempotency-Key. For a service that doesn't, turn it off with RETRY=OFF. See Commitments per kind of service.
• The ADO.NET CommandBuilder in its default needs pass-through, or a different concurrency rule; see ADO.NET and Visual Studio.
• ADO.NET transmits a DateTime only to the millisecond; see ADO.NET and Visual Studio.
• Only what can be translated – or is passed through. A statement that doesn't fit into an OData query is rejected unless the connection names an address for custom statements (see How the driver translates statements). In no case does the driver silently load the whole entity set to filter locally.
• Editing without a key only if the service offers it. An entity set without a key is read-only unless $metadata declares the actions UpdateByOriginal and DeleteByOriginal for it. Then Access identifies the record by its old values; identical records remain unchangeable.
• Changing and deleting need a row version that the service declares. If it declares one – a stored column or a computed value – Access uses it as the only additional condition, and the driver turns it into If-Match; a write conflict then appears as such in Access. If it declares none, Access puts the old values of all columns into the condition. That can't be expressed in OData, and the driver rejects it instead of silently dropping the check: otherwise the change would overwrite someone else's change that Access considers impossible. Adding is possible even without a row version. The remedy lies with the service, not the driver: it declares a row version or a computed stamp.
• A translated statement reads exactly one entity set; joins and subqueries run only through pass-through, see Supported SQL.
• LIKE only in the forms x%, %x, %x%, and without _ as a wildcard ([_] and an escape character are allowed); see Supported SQL.
• The cursor reads forward, without bookmarks and without locks.
• A few characters remain encoded in the name (!, ", ., [, ], `, control characters, leading spaces); see Troubleshooting.
• A name with more than 64 characters fails in Access, as through the SQL Server driver; see Troubleshooting.
• Access shares connections by DATABASE. Two links with the same DATABASE run over one connection, even with a different URL – and then silently read and write the data of the first service root. The driver learns nothing about it and can't prevent it; the rule for setup is under Several service roots in one file. Likewise, links that differ only in ROLE= share one connection; the database identity of the first then applies. Different service roots and a second identity in the same session therefore need different DATABASE values; the driver supplies one if the string names none. See Driver connection string.
• The dialog appears only where the application allows it: with a window and not with SQL_DRIVER_NOPROMPT. Without a window, an incomplete connection fails with the service's rejection. If Access opens a link without a saved password, the driver signs in with the most recently used name whose password was stored with “Save password”; otherwise it asks with a short sign-in form. After “Cancel”, it doesn't ask again for the same service for five seconds.
• On a failed connection attempt, Access shows only DAO 3151; see Troubleshooting.
• The service describes a passed-through statement before it runs. When an application asks for the columns between SQLPrepare and SQLExecute, the driver gets the description from the service without running the statement. It does so only when the service names the address for custom statements itself (Store.PassThrough); with an address from PASSTHROUGH, and for a statement that SQL Server can't describe without running it (temporary tables), the driver reports zero columns until the statement runs.
• Only the first result set arrives row by row. Every further set of a passed-through statement arrives with the end of the first one and is held in memory until the application reaches it with SQLMoreResults. Put a large set first in the procedure.
• DAO GetRows hangs on an error while reading further – with any driver; see Troubleshooting.
• A passed-through statement is read in full. There are no next pages: the statement itself determines the set, and the service caps it with its row limit. If this limit takes effect, the driver returns the rows supplied and then reports HY000 “The service cut the result off after N rows …”; Access shows this as “ODBC–call failed”. See Classify responses.
• Only mappable parts of the OData model appear. Complex values, collections, relationships, singletons, functions, and inherited properties are missing from the catalog; see How an OData service becomes a relational ODBC model.
• A bound binary value travels as text. On the pass-through route, it's passed base64-encoded; a statement that has to compare a binary value should write it out itself.

• Tracing: For troubleshooting, use the driver's tracing and the Driver Manager's tracing; see Turn on tracing. The trace keeps its file open as long as the process runs and writes every line immediately.
• A call blocks the application's thread as with a native driver until the response arrives, and processes no window messages in the meantime. Application events, for example “On Current” of a datasheet in Access, therefore never run in the middle of a driver call.
• Certificates: Over https, the driver validates the certificate against the Windows certificate stores, with no switch to turn it off; an untrusted certificate results in 08S01 and isn't retried. See 08S01 "UntrustedRoot".

• Schema changes: The driver loads $metadata once per connection and keeps it until the connection is closed. If the service's schema changes while an application is connected:

  Change	Consequence until the connection is rebuilt
  Column or table added	not visible
  Column removed or renamed	a request that names it is refused by the service; the application receives the service's message
  Right revoked	the service refuses (403), even if the table is still displayed

  In Access, rebuilding means: close and reopen Access, then refresh the affected links in the Linked Table Manager; only then does Access adopt new columns and types into the link.

If you move an application from the SQL Server ODBC driver (ODBC Driver 18) to this driver, pass-through returns the same values wherever both drivers return a result. This driver behaves differently in the following areas, in both the x86 and the x64 version:

• Supported SQL
• Driver connection string
• How the driver translates statements