How to Split an Access 2007 Database

Most often, the reason that you may want to split a database is you want to provide multi-user access to the database. What splitting can achieve is create a backend database that holds all the tables. A frontend database is created that holds all the forms, queries, reports and macros and the modules. In a multi-user configuration you house the back-end database on a file server and provide a copy of the front end to every user that needs access to the database. The front end links to the backend database thus providing multi-user access to the main set of tables. The splitting is possible with not only Access 2007 but also Access 2003 and Access 2002 Standard edition as well. A word of caution though, this process should be handled by some expert level Access users, who would understand the issues related to the multi-user configuration and interoperability of the front and backend. The process should work with both the MDB and the ACCDB files.

When deployed the forms are not sent from the database but open from the frontend locally and only the data actually entered moves to the backend tables. This reduces the data traffic over you network.

Besides making your database multi-user, there could be other motivations for splitting the database. You may not want to let many people access the tables so that they can make changes to it. In a monolithic version this is possible for any user. You may also have several people developing the project in the database and there is no means of ensuring uncontrolled changes to the monolithic form of the application. One would need some kind of a source code control system to ensure that.

With splitting, the application becomes multi-user and that is a large benefit. The performance should improve significantly as main interactions are all local and on individual machines. Only the required data travels back and forth over the network. Thus time responses should improve all round. In the monolithic form the application is stored on a network shared directory on a file server. When users open a form or other elements they have to be sent over to his workstation over the network. Beyond a small size, this is inconvenient, time consuming and generates a lot of network traffic that could bog down others. Except for really simple and small databases or single user uses it makes sense to split the database.

There is database splitter wizard available with Access 2007 as well as 2003 versions. You could use that or there is a process to follow to create the split. The splitter utility may be the path to take for less experienced users. True experts will be able to optimize the application split by doing the split manually.

This process works through the database splitter. You locate and activate the wizard. Tools to Database Utilities to Data splitter is the route to take on Access 2003, while the route for Access 2007 is Database Tools to Move Data to Access Back-end. The wizard tells you what the results are. It’ll tell you that it is going to create a back end containing table and a front end on which developers can continue to work without affecting data or users. You will need to provide a name and location fro the backend file and you are all set to split. The process takes fair amount of time. The wizard will warn you of that fact too. It’ll warn you about keeping a back-up, just in case. You should do that before you proceed with the split. Once the splitting is complete you get a message that the splitting is done. On confirming, you are ready to test the split and you should do that thoroughly to make sure links both ways work and security is implemented correctly. On satisfactory testing you are ready to deploy the split database and meet your goal of performance, continuing development or a just plain multi-user implementation.

This is not for the faint-hearted, and certainly not for inexperienced users. You need a lot of knowledge as well as skills to ensure that you can do the split properly and the backend and the front end work together well. The underlying process is the same with either the Access 2007 or the 2003 version. Because of the differences in the menu structure the actual route taken through the menu items is a little different, even the actual menu items are sometimes different. The essential sequence of things do is to create a new database, get the database table imported into it and link the tables to the front end. This is the backend database. This needs to be housed on a network share and get users full access to it. A second database should import all the other objects of the original database. If you need the exact sequence of commands in either the Access 2007 or the 2003 version, this here is an excellent exposition from MS. Look it up and simply follow the sequence and you are done.