databasedev.co.uk - database solutions and downloads for microsoft access

Split A Database

Splitting a Microsoft Access Database:

One of the important decisions that must be made before a Microsoft Access Database can be distributed is the form that the database will assume after the deployment. In this article, it addresses the pros and cons of splitting the database, and implementing the Database Splitter to create a front and back end database.

Generally, you will split a Microsoft Access application into two databases. One of these contains only the database tables, the other contains all of the queries, forms, reports, macros and modules. This is extremely important when moving an application to a multi-user environment. The database with the queries, forms, reports, macros and modules will be installed on each client machine, and the database that contains the source tables will be installed on the server.

Splitting the database will have several major benefits:

  • All users across the network share one common set of data
  • All users can update data at the same time
  • When the administrator needs to update the application (forms, reports etc.), they will not need to worry about interrupting processing of data or corrupting the data.

Installing a database on a desktop computer or server is a relatively simple task. Maintaining it is another matter entirely. Every time the information or the application changes in any way, every installation of the database has to be updated. Let’s say that you maintain one database that has 100 local users, and that you update data on a weekly basis. If each update takes 15 minutes, you’d be spending about 37.5 hours a week just keeping the data current. That doesn't leave much time for work, does it? If you know how to create a back end database, you can make a single update to the server, every user will be current and up to date, and you'll have plenty of time to do your job.

The Microsoft Access Database Splitter Wizard

In Access, using the Database Splitter Wizard, you can split a database into two files: one file (the back end) that contains only the tables, and a second (the front end) that contains the rest of the application, including shortcuts to your data access pages. Once you choose to split the database, you will be prompted to save the new file. The file name DatabaseName_be.mdb is supplied by default and a message box confirms a successful split.

Where’s the Data?

When you split a database, you create a front end and a back end. The back end contains the actual data tables. It is usually placed in a shared folder on the network and is linked to the front end file. The front end file contains all of the forms, queries, reports, macros, modules, and links to data access pages. The front end file is distributed to individual users for their workstations.

To Split or Not to Split?

It should be obvious that the primary reason to split a database is to allow multiple users to access the application. Microsoft Access 2003 allows a maximum of 255 simultaneous users. The split database is the most common configuration for production databases shared across a company or core group of users.

Advantages of Split Databases

Maintaining a distributed database requires careful attention to detail. The advantage of creating a back end file is that there is only one copy of the data to manage and protect. Any change in the data is immediately available to all authorized users. The database administrator doesn’t have to visit each workstation to keep all users up to date, thereby saving time and resources. The data files are housed on a server, so there is no need to be in the vicinity of a local workstation. For example, the server can be located at corporate headquarters in London, and employees located in Amsterdam, New York City, and flying over the Atlantic Ocean can be working simultaneously.

Disadvantages of Split Databases

Managing a production database requires a thorough understanding of front end usage in order to prevent disaster. When a local user opens an Access database, there is never a problem because most workstations allow only one keyboard to be used at a time (so the database gets one hit at a time). If a large number of users attempt to hit a back end database at the same time, a bottleneck can occur if the network and database are not set up to handle the traffic. All authorized users of the back end must have read/ write permissions because they must be able to write to the lock file ([FileName].ldb). Due to the fact that all of these users will have read/write access, thorough security measures must be implemented.

How to Split a Microsoft Access Database

Splitting a database into front and back ends is easy when you use the Database Splitter utility. To split a database:

  1. In Access 2003, open the database to be split.
  2. From the main menu, choose Tools » Database Utilities » Database Splitter.
  3. Click the Split Database button.
  4. The default file name for the back end will appear as DatabaseName_be.mdb. If necessary, rename the file.
  5. Browse to the server and directory where the file will be housed.
  6. Click the Split button.

    It may take a considerable amount of time for the split process to be completed, depending on the size and complexity of the database, the speed of the computer, and so forth.
  7. Click OK to dismiss the message that appears when the split is complete.
  8. Test both ends of the database to confirm that the proper links have been created and security has been implemented.

Optimizing Linked Database Tables

Once the database has been split, you will be working with linked tables, which Access then uses to retrieve the records from another file. This process can take time, especially when the tables reside on a network or in an SQL database or similar. When working with this external data, you can optimize the performance by following the points detailed below:

  • Limit the number of external records to be viewed. Create a query that specifies a criteria that limits the number of records returned from the external table. This query can then be used by other queries, forms or reports.
  • Avoid excessive movement in datasheets. View only the data you need in the datasheet. Avoid paging up and down and jumping to the first or last records in very large tables.
  • Avoid using functions in query criteria. This is especially true for aggregate functions, such as DCount or DTotal, which retrieve all records from the linked table automatically and then perform the query.
  • If you add records to an external linked table, create a form to add records and set the DataEntry property to True. This makes the form an entry form that starts with a blank record every time it's executed.
  • When working with tables in a multi-user environment, minimize locking records. This will free up records for other users.