ONE OR TWO DATABASE CONTAINERS: KNOWING WHERE TO PUT THE PIECES

When first starting with Access, most developers create a single database for all the objects used with Access. This is very convenient because you have to update only one file when updating a system. This works great when it's a single-user system. For most multiuser systems, however, this isn't so great. Performance takes a big hit when you have all your applications out on the network.

Knowing What Should Go Where: An Overview

When working with a purely Jet environment, the ideal way to arrange your system when working on a network is to have your application components—queries, forms, reports, macros, and modules—in a local database. (Other components that you can keep locally for better performance are discussed shortly.) After splitting up a database, you have to relink the tables, which is covered in great detail in Chapter 26, “Startup Checking System Routines Using DAO,” and Chapter 27, “Startup Checking System Routines Using ADO.”

Shared data in tables is stored in a shared folder on the network. Figure 22.6 shows what should go where when dealing with splitting the database.

Now that you've seen what an Access application looks like when it's split up, it's time to look at the advantages and disadvantages, so that you have the whole picture.

Understanding the Advantages and Disadvantages of Splitting Databases

Overall, the benefits received by splitting databases overshadow any of the extra tasks that arise. Here are some of the advantages and disadvantages of splitting out databases.

Storing certain objects of an application locally, rather than on the server, tends to increase performance. How much performance improves depends on workstation hardware, the type of network, and the performance of the file server.

The following objects would be best kept locally:

• Application objects make up the application portion of the project: forms, queries, macros, and modules.

• Static objects are tables that are never updated, such as code tables. Generally, this data is updated only by the developer and administrator, and then sent out with an update of the front end.

• Semi-static objects are tables that are updated infrequently, such as user tables, and replicated by the developer from back end to front end. More explanation and code for this technique are provided in Chapter 28, “Creating Maintenance Routines.”

• Temporary objects are queries and tables that are created on-the-fly, and then deleted when the tasks are completed. Keeping these objects on the front end saves the hassle of worrying about other users overwriting your temporary tables and coming up with a naming scheme for them.

When you're working remotely and need to update the application, just sending the application database file is more efficient than sending both the application and all the data. The application likely will stay under 1MB in file size when compressed, whereas the data portion will continue to grow.

Also, you can provide users with new versions of the application database without affecting the shared database. By updating only the application database, the data is unaffected and the update process is much smoother than a single database application.

One possible disadvantage in splitting a database is that you have to come up with a means to automatically relink to tables. This routine is one that you need to include in your startup system checking (covered in Chapters 26 and 27). Figure 22.7 shows the dialog that appears when the back end is moved and the shared tables must be relinked.

When using Data Access Objects and VBA to link tables, you have to use either a dynaset- or snapshot-type recordset variable. To create a reference to a table recordset type variable, you must use the table from the back end itself. The following code sample shows how to create a recordset off a table in the back end:

Dim dbNet as database, tblCust as Recordset
Set dbNet = dbEngine(0).OpenDatabase("E:NetBackEnd.mdb")
Set tblCust = dbNet.OpenRecordset("Customers", dbOpenTable)

To create a reference that uses ADO, use the following:

Dim cnnCurr As New ADODB.Connection, rstCust As New ADODB.Recordset
cnnCurr.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data " & _
   "Source=E:Netackend.mdb;"
rstCust.Open "Customers", cnnCurr

Another disadvantage could be that you need to update two separate files—the front end and back end.

Splitting Databases Manually

Just as there's more than one way to skin a cat (where did that phrase come from?), there are a couple of ways to split a database. Here's one way to do so manually:

You then just have to maintain links if they get broken by a back end moving.

Working with the Database Splitter Wizard

Splitting a database by using the Database Splitter wizard is fairly straightforward. Starting out, you perform step 1 in the preceding section. To demonstrate how to use this wizard, open the database named FrontEnd.mdb, which contains two tables and two forms named Customers and Employees. You can find FrontEnd.mdb on the accompanying CD-ROM in the ExamplesChap22 folder.

To split this database after you open it, follow these steps:

The front- and back-end databases are both created. Now you need to look at other issues in Access multiuser development.