Argee's Access Blog

The term ‘split database’ seems to strike fear and trepidation into the hearts of people just getting to learn about creating Access applications. This article is for Soma, who raised the question privately with me. Thanks for asking Soma, I hope these few notes help you to a better understanding of what a split database is and why you should split it.

What does ‘split database’ mean?

An  Access file (.mdb 2003 and earlier, .accdb – 2007 & 2010) can contain both user interface and the data of the database. Think of the user interface as being everything you need to display data to the user in useful and meaningful ways, everything, in fact, except the actual tables and relationships between them.

A split database architecture separates the user interface from the data. In Access, the user interface ‘lives’ in a file known as the frontend. This file contains all of the application’s forms, reports, and queries. The backend, on the other hand, is a second file that contains the tables and relationships of the database.

Why should a database be split?

Separating the user interface and data storage functions into two separate files makes the database easier to manage and greatly simplifies the enhancement and deployment of frontend modifications.

In a multiuser environment, a split database is mandatory so that all users can share and work with the same data. That is because their individual copies of the frontend all link to the same backend. To put it in somewhat techy terms, one data source serves all users of the database. If each user had his or her own copy of the data, managing changes to the data and keeping all copies of the data ‘in sync’ adds a whole new layer of complexity to managing the data.

However, even if there is only one user, a split database still makes a lot of sense. That is because you can continue to develop and enhance (and replace) the frontend as needed without jeopardized your valuable data. In my own applications, for example, I have a frontend that I use on a day to day basis. I have a second, development, version of the front end where I try out new ideas for form and report design. The development frontend is linked, not to my live data, but to a test dataset that I copy from my live data from time to time. When I am satisfied that the development frontend is working correctly and has been properly tested, I put it ‘into production’ by linking it to my real frontend. (Incidentally, UtterAccess moderator Cybercow has posted a nice utility for switching a frontend’s between test and live data in the UtterAccess Code Archive

How do you split a database?

Generally, splitting a newly developed Access database is quite straightforward. Access includes a Split Database wizard that will move the original database’s tables and relationships to a new file. There is also the Linked Table Manager to allow you to manually link a frontend to the backend when necessary.

This article is intended to be a brief overview of what it means to have a split database. The Access help files contain more complete descriptions of the concept and how to go about splitting your database. You might also what to do an advances search for ‘split database’ at UtterAccess where you will find many questions on the topic posted by many new members over the years.