Microsoft Office 2000/Visual Basic Programmer's Guide

The Two-Database Approach

When developing multiuser database solutions with Access, you may find it helpful to split your objects into two databases. This approach offers many advantages. These two databases are known as the front-end database and the back-end database.

The front-end database has three basic characteristics:

• It contains non-data objects, such as saved queries, forms, reports, macros, and modules. It can also contain additional objects created by the user for personal use.
  
  
• It serves as an area for managing temporary objects. Most multiuser solutions perform some type of data access that is temporary in nature: creating tables with transient data, for example.
  
  
• You distribute a copy for each user to run from his or her workstation.

The back-end database contains the tables that hold your solution's data. The tables in this database are accessed by your solution through links in the front-end database. Figure 16.1 illustrates the two-database approach.

Figure 16.1 The Two-Database Approach

If you know from the beginning that you intend to split your solution into two database files, you can develop it with this in mind. Or you can keep tables and objects together in the same file and split them only when you're finished with development and are ready to distribute the solution. The easiest way to split a solution after creating its objects is to use the Database Splitter Wizard.

To split an Access solution after creating its objects

1. On the Tools menu, point to Add-ins, and then click Database Splitter.
   
   
2. Follow the instructions in the dialog boxes that appear.

The Database Splitter Wizard creates a new, empty back-end database. It moves all tables in the current database to the new back-end database, preserving table relationships and properties. The current database is now the front-end database. The wizard then links each table in the back-end database to the front-end database. For information about linking tables by using ADO code, see Chapter 14, "Working with the Data Access Components of an Office Solution."

The links used by linked tables in the front-end database are based on the location of the back-end database. If users move the back-end database to a different location, the links will fail. If the current links are broken, you can automate the process of linking tables for your users by prompting them for the path to the back-end database when they start your solution. Then you can refresh the links to your solution's tables. For an example of how to do this, see the RefreshLinks procedure in Chapter 14, "Working with the Data Access Components of an Office Solution" (the procedure can also be found in the CreateDatabase module in the DataAccess.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH14 subfolder on the Office 2000 Developer CD-ROM).

The advantages of using the two-database approach in a multiuser environment are as follows:

• By storing the front-end database on the user's workstation, there is no contention for temporary objects, such as creating a temporary table, because only one user has the front-end database open.
  
  
• By storing application-specific objects that are typically static in nature on the user's workstation, the amount of network traffic that occurs while your solution runs is minimized.