Do I need the ODBC driver?
Only for Access and for other programs that work through ODBC. Excel and Power BI don't need it: they read the data service through Power Query as an OData feed.
What each tool needs
| Tool | How it reaches the data service | Driver required | Reading and filtering | Editing |
|---|---|---|---|---|
| Access, Access Runtime | linked table, pass-through query, DAO/ADO | yes, in the bitness of Access | yes, on the service | yes, in datasheets and forms |
| Excel, Power BI | Power Query, OData feed (/odata/v4/…) | no | yes, filters and sorting are sent to the service | no |
| .NET programs | ADO.NET (System.Data.Odbc) through the driver, or HTTP | only for ADO.NET | yes | yes |
| VBA without linked tables | HTTP with the DbGwClient class: administration commands, and any other request through Execute | no | through Execute | through Execute |
The options built into Access aren't enough. Linking to data services (“Datendienste” in the German
user interface) relies on an XML connection definition from Business Connectivity Services, not on
OData, and the linked table is read-only (Microsoft:
Link to a Data Service). For
Access to edit data in a datasheet, an ODBC driver has to bind Access's parameters as values, pass
filters on to the service, and take the column types from $metadata. The OData ODBC driver does
exactly that.
Core concepts
Bitness. A 32-bit application loads only the 32-bit driver, and a 64-bit application loads only the 64-bit driver. The driver's installation package installs both, so it doesn't matter which Office edition a workstation runs.
No runtime. The driver is a native DLL; the workstation doesn't need a .NET runtime.
A generic driver. It works with any OData v4 service. With the data service, it also uses the service's extensions: pass-through queries, grouping, sign-in with a personal key and a second factor, and choosing the database identity.
Related topics
- Background, use cases, and alternatives—the problem the product solves and the other options
- “Link a table in Access” and “Driver connection string (reference)” (documentation of the ODBC driver)
- HTTP: OData v4 (reference)—what Power Query sends to the service