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:
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.
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.
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.
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.
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.
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.
Splitting a database into front and back ends is easy when you use the Database Splitter utility. To split a database:
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: