Creating a Split Microsoft Access Database
Splitting a database means separating the front end user interface from the main data tables themselves. This can be done with separate Access files or a database server application and allows better control over the data itself and the application end users use to work with that data.
This is most important when multiple users will be accessing the database at the same time. The simplest way to accomplish this is by using the table linking functions within Access. This creates a minimum of 2 files to use the database, commonly referred to as:
Front End – the file the user will open containing all queries, forms, and reports
Back End – The file containing the table objects themselves
Linking Tables
Microsoft Access is capable of linking all kinds of tables for use in the database.
- The table Category above has a tiny arrow next to it, indicating it is linked from another database file. Right clicking on the table and selecting linked table manager allows you to view and maintain where the links go.
The tables can exist anywhere on your drives or network, but you should follow a few guidelines to simplify your life.
Quick Tips for Linked Access Tables
- Keep the tables for each front end database in one back end database. That will make it much easier to maintain.
- Use absolute UNC network names when linking tables on a network drive, such as “\servershare”. That will keep you from having to rely on a mapped network drive.
- Everyone using the database must have read/write privileges on the chosen network location. Even if a user is only reading data, Access will create lock files so they need write privilege as well.