This is part two of a three-part series of tutorials on Linked Tables in Microsoft Access. If you’ve missed out on part one, then you may want to take a look: Why create linked tables in Microsoft Access.
In this second part of the tutorial, we’re taking a look at the practical nuts and bolts, answering the question:
How do we create linked tables in Microsoft Access?
Microsoft Access makes this process remarkably simple.
Let’s start with a single database file that contains both the data and all the other components.
As you can see from figure 1, this database has various tables. Each one shows the standard Microsoft Access icon for a table, plus the name of the table. These tables sit within the current database file, and we want to move them into a separate file for all the reasons discussed above.
The steps to do so are as follows:
- Click the “Access Database” button within the Move group on the Database Tools tab of the ribbon:
Figure 2: The Database Tools tab of the ribbon provides various options for moving the data out of the current file.
- Having clicked confirmed on the welcome screen of the database splitter wizard that you want to go ahead and split the database, you will be prompted for a location in which to save your data file. Remember, this file will contain your tables, relationships and nothing else. It is not the file you will open to use your database normally – your “application” or “front-end” file will be the file that you open to use the database on a day-to-day basis:
Figure 3: Step two of the wizard: specify where the data file should be stored.
NOTE: It is vital that you store the data file in a location on the server to which everyone who needs to use the database has access. Furthermore, it should be accessible by all users with the same path – so for example, it should be in “J:/Data” for all users, not in a location which is “J:/Data” for some users but “P:/Data” for other users.
- That’s it!
Figure 4: The arrow icon next to each table’s name indicates that it’s a linked table
So, as you can see, the process is pretty simple. When you’re done, you’ll be able to identify that the tables in your database are actually links to tables physically stored elsewhere (in your Back End file) by the arrow icon next to each table (see Figure 4). Hover over the table name, and Microsoft Access will display a tool-tip to show you where the table is physically stored.
Conclusion 1: Linked tables are good. They should always be used when developing any database in order to allow for continuous development without affecting the data.
Conclusion 2: Converting “standard” tables to linked tables is easy. There’s a wizard that will do it for you, and all you have to do is decide on the best location to store the database file containing the back end.
In theory, that could be it and all about it. But in practice, there’s likely to be some maintenance. You’ll want to change where the “back end” file is stored. Or you’ll accidentally delete a linked table and need to get it back. Or you’ll want to convert a linked table back to a standard table for some reason.
In other words, there will be a bit of maintenance to be done from time to time. And that’s the subject of the third and final part of this series of tutorial posts on Linked Tables.
Part three: Maintenance of Linked Tables in Microsoft Access.