Splitting a Microsoft Access Database

Splitting a Microsoft Access Database

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.

  1. Back up the database offline such as onto a DVD or USB drive. You never know when you will make an oops.
  2. Click Database Tools on the ribbon.
  3. Then Click Access Database

    Access Split Backend Database Tool

    Access Split Backend Database Tool

  4. Click Split Database

    Access Database Splitter

    Access Database Splitter

  5. Enter a name for the backend database. I recommend adding the word Data to the end of the original name. Click OK. when done.
Access Backend and Frontend

Access Backend and Frontend

The database is now split. It will now be much easier to manage especially when doing updates.

Now that it is split you will want to be able to re-link the backend tables automatically. Get our Re-link tool here.

Share on Google+Share on FacebookTweet about this on TwitterBuffer this pageShare on LinkedInEmail this to someoneDigg thisPin on Pinterest