Splitting a Microsoft Access Database

Splitting a databse is the third step in improving the speed of a Microsoft Access database.

If you haven’t yet read or heard about splitting a Microsoft Access Database then either you are very new to databases or work in an I.T. department. Splitting a database is the process of separating the working

bits from the data bits. That is separating the forms, reports, queries and programming from the data. The two separate databases are called a front end (forms, queries, etc) and the back end (data).

Splitting the database only takes a couple of minutes.

There are very good reasons for splitting a database. MS Access works a lot faster if everyone is using their own front end and the front ends are all linked to the one back-end. Making programming (form, query or reports) changes to a live database causes havoc. The only solution to this in a non-split database, is to make changes in an offline copy, then manually export the changes to the live database. This is cumbersome. A far better, quicker and easier solution is to split the database once and for all.

It then becomes so much easier to rollout updates.

Even if you are the only user there are still good reasons to split the database.

It is possible to link the front end to a different back-end database for testing. So, for example, there might be a datafile called, MyData.mdb and MyTestData.mdb. This is particularly useful when testing queries that alter many records in one click such as update queries.

Splitting a database should happen before it is rolled out to users.

How to split a database.

Back up the database offline such as onto a DVD or USB drive. You never know when you will make an oops.
Click Database Tools on the ribbon.
Then Click Access Database
Access Split Backend Database Tool
Click Split Database
Access Database Splitter
Enter a name for the backend database. I recommend adding the word Data to the end of the original name. Click OK. when done.