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.
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.”
Note
By splitting your databases, you are in fact creating a back end and a front end.
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.
Note
Along with the database file containing the shared data on the server, the file called System.mdw should also be stored there as well. This file contains various information for a workgroup, including user and group information dealing with security. For more information about this file and security, see Chapter 21, “Securing Your Application.”
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.
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.
Note
Make sure that you have enough local hard disk space to handle the largest size a temporary table will likely become.
Note
As mentioned in the beginning of the last section, this setup (splitting the front end from the back end) works best for a pure Jet environment, where the front- and back-end databases are both .mdb. When working with an ADP and client/server environment, the queries likely will be stored as views or stored procedures and will be located on the server. Also, you won't store temporary tables in the ADP, but will instead use persisted recordsets or temporary views. To find out more about this setup, see Chapter 25.
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.
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:
1. |
Create the database as a normal single-station system with all the elements in the same database container. |
2. |
Create a second database with the back-end name you want to use. |
3. |
Open the original table and export the tables you want shared into the new database. |
4. |
Delete the tables that were exported from the original database. |
5. |
Link all the shared tables from the back end to the front end. |
You then just have to maintain links if they get broken by a back end moving.
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:
1. |
From the Tools menu, choose Add-Ins and then Database Splitter. The first dialog (see Figure 22.8) warns you to back up the database and that this process could take awhile. Figure 22.8. Heed the warnings given on this first dialog of the Database Splitter wizard. |
2. |
Choose Split Database. The Create Back-end Database dialog appears. |
3. |
Type backend.mdb in the File Name text box (see Figure 22.9). Figure 22.9. Use the filename backend.mdb for the new shared database. |
4. |
The front- and back-end databases are both created. Now you need to look at other issues in Access multiuser development.
3.15.34.39