Linked tables in Microsoft Access

Linked tables are strange. Anyone who spends any time developing databases in Microsoft Access will always split their database into a front end and a back end. The front end will contain linked tables which are so called because they are linked to the back end.

All this is straightforward.

Except that it’s not if you’ve never had it explained properly.

Here’s the thing. I’ve had two clients in the past two weeks ask me seemingly simple questions about linked tables. One was a client for whom I’ve developed a Microsoft Access database which has been in use for a year or more now, and, of course uses linked tables. The other was a company for whom I ran a fast-paced one-day course in Access databases, while also working with them to put together a simple database to allow them to import, transform and query their data.

And so it occurred to me that – like so many things, not just in Access but elsewhere in IT and indeed life – those who are in the know make far too many assumptions about the knowledge of those who are not doing what they’re doing all day, every day. I’m sure to a cardiovascular surgeon it goes without saying that we all know exactly how the heart works. Except, of course, we don’t.

So, as the second in our series of posts about Microsoft Access basics, here’s a guide to Linked Tables in Microsoft Access. If there are things that still aren’t clear, just use the comments below to ask questions, and I’ll do my best to answer.

In fact, this is the second, third and fourth posts in the series, as I’ve broken this tutorial down into three parts:
Part one: The rationale – why create linked tables? And what are they anyway?
Part two: The technique – how to create linked tables
Part three: Managing linked tables – such as how to re-link the tables in a Microsoft Access database, or how to link a new table to an existing database.

Let’s crack on and start with…

What is a linked table?

You probably know already that the role of a table is to store data (and if you’d find a refresher on what each of the access objects do, take a look at our post on the basics of Microsoft Access objects).

But it’s worth asking the question – where should that data be stored?

The obvious answer would be “In the database, of course”.

But here’s the thing – a Microsoft Access database file (either an MDB file or an ACCDB file) can store data, plus lots of other things too.. In addition to the data, the database file may store the forms and reports that display the data on screen or on paper, plus the queries that extract the data and all sorts of programming logic (in macros and modules) to make the database work as a whole.

It takes time to put these objects together. More to the point, in anything other than a simple database, it is always an ongoing process – you create a form, then you need to tweak it, to expand its functionality, to bug-fix it, to add another piece of information or another button. Or the form is perfect, and so one of the users puts in a request for “something like that form, but for this other set of data”. Or you have a great report that shows the current stock levels of all the parts in your manufacturing process, and someone then asks if you can create one that shows how many parts of each type have been used over the past 6 weeks.

In other words, developing the database is a process that never ends.

Where did my data go?!

Here’s the thing. When you need to make a change, how do you do it? Let’s say that at 5pm on 1st December you take a copy of the .accdb (or .mdb) file from the server, with all its objects, and spend a couple of days working on it. At 9am on 4th December, you then upload your “new and improved” copy of the file back to that same location on the server. This means that the forms, queries and reports may be new and improved, but the data you’ve uploaded on December 4th is identical to the way it was at the end of 1st December. Any additions, changes, deletions and so on performed by people in the intervening 3 days are gone!

One option might be to say to the staff “I’m going to be making some changes to the database for the next few days, so you won’t be able to use it”. But you may have trouble keeping your job if you do that.

So here’s the solution.

Separate the application from the data

By the application, we’re not talking about Microsoft Access that you’re using to develop the database itself. Instead, we mean the front-end user interface – the forms. Also, the reports. Also all the queries that provide the data to those interface objects, and the code that makes them do what they do.

The Microsoft Access Objects pane

Figure 1: The Microsoft Access object pane, showing various non-linked tables

In other words, everything except the tables – the data itself.

The way we do that is to create two files. The first file (the ‘Back end’ file) contains the data, the second file (the ‘Front end’ file) contains all the other objects, plus a set of links to the tables. As far as all the forms, queries, reports and other objects are concerned, everything works just as though the data was in this file too. But it isn’t – instead the data is in the Back End file, and this front end file simply contains these links – but no actual data.

The practical implication of this is that you can now take a copy of the Front End file and play with the forms, queries, reports and other objects to your heart’s content. And when the work is done, you can disconnect the old copy of the front end file, and connect your new copy of the front end instead. And because you’re connecting to the same data file as the old front end was connected to, any changes to that data that have been made while you’ve been working on your new Front End file will still be available.

Hopefully, you can see all the benefits of working this way, with a database split into separate Front End and Back End files. So, in the next part of this tutorial, we’ll be looking at the big question…

How do you split a database into a Front End and a Back End?

If you’re ready to roll your sleeves up and get on the nitty gritty, just click the link below and we’ll see you there!

Part two: how to create linked tables in Microsoft Access