the Access Experts: Custom Database Solutions

Home
Services
Products
Links
Support
Portfolio
About Us

 

Why Split a Database?
 
There are a number of reasons for splitting an Access database into a front-end and a back-end, where the back-end contains only tables, and the front-end contains queries, forms, reports and modules. More or less in the order of impact, they are:
  • Significant performance benefits can be achieved if the back-end is on a server and the front-end is located on the local hard drive of the users’ PC. This arrangement loads forms and reports from the local hard drive, which is almost always faster than loading them from a LAN connection to a server, and it also reduces the traffic on the LAN by only sending the requested data, and not forms and reports. Note however that the data passed is the same, as all query activity occurs on the local PC. To gain further performance benefits in this area, you should consider a database server such as SQL Server or Oracle.
  • Updating or adding to the design of queries, forms, reports and modules is made easier, as you can replace the front-end on each PC on an as-needed basis. Otherwise, in a shared database, all users should exit the database before you make design changes. With Access 2000 and beyond, all other users must exit the database before you make changes to forms, reports or modules.
  • Using local front-ends will provide a more robust solution. Most corruption in databases occurs in the forms, reports and modules objects. Thus a local front-end that goes corrupt only affects that specific user, and can be replaced without requiring all users to exit the database.
  • This arrangement makes it easier to debug problems experienced by only one, or a few users, and not by others.  Individual users often do tasks or use procedures that others don’t, and it becomes easier to narrow down the specific causes of problems.  If all users are running from a common database, this can be a very difficult situation to sort out, as one user may cause a number of users to have problems, and it isn’t clear who caused the initial problem.
  • This arrangement allows users to create local objects in their own copy of the front-end without cluttering up the environment for other users.  Please note that early versions of the switchboard manager required that the switchboard table be a local table – the situation with Access 2000/2002 doesn’t appear to be documented.
  • Users can’t accidentally (or deliberately) delete a table in this configuration – they can only delete the link to the table. Of course security can be implemented to prevent that as well, but the bottom line with an Access database is that all users must have edit permissions on the .mdb file, which in some cases means they could theoretically delete the actual file. Backup and do it frequently is the bottom line.
  • This configuration makes it easy to switch between a test version and a live version of the database if you need to verify design changes or do training for a new user. The linked table manager can be used for this, or you can write code to do your own relinking.
  • If you have a situation where some people are running Access 97 on their workstation and others are running Access 2000 or 2002, you can use an Access 97 back-end database, and do a version of the front-end in Access 97 for people with that release, and a second front-end version in Access 2000 for people running 2000/2002.  Also note that Access 2002 can run an Access 2000 databases in native mode with no apparent performance penalty.

 

How to split a database?

Splitting a database is a fairly easy task – in fact Microsoft provides a wizard as part of Access that will do the job for you.  You might want to take that approach the first time you do it.  The wizard can be found under the Tools / Database Utilities / Database Splitter and walks you through the process.  If you want to do it manually there are two processes you can use.  The first is:

  1. Make a copy of the database!
  2. Delete all the tables from the copy
  3. Delete all the queries, forms, reports, macros and modules from the original
  4. In the copy, use the Linked Tables Manager to link to all the tables in the original
  5. Compact and repair each database

Of course you should make a backup of the database before you start.  The second process is:

  1. Create an empty database (the back-end)
  2. Import all of the tables into it
  3. Create a second empty database (the front-end)
  4. Import all of the queries, forms, reports, macros and modules into it
  5. Use the Linked Tables manager to link to all the tables in the back-end

This process has the advantage of providing a back-up, and may clean up a few problems in the process, resulting in smaller sizes for both databases, so we typically either use the wizard or this second process.

 

Are there downsides to splitting a database?

 As with most technology options, there are some aspects of split databases that will raise issues in certain circumstances.  The significant ones are: 

  • Copying a database to a workstation can be a tedious, and deploying frequent design changes are likely to make it an even bigger burden.  A common solution is to use a small batch file to copy the front-end to the workstation hard drive each time the workstation is rebooted, and that works well for small networks with 5 to 10 users.
  • However, replacing the front-end on 50 or 100 workstations can become a burdensome task, and can create a significant load on the network.  We have developed a tool known as DBLauncher that automates the task of downloading both databases and components, and will work with multiple databases.  Also, if multiple developers are working on a given front-end, you should seriously consider using Microsoft Visual Source Safe or another source control system.
  • When working with an Access 97 back-end connected to an Access 2000/2002 front-end, you should be aware that Access 2000 users will see a noticeable decline in performance compared with Access 97 users. There is also anecdotal evidence that corruption problems in the back-end may occur more frequently in this configuration.  This should be used as a temporary expedient until all workstations are converted to Access 2000.
  • When users are allowed to create local objects in the front-end, considerable care and planning is required for maintenance updates, as local objects will normally be lost when the front-end is replaced.  

What’s the bottom line?

In summary, we recommend that you always use a split database approach, even if you are a single user working on a local hard drive because, of performance improvements, the reduction in problems with corruption that are caused most often by hardware or software crashes, and the improved development conditions.

 

Copyright © 1998-2011 Wendell Bell & Associates Inc.  Last updated on August 1, 2011