Link a table in Access

In this tutorial, you link a table of an OData service in Access, without VBA, and edit it like a table on a SQL Server. It takes about ten minutes.

Prerequisites

Microsoft Access or the Access Runtime (Access 2010 or later, 32-bit or 64-bit; see Prerequisites); the bitness (32-bit or 64-bit) is shown under File > Account > About Access
the Wkl OData driver in the same bitness as Access; the installation package Wkl.Data.OData.Odbc-<version>.msi installs both bitnesses and requires administrator rights
the address of an OData v4 service, for example the data service, and an account there; this tutorial uses Windows sign-in, so the service must know your Windows account or one of your groups

Steps

1. Open the link wizard

In Access, open a database or create a new one.
Select External Data > New Data Source > From Other Sources > ODBC Database.
Select Link to the data source by creating a linked table, and then select OK.

2. Create a data source for the service

In the Select Data Source dialog, switch to the Machine Data Source tab, and then select New.
Select User Data Source and the driver Wkl OData, and then complete the wizard with Finish. The driver's dialog Set Up OData Data Source opens. The driver's dialogs follow the Windows display language: German, Slovenian, or else English.

Enter the connection:

Field	Entry
Data source	a name, for example `Fachdaten`
Service address	the root address, for example `https://daten.example.com/odata/v4/fachdaten/dbo/`
Authentication	Windows authentication

If you know only the address of the service but not the database: switch to Browse at the top, enter the address up to /odata/v4/, select Search, and select the database and schema in the tree. The dialog takes over the service address. The "Set Up OData Data Source" dialog: at the top the Browse tab with an address field and Search, below it the connection properties data source "Fachdaten", service address, and authentication "Windows authentication", at the bottom Test, OK, and Cancel

Select Test. The status line reports a successful connection.
Select OK.

3. Select the data source

In the Select Data Source dialog, select the new data source, and then select OK.

The "Select Data Source" dialog, Machine Data Source tab: the user data source "Fachdaten" with the description "Fachdaten über den Datendienst" is selected

4. Select tables

In the Link Tables dialog, select one or more tables, and then select OK. The dialog shows the tables and views that you're allowed to read. Access doesn't ask for a unique field: the driver reports the key.

The "Link Tables" dialog with the tables dbo.Artikel, dbo.Kunde, and dbo.Umsatz; dbo.Artikel is selected; on the right OK, Cancel, Select All, Deselect All, and "Save password"

5. Open the linked table

Open the new table in the Navigation Pane. Filter, sort, and search as you would in a local table; the service evaluates conditions and sorting. If you're allowed to write to this table, change a value and leave the record: Access saves it to the service.

Result

Access with the linked table dbo_Artikel open in Datasheet view: columns Nummer, Bezeichnung, Gruppe, Preis, and Version, six records; in the Navigation Pane dbo_Artikel with the icon of a linked ODBC table

The Navigation Pane shows a linked table (icon with a globe and an arrow). The link stores the name of the data source and the service root as DATABASE, without any secret:

ODBC;DSN=Fachdaten;DATABASE=https://daten.example.com/odata/v4/fachdaten/dbo/

Access has adopted the table's key as its primary index; that's how it recognizes the table as editable. If another user changes the same record between your read and your save, Access shows the familiar write conflict and overwrites nothing.

Several service roots in one file

Warning: If you link tables from a second service root – another database, another schema, or a second database identity (ROLE) – into the same Access file, each connection needs its own value for DATABASE. Otherwise Access serves both through the connection opened first: the tables of the second root show the data of the first without any error message, and changes end up there.

Access doesn't separate connections by address, but by the value of DATABASE in the connection string that it stores when linking. The driver supplies it: from the data source, otherwise from the service root. The setup dialog writes the service root into every new or changed data source. Two data sources pointing to two service roots therefore carry different values.

Route	Connection string of the link
without a data source	`ODBC;DRIVER={Wkl OData};URL=https://daten.example.com/odata/v4/vertrieb/dbo/;AUTH=WINDOWS` – the driver supplies `DATABASE` per service root
data source	`ODBC;DSN=Vertrieb;DATABASE=…` – the driver supplies the data source's `DATABASE`
link by code (`TableDef.Connect`)	specify `DATABASE` yourself, different per service root; Access stores the string as set

To check which connection a table actually uses:

Turn on tracing, and restart Access.
Open one table from each service root, one after the other.
Search the file for the lines HTTP GET. The requests for the second table must carry the address of its own service root. If they carry the address of the first, the links share a connection.
In the Immediate window (Ctrl+G), check the stored string of each link: ? CurrentDb.TableDefs("dbo_Artikel").Connect.

How Access behaves in detail is described under Connection string: Access shares connections by `DATABASE`.

Next steps

Sign in with a personal key and a second factor – without a domain, also from outside
Set up pass-through queries – joins and groupings across several tables
How the driver translates statements
Driver connection string – linking by code