Use External Data With Access: Linked Tables, BDCM, and Refresh Prerequisites

Connecting Access to an outside data source is rarely a formatting problem. It fails because the route was wrong for the environment, because the service behind the connection was never configured, or because the table was created as a snapshot when the goal was to refresh. Choosing the route before you click through any dialog prevents most of that.

This guide covers the Access side of the job: the three common routes, the prerequisites that decide whether you can take each one, how to build and verify a linked table, the legacy definition-based route, and the symptom branches for refresh failures. Where a step depends on a specific version, it is labelled as an item to verify rather than stated as a certainty.

Use External Data With Access: Linked Tables, BDCM, and Refresh Prerequisites

Free Download

100% secure

Use External Data With Access: Pick the Right Route First

There are three routes worth knowing, and they are not interchangeable. Linked tables keep a live connection you can refresh. A data source definition (the BDCM route described later) is an administrator-oriented route that brings a pre-built connection definition into Access. A plain import copies the data once and takes a snapshot.

Three Access Routes at a Glance

Route	What it gives you	Can it refresh?
Linked table	A live view of the source data inside Access	Yes
External data source definition	A connection definition imported through the Web Service route	Yes, where the backend service is in place
Imported static data	A local copy of the records	No, it is a snapshot

Two Limits You Cannot Design Around

First, data reached through these external routes is typically read-only in Access, which is the intended behaviour rather than a fault. Second, the definition-based route depends on services an administrator configures for the organisation, including the external content types behind them. A client-side runtime requirement also applies to that route, and the version-specific details are covered in the prerequisites section.

What This Guide Does Not Cover

This is not a guide to opening a CSV in a spreadsheet, and it is not a claim that a spreadsheet application replaces an Access database. The two tools sit at different points in the chain, as the later section on handing data off explains.

What this guide covers and leaves out

Use Word, Excel, and PPT for FREE

Free Download

Prerequisites Before Any Access External Data Route

Missing prerequisites are the most common reason a route looks available and then fails halfway through. Work through the six checks below before you open the connection wizard, so that a later error tells you something new.

Permissions and Roles

You need read access to the data source itself, and you need to be allowed to create objects in the Access database. On managed networks, the second permission is often granted through the team that owns the database.

Backend Service and Secure Store

For the definition-based route, the backend service and its external content types must already be configured by an administrator. Credentials for the external system are usually held in a secure credential store rather than typed into each client machine, which is why this step is not something an end user can complete alone.

Runtime and Client Requirements

The documentation behind this route has historically referenced a specific client runtime version and an older product generation. Treat the exact requirement as something to confirm against current vendor documentation for the version you are running.

A Readiness Checklist

1. You can reach the target data source with your own account.

2. The backend service and the external content types are already configured.

3. The client runtime meets whatever the current vendor documentation requires.

4. You have the connection definition file if your route needs one.

5. Your network path to the service address is open.

Version Note

The reference material for the definition-based route describes older product generations, and the current equivalent configuration path has not been verified here. Confirm the current steps before you rely on them in production.

Six prerequisites before connecting to data

Create and Refresh a Linked Table

A linked table is the route most people actually want: the records stay at the source, and Access shows a live view of them. Each step below ends with something you can observe, so you know the connection is real before you build anything on top of it.

Step 1: Connect to the Data Source

Start from the External Data group of commands on the ribbon and choose the source type that matches your backend. Confirm the service address or the file location, then authenticate.

Step 2: Choose the Linked Table Option

In the wizard, select the option that links to the data source by creating a linked table rather than importing the records. Importing at this point is what produces the snapshot problem described earlier.

Step 3: Verify and Refresh

After the table appears in the navigation pane, use the refresh command and confirm it completes without an error. Then change a single value at the source and refresh again: if the new value appears, the link is live.

Step 4: Confirm What Stays Read-Only

Try to edit a cell in the linked table. In most configurations the write is refused, which confirms you have a genuine linked table. A table that quietly accepts edits is usually an import.

Failure Triage

• Permission error on refresh: go back to the first two readiness checks.

• Connection error: verify the service address, credentials, and network path.

• Data looks unchanged: confirm the table was linked and not imported, then refresh rather than reopening the file.

Linked table compared with imported snapshot

Import a BDCM Connection Definition (Legacy Route)

This section describes a legacy route. Every step below is marked as an item to verify against current documentation, because the reference material for this workflow describes older product generations and the current equivalent path has not been confirmed here.

What a Definition File Is

A connection definition file packages the details of an external system: the endpoint, the operations, and the shape of the returned data. It is normally produced by an administrator rather than by the person who needs the table.

Import Through the Web Service Route

1. Obtain the connection definition file from your administrator.

2. In Access, start from External Data and choose the Web Service data source option.

3. Point the wizard at the definition file and select the external content type you need.

4. Choose whether to link or import, keeping in mind that only the linked option refreshes.

Verification Points Before You Rely On It

• Whether this definition format and its backend service remain supported in the version you run.

• Whether the client runtime requirement still applies.

• Whether a current, documented alternative exists for your environment.

When This Route Is Not Available

If your organisation does not run the backend service this route depends on, skip it entirely. Direct connections to the database, or exporting a file from the source system and importing that file, are the practical alternatives.

Legacy connection definition import steps

WPS Spreadsheet: The Same Data, Different Job

The Access side keeps the connection. The spreadsheet side works on a copy of the result, which is a different job with a different set of expectations. WPS Spreadsheet sits downstream in that chain, not in place of the database.

What WPS Spreadsheet Can Do With Exported Data

Open an exported CSV or XLSX file, clean it, add formulas and charts, and export the result for reporting or distribution. This is the analysis step that usually follows a refresh, and it is where spreadsheet features are genuinely useful.

What It Cannot Do

WPS Spreadsheet does not consume a database connection definition, does not work with external content types, and cannot refresh an Access linked table. Nothing in the file carries the live connection, so a spreadsheet that was built from an export will keep showing the data it was given.

A Safe Two-Step Handoff

1. Refresh in Access, then export the result to CSV or XLSX.

2. Open that file in WPS Spreadsheet for cleaning, calculation, and presentation.

The trade-off is explicit: after the export, the data no longer refreshes. If a regular update matters, repeat the export or ask your administrator about a direct database connection instead. You can start with the free desktop suite at WPS Office if the analysis step happens on a machine without a suite installed.

WPS Office suite interface overview

Use Word, Excel, and PPT for FREE

Free Download