In a large organization, different people will have different uses for the information in a database. They might want to develop their own variations of your queries, forms, and reports, or even create their own. Allowing dozens of people to edit the objects in a database leads at best to confusion and at worst to disaster.
One easy solution to this problem is to split the database into a back-end database, containing the tables, and a front-end database, containing the other database objects. You can store the back-end database on a server and distribute the front-end database to all the people who work with the data. They can use the queries, forms, reports, pages, macros, and Microsoft Visual Basic for Applications (VBA) code that you developed, or they can write their own. Although everyone still has access to the data in the tables, and there is still some potential for corrupting the data, the rest of your database objects are secure.
Tip
Before splitting a database, you should make a backup copy of it. To back up an open database, on the File menu, click Back Up Database. Access appends the date to the database name and offers to store it in the same folder.
In this exercise, you will split the GardenCo database into back-end and front-end components.
USE the GardenCo database in the practice file folder for this topic. This practice file is located in the My DocumentsMicrosoft PressAccess 2003 SBSSecureSplit folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Access 2003 Step by Step.
OPEN the GardenCo database and acknowledge the safety warning, if necessary.
On the Tools menu, point to Database Utilities, and click Database Splitter.
The Database Splitter Wizard appears.
The Create Back-end Database dialog box appears so that you can specify where the back-end database should be stored and provide a name.
In the Create Back-end Database dialog box, navigate to the My DocumentsMicrosoft PressAccess 2003 SBSSecureSplit folder, and click Split to accept the default name of
GardenCo_be.mdb.After the database is successfully split, click OK to return to the database window.
Notice that now, in the list of tables, each table name is preceded by an arrow, indicating that it is linked to a table in a different database.
Attempt to open the Categories table in Design view.
A message box appears informing you that this is a linked table with some properties that can’t be modified.
Click Yes to open the table, and then click each field name in the top section of the Design view window.
A message displayed in red in the Field Properties section informs you that the properties for the selected field cannot be modified. If you click the properties in the lower part of the window, you will find that some can be changed and some can’t.
Close the table.
On the Objects bar, click the other object types.
Each type appears to be intact, and you can modify the object if necessary.
Close GardenCo, and open GardenCo_be.
This database contains only tables. Other types of objects are listed on the Objects bar but do not exist in this database.
Open the Categories table in Design view.
The properties of the fields in the table in this database can be modified.
Close the table.
CLOSE the GardenCo_be database.