This is the final part of a three-part series of tutorials on Linked Tables in Microsoft Access. If you’ve missed out on the previous parts, then you may want to take a look: Part one: Why create linked tables in Microsoft Access and then Part two: How to create linked tables in Microsoft Access.
In this last part of the tutorial, we’re taking a look at what you’ll need to do to keep your newly split database in good working order. Essentially, we’re answering the question:
How do we manage linked tables in Microsoft Access?
The theory would be that linked tables need no management – they just work. And to a large extent, that’s true. But there will still be occasions when you think “Huh?” or “Oh no…” and you just need to know what to do next. So, here are our top three tips for managing Linked Tables.
1. Remember that it’s a linked table
If you’ve been working with non-split databases, where the data resides in local tables (ie not linked tables) then you’ll be used to making design changes to your tables by simply opening them in design view. But when you try the same thing in a linked table, you’re politely but firmly told “No chance”:
Figure 5: The design of linked tables cannot be modified
Remember that the actual data resides in the “back end” file – and it’s this file that needs to be opened to modify the design of the table. Once you’ve made whatever changes you required, you can then open the design of the table in the “front end” file to bring them across to your application.
2. Re-linking tables
This can be necessary if you move the back end to a different location, or if you make changes to a number of your tables in the back end. (As mentioned above, opening the linked – front end – table in design view will bring across any design changes, but you don’t want to have to do that for lots of tables in turn).
Again, Microsoft Access makes this a simple process. Simply right-click on any of your linked tables and select Linked Table Manager. The following screen will appear:
Figure 6: The linked table manager
If you’re bringing across design changes, but the location of the back end file hasn’t changed, you can simply select the tables to be refreshed, and click OK. You should then get the message that all tables were succesfully refreshed. But if the back end file has moved and so the tables cannot be found where Microsoft Access thought they were, you will be prompted for the new location.
Note that if you are moving the back end – or want to link the application to a different back end – but the current back end file is still in its original location, Microsoft Access won’t ask you for the location to which to link the tables unless you tick the box labelled “Always prompt for new location”.
3: Adding a link to another table
It may be that you need to add a new table to your database. But now that your tables all reside in the back end file, you’ll want to create the new table there too. This then raises the question of how you link this new table to your existing front end file.
In order to do this, you’ll need to go to the “External Data” tab on the ribbon and look for the “Access” button in the Import & Link group. Do make sure that you use this group – there’s also an Access button in the Export group of the ribbon but you won’t want this one.
When you click this button, you will be asked for the location of the file containing the new objects, and also whether you want to import them or link them:
Figure 7: The Get External Data dialog box
It’s important that you click the second option to link your table, or you’ll end up with a copy in the back end and another copy in the front end!
On the second stage of the wizard, you will be prompted for which table(s) and other objects you want to link. Select your new tables and click OK, and Microsoft Access will take care of the rest.
So there we have it. Microsoft Access does a good job of giving you the tools you need to split your database, and then to manage your split database once you’ve done so.
As we discussed in the first post in this mini-series, there are lots of good reasons to split your database, and the tools are there to help you do so. If you have any questions, or any other ideas on tips, tools and techniques around splitting a database, please do let us know your thoughts through the comments below.
In conclusion – two more reasons to split a database
There are, actually, a couple more reasons why you should split a database, which we didn’t cover in the first post in this series because they’re not, perhaps, the primary drivers for people wanting to split a database for the first time (who are the likely audience of these articles).
Firstly, size. Access allows for much larger sets of data than Excel – multi-million row tables are certainly possible. But there may still come a point when you cant fit all your data into your database; Microsoft Access has a limit of 2GB per database.
By splitting the database, you can have some tables in one back end file, and some in another, effectively doubling the available space. Furthermore, although we’ve been concerned here about splitting the database into two Access files, the same tools can also be used (with a few different settings, of course) to link to tables stored in a much larger database system such as Microsoft SQL Server, and then the sky is pretty much the limit when it comes to size.
The second reason for splitting a database that we’ve not mentioned before is to avoid corruption. When you’re deeply involved in creating your ultimate Access database, you may well be using VBA code to extend what it does. And unfortunately, it’s not unknown for Access to crash occasionally… That’s why you MUST take regular backups, so that the risk of losing large amounts of your design work is minimized.
However, it’s one thing to lose three hours of development work… to lose potentially thousands of rows (or more) of data too can be disastrous. This risk is minimised by moving the data into a separate file. It’s really pretty rare for this back end file to cause a crash and be corrupted – after all you hardly ever have it open directly (it’s just connected to by your application file). So when (sadly, it probably is “when” rather than “if”) there is a crash, if your data is safely stowed away in a separate file, at least you can be safe in the knowledge that it’s only your work that may be corrupted, not everyone else’s data!