Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 36 ยท Data Import, Export and Linking

Linked Tables and ODBC in Access

Link Access to Excel, other Access files and SQL Server through ODBC. Learn linked versus imported data, the Linked Table Manager, DSN-less connections and pass-through queries.

A linked table looks and behaves like a normal table in the Navigation Pane, but its data stays in the original source: another Access file, an Excel workbook, a text file, a SharePoint list or a table on a database server such as SQL Server, MySQL or PostgreSQL. Queries, forms and reports use it exactly like a local table. Linking is the foundation for splitting a database into a front end and a back end (split databases and multi-user) and for using Access as the interface to a real database server.

The difference from importing is simple: import copies, link connects. Import when the data should live in this file and not change; link when other people or systems keep changing it.

Use External Data > New Data Source and choose Link to the data source by creating a linked table. Typical sources:

  • Access database: link tables from another .accdb (the back end of a split database).
  • Excel: link a worksheet or named range (read mostly; Access can edit values but cannot add or delete the sheet's structure, and you cannot link to a file open exclusively elsewhere).
  • Text file / CSV: a live view of a file that another program rewrites every night.
  • ODBC Database: any server with an ODBC driver.
  • SharePoint list: a shared list on a team site.

Linked tables show an arrow icon in the Navigation Pane (a globe for ODBC). A linked Excel sheet shows the $ name of the worksheet. Linked tables cannot have their design changed from Access (except for local properties such as the caption); change the structure in the source.

ODBC in a nutshell

ODBC (Open Database Connectivity) is a standard driver interface: Access talks ODBC, the driver talks the server's own protocol. To link a SQL Server table:

  1. External Data > New Data Source > From Database > ODBC Database > Link.
  2. Choose a Machine Data Source (a DSN), or create a new one for ODBC Driver 18 for SQL Server, entering the server, authentication mode and default database.
  3. Pick the tables. If a table or view has no primary key, Access asks you to select the unique identifier fields, otherwise the link would be read-only. Never skip this: choose the columns that uniquely identify a row.
  4. Optionally tick Save password. A saved password is stored in the database file, which is a security trade-off (see security); prefer Windows (integrated) authentication.

A DSN-less connection puts everything in a connection string, so the app works on any PC with the driver installed, without configuring a DSN first:

TEXT
ODBC;DRIVER={ODBC Driver 18 for SQL Server};SERVER=sql01.corp.local;DATABASE=Sales;Trusted_Connection=Yes;Encrypt=Yes;TrustServerCertificate=No;

You can store this string on each linked table (the Connect property of its TableDef) and rebuild it when the server moves.

Linked Table Manager

When a back end moves (new server, new folder, new drive letter), links break and queries fail with "could not find the linked table". Fix it with External Data > Linked Table Manager, tick the tables to refresh, and browse to the new location. For automation, relink in VBA:

VBA
Public Sub RelinkAccessBackEnd(newPath As String)
    Dim td As DAO.TableDef
    For Each td In CurrentDb.TableDefs
        If Left$(td.Connect, 10) = ";DATABASE=" Then
            td.Connect = ";DATABASE=" & newPath
            td.RefreshLink          ' raises an error if the path is wrong
        End If
    Next td
End Sub

For ODBC tables the Connect string starts with ODBC;. A common pattern is to read a configuration table or the application's own folder at start-up, test each link with RefreshLink, and show a friendly dialog when the back end is unreachable.

Pass-through queries and views

A normal query against a linked server table is processed by the Access database engine (ACE), which may pull a lot of rows over the network before filtering them. For heavy work, use one of these:

  • Create a view on the server that filters and joins, then link to the view. Access treats it as a table, and the server does the work.
  • Pass-through query (Create > Query Design > Pass-Through): the SQL text goes unchanged to the server in its own dialect (T-SQL, for example), and only the result returns. Use it for stored procedures, server-only functions and big aggregates.
TEXT
-- Pass-through query to SQL Server (T-SQL, not Access SQL)
SELECT TOP 10 c.Name, SUM(o.Total) AS Revenue
FROM dbo.Orders AS o JOIN dbo.Customers AS c ON c.CustomerID = o.CustomerID
GROUP BY c.Name
ORDER BY Revenue DESC;

Pass-through results are read-only and you cannot use Access functions such as IIf or Nz in them. The dialect difference is why SQL Server queries use ISNULL/COALESCE and CASE where Access uses Nz and IIf. This stand-in runs the portable form:

SQL Stand-in: portable SQL the server would run

Press Run (or Ctrl + Enter) to see the result here.

  • Filter early. Put criteria on indexed server columns, and avoid joining a big linked table to a local one on a calculated expression, which forces Access to download everything.
  • Bind forms to filtered queries or load a few records at a time, not the whole table.
  • Keep a persistent connection: open a hidden form bound to a small linked table so the connection stays alive, which makes later queries much faster than reconnecting each time.
  • Linked Excel and text files are slow and fragile: if the file is open or renamed, the link fails. Import the data on a schedule instead when it matters.
  • Never use the same file as both front end and back end for several users (see splitting).

Which choice fits which job?

flowchart LR
  A["Data source"] --> B{"Changes outside Access?"}
  B -->|no| C["Import (copy once)"]
  B -->|yes| D{"Server database?"}
  D -->|yes| E["Link via ODBC + views / pass-through"]
  D -->|no| F["Link (Access, Excel, text)"]

Import is a snapshot; a link is a live window.

Linking Access to SQL Server is also the first step of moving the whole data layer to a server, covered in upsizing to SQL Server.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the main difference between importing and linking?

    An imported table is a local copy; a linked table points at the original data.
  2. 2. Why does Access ask for a unique identifier when you link a SQL Server view?

    Access needs a unique key to update rows; you choose the columns that identify a row.
  3. 3. Which tool repairs links after a back end moves?

    Linked Table Manager relinks selected tables to the new location.
  4. 4. What is special about a pass-through query?

    The server executes the text in its own dialect and only the results come back.

Summary

  • Link to keep data in its source (Access, Excel, text, SharePoint, ODBC servers); import to copy once.
  • ODBC needs a driver and either a DSN or a DSN-less connection string; choose unique identifiers for views.
  • Repair links with the Linked Table Manager or TableDef.Connect and RefreshLink.
  • For speed use server views or pass-through queries, filter early and keep a persistent connection.

Next: Automating Excel, Word and Outlook from Access