Using the Access Database Splitter

Last year I wrote a post on Linking to an External Data Source.  This is where we split an Access database  into a Back End (which stores all the tables and data), and a Front End (which contains all the forms, queries and reports).  There are a number of advantages to observing this practice.  To begin with, splitting a database file in this way is all but essential if it is to be deployed on a network in a multi-user environment. Another big advantage is that we can easily replace a front end file with a new updated version with minimal disruption - the actual data stored in the back end tables is not touched in this process. 

If you read the my post last year, you may remember that I showed you how to link a front end Access file to an existing back end database.  This is fine if the back end database already exists, or if you decide to create the application in two separate files from the start.  However, many developers create or inherit a complete database application as one stand alone file, and then proceed to split the database.  Splitting the database can be done manually, but this is often time consuming and there is always the possibility of making errors in the process.  An easier and quicker way of doing this is by using the Access Database Splitter.  This is a wizard that splits the database into a front and back end, as described above.  

Splitting an Access Database

The database that I am working with here is an Order Management System containing a number of tables.  If you want to try this on one of your own database files, it does not matter how many tables it contains - it could have a hundred or it could just have one.  

Figure 1

1. Click the ACCESS DATABASE icon (located in the MOVE DATA group of the DATABASE TOOLS ribbon).  See figure 1 on on the right.  This opens the DATABASE SPLITTER Wizard.
2. Click the SPLIT DATABASE button at the bottom of the Wizard (see figure 2).   
3. You are now asked to select one of your folders for the new back end Access file.  Browse to a location of your choice, enter a back end file name, and then click SPLIT.  After a moment or two, you should get a message box saying "Database Successfully Split".
4. Click OK.

Figure 2: The Database Splitter Wizard from Stage 2.

You can now look at the navigation pane of the front end database and see the linked tables (see figure 3).  You can try adding data to one of your tables and then open the back end file to see it stored there.  You could also try copying the front end file in order to access the back end tables with different front ends.  In so doing you can see the basic principle behind how multi-user systems operate*.

Figure 3 (above): This is how linked tables appear in the Front End Database file.

Figure 4:  Your Database is now split into two files one for the front end and one for the back end.

*Tip:  If you want to run more that one Access front end at a time, you will need to open multiple instances of the Access Database.  So rather than clicking on the access front end file directly (ie from within Windows Explorer), you should open Microsoft Office Access from the windows START button or DESK TOP and then open each front end file from the GETTING STARTED WITH MICROSOFT OFFICE ACCESS screen.   

Linking Access to an External Data Source

There are many reasons an Access Developer needs or chooses to link an Access database to an external Data Source.  One common reason is to increase efficiency when a team of users need simultaneous access to a database across a Local Area Network. A common practice is to store an Access file containing the database tables in a shared folder.  This is referred to the Server or Back End.  Individual Users then have a local Access database stored on their own PC's which is linked to this Server.  Each one of these local database files contains all the Access Forms, Queries, and Reports, and are referred to as Client's or Front End's.  The logic behind this set up is that once data is downloaded from the Server, any processing that is required can then be done locally, thereby freeing the Server to deliver information to other Client's on the network.

In this exercise we are going to have a go at linking an Access Database to an External Data Source.   You don't need to be on a network to try this.  The general principle of Linking works exactly the same when the Client and Server database files are stored on the same machine, and even in the same folder.

Before you begin, you will need to create a new database containing a table. Call the database LinkTestServer.accdb. This will be the data source that we will be linking to.  A simple table of made up names will do fine for this.  Alternatively, you can download this example Link Test Server Database to use for the purpose.  Once you have done this we can begin by creating the Client database, and then link it to the Server file.

1. Open Access and Create a New Database.  You can save it in the same folder as the Server file.
2. Select the EXTERNAL DATA tab on the Access Ribbon.
3. In the Ribbon's IMPORT group, click the IMPORT ACCESS DATABASE icon.
   
   
   
   
4. This opens the GET EXTERNAL DATA dialogue box.  Here we need to browse and select the name of the Access Database that we are going to use as our Server or Data Source. You will also need to click the lower option box where it says LINK TO THE DATA SOURCE BY CREATING A LINKED TABLE.
   
   
   
   
   
5. Click OK.
6. The LINK TABLES dialogue box now opens. This lists all the tables in the Server Database.  In our example there is just one: tblCustomer.  Click the name so it is highlighted in blue.
7. Click OK to complete the linking process.

You should now see the linked table represented in the NAVIGATION PANE of your Client database.

As you can see from the screen shot above, it looks similar to an ordinary 'native' table, except there is a blue arrow to its left, indicating it is a linked table.  You can now open it from within the Client database and add additional names to it as if it was a native table.  You may also base Forms on it, Query it and Create Reports from it too.  It is just the same as working with a native table except you cannot modify the table design (eg add or change fields ... etc).  The table is still located externally, but any additions, deletions or edits you make to the table's data from the Client database is reflected in the data stored in the Server database. You may like to experiment with this and see for yourself.