Splitting a Database

Most of the databases we will create in our lessons are referred to as desktop databases because they are meant to be installed in the computer where they will be used. For maintenance purposes, the problem with this approach is that, if you distribute your database to the users or if you have distributed the database to a few people already, if you modify the database, you will have to visit each one's computer to install a new version of the database or to modify the database on each user's computer. For example, imagine that some or all users of the database call you and say that a certain form or report seems to produce a wrong result and you suspect that an expression in the database is false.

Once you find out what needs to be done to correct the problem, you would have to visit each computer to make the change. Also, if you want to backup the database, although you need only the values stored in the tables, you may have to visit each computer. Sometimes this is not possible and it is unprofessional. Microsoft Access provides an alternative to this problem.

You can divide your database in two parts. One part would be installed on one computer that can be a server or just another computer. This is referred to as back-end. The other part would be installed in each user's computer. These users computer can then connect to the back-end database that stores the actual database. The users computer have only the graphical interface that allows them to work. If you modify the database (for example if you correct an expression that was producing wrong results), you can do it on the back-end database and the users' computers would receive the change. To implement this scenario, you split your database.

To split a database, on the Ribbon, you can click Database Tools. In the Move Data section, you can click the Access Database button (if you receive a Microsoft Office Access Security Notice, read it and click Open). This would open the Database Splitter dialog box with four paragraphs and two buttons:

After reading them, you can click Split Database. This would open the Create Back-End Database dialog box. It would suggest a name of a database that is made of the original name of the database followed by _be. You can keep that name or change it. Once you are ready, you can click Split. The splitting process would start. When the splitting is over, you will receive a message box:

You can click OK.

After splitting a database, the tables would be removed from the Navigation Pane. The linking tables would have new icons and each icon would appear with an arrow that indicates that the table is only linked to an actual table in the back-end database. Also, after splitting a database, since the actual tables would have been removed from the Navigation Pane, you cannot modify their structure from a user's computer. That is, you cannot add a column or change the data type of a field.

After splitting the database, you can install the _be file, that was created when splitting, to the server or the computer that will host it. Then install the original database on the user's computers. You should (must) then establish a connection between each user's computer and the back-end server.

If you have created a database and decide to distribute it or to give it to a customer, colleagues or employees, they may be tempted to open the forms in Design View and possibly modify their structures. There are two disadvantages to such an action. They may compromise or corrupt your hard work, possibly rendering it obsolete. Some bad eye may decide to cheat on your VBA code. Microsoft Access allows you to make some or most of these actions difficult or impossible. An ACCDE file is a database compiled by Microsoft Access so the users of the database cannot change the design of forms or reports, create new forms or reports, view its VBA code, or import forms, reports or VBA code.

Before creating an ACCDE file, you should enable the security. To do this, on the Message Bar, you can click Options... In the Microsoft Office Security Options dialog box, click the Enable This Content radio button and click OK. To create an ACCDE file of your database, after opening it, on the Ribbon, you can click Database Tools. In the Database Tools section, you can click Make ACCDE. You will receive the Save As dialog box with a suggested file. You can accept that file name or replace it. After this, you can click Save.