One of the most useful features of an Access database is its ability to be used by multiple users at the same time. This is known as concurrency. But there is a right and a wrong way to do this.
Inexperienced users will usually start by simply copying the entire database to a network location and create shortcuts on the users' desktops. However, over time they discover that this method is not without its faults. Issues ranging from locking contentions to outright corruption of the database soon become apparent.
This article is meant to provide the best practices for sharing a database with others. Incorporated into these best practices are the concepts of maximum security, future manageability, and higher degrees of scalability. Simply put, your database will be as secure as possible, as easy to modify as possible, and as easy to migrate to a larger platform when and if you ever outgrow Access.
What is the correct method to share an Access database?
A correctly shared database will be split into two parts. One backend database (hereafter referred to as BE) located at a shared location, and one copy of the frontend database (hereafter referred to as FE) for each user.
How do I split a database?
Always backup any database before making changes of any kind.
Microsoft Access has a built-in utility for assisting you with the splitting process.
Choose Tools> Database Utilities> Database Splitter (Access 2003 and earlier)
Choose Database Tools Ribbon > Move Data Group > Access Database (Access 2007 and later)
These tools provides the same functionality as splitting the database manually as described below.
Splitting a database manually
Create the BE
Create a brand new, empty .MDB or .ACCDB Import all the tables from your original .MDB into it by doing the following:
You now have a BE.
What do I do with the BE?
The BE should reside permanently at your network share.
Note: This new BE should be named differently than your FE. (Ex: FE name = Accounting.MDB, BE name = Accounting_BE.MDB) Additionally, all of your users that will update data will need Read/Write/Delete permissions at this location. If users will open the database to generate reports only, they may have just Read privileges.
Create the FE
Open your original .MDB or .ACCDB Delete all tables and their relationships (if any). Link to the tables in your newly created BE. Make sure your BE is in its permanent network location. Link them by doing the following:
You now have a FE.
If you change the BE location after the links in your FE are created, you can specify a different file using the Linked Table Manager.
What do I do with the FE? This new .MDB (or .ACCDB) file becomes your new development copy. Guard it with your life. Back it up frequently, as well as the new BE you have just created which houses all of your data. Design changes from now on will take place in this copy and this copy alone.
What do I distribute to my users? Individual copies of this new FE are what gets distributed to your users.
IMPORTANT: EACH USER GETS THEIR OWN COPY OF THIS FILE. THIS COPY SHOULD BE PLACED ON THEIR OWN LOCAL MACHINE.
If the FE databases are all in the same network directory, name them like this:
Note: To guard against unwanted changes to your code or to prevent design changes to your forms or reports, please see the Securing Your Code section below for details about distributing an .MDE instead of an .MDB.
This new architecture creates some new obstacles of its own. When you make a change in your FE development database, these new design changes are not immediately reflected in each users personal copy of the .MDB or .ACCDB. This is good and bad. Good because you can now fully test a new method under controlled circumstances before it is ever distributed out into a production environment. Bad because every time you wish to deploy a new version of your database, you will need to redistribute copies of the MDB/ACCDB file to each of your users.
There are many good reference articles in UA for redistributing a FE database automatically without going machine to machine. Here are a couple of the many links you will find if you do a search:
Deploying and updating Front End Database Applications, by Bill Mosca
Auto FE Updater, by Tony Toews
Relinking a FE
Another advantage that this structure gives you is the ability to easily create a separate testing environment by relinking your development FE to a different BE other than the production BE. Simply copy the BE to your local machine and relink the FE to this local copy. To relink your FE to the new local BE, perform the following steps:
You now have a fully functioning split database to test with. Any changes made to the data will be local changes only. Nothing in your production BE will be affected and your users can continue to work. When you have fully tested your modifications, relink your development FE to the production BE on your server and redeploy your modified FE to your users.
Note: If all the linked tables are from the same BE, you will be prompted once for the BE location. In an installation that has multiple BE’s (discussed later), you will be prompted for a location for each table (not each BE). To avoid this, you can re-link the tables in groups based on the BE.
Q: "If my users make a change in their copy of the .MDB, won’t they be overwritten when I redistribute a newer version?"
A: Yes. Any changes/customizations made to a user’s local copy of the MDB will be lost. In this scenario, good communication with all of your users is critical.
There are options. One solution would be to provide them with an additional brand new, empty .MDB that has been linked to your BE. This will allow them to create any adhoc object they want. To prevent unwanted changes to your data, a good practice is to provide Read-Only links to the BE.
Q: "Can I use a shortcut on my user’s desktop to open the FE?"
A: Yes. But the shortcut should point to the LOCAL COPY of the FE.
Q: "But I’ve been using a single shared copy for a while now, without any problems. Why should I change?"
A: Consider yourself fortunate. If you haven’t experienced problems yet, as the usage and number of users of your database increase, so will the likelihood of corruption. Keep in mind one of the purposes of this FAQ is to provide best practice guidelines for maintaining your database. Have you ever tried making design changes to a database that is being actively used? It is awkward at best. Who is logged in the database? Are they using the object I want to modify? Will my changes affect the workings of my database? All of these questions and more can be averted by implementing the changes suggested here.
Q: "My users access my database through a Terminal Server or other Thin-Client technologies. (i.e. Citrix) So they do not have a local machine to load the .MDB on. Can I give them a shared copy of the .MDB to use?"
A: No. In a Terminal Server environment every user will still need to have their private network share or their own desktop where their own private copy of the .MDB file should reside. They will access this file as usual.
Q: "What do I do differently if User Level Security (ULS) has already been applied to my .MDB?"
A: In order to preserve the ULS security in the BE, a slightly different approach to splitting the database should be used. Make a duplicate copy of the original .MDB. Open the new copy. Delete all objects except the tables and relationships. Compact & Repair the database. Rename the new file to distinguish it as the BE. Move the BE to its permanent network location. Link the FE to this new backend as detailed above.
The Workgroup file (.MDW) should be located in the same folder as the BE. Your .MDW file should be named differently than your BE. (Ex: BE name = AccountingBE.MDB, WorkGroup name = AccountingWG.MDW) Your shortcut to open your database will look similar to:
"Full Path to MSAccess.EXE" "Full Path to local copy of the FE" /wrkgrp "Full Path to Workgroup file"
Note: In Access 2007, the User Level Security feature has been removed from databases stored in the new .ACCDB format. It is still available in databases that are saved in .MDB (ACC2003) format
Securing Your Code
Many times it is desirable to secure the objects you have created to prevent unwanted changes or disclosure of methods within your code. Creating an .MDE from your .MDB (or ACCDE from ACCDB) solves this.
Follow these steps to create an .MDE,ACCDE
An .MDE/ACCDE file is the exact same thing as an .MDB/ACCDB, only all the internal code has been compiled. It will run faster and will not allow Design changes to any forms, reports, or modules. This is a very good thing, as it prevents unwanted changes from occurring in your database that you have worked so very hard on. This new .MDE/ACCDE file is what gets distributed to your users instead of the .MDB/ACCDB in the above example.
Since .MDE’s get compiled, there is no longer any means to gracefully tell the user that something bad has happened. Therefore when using an .MDE, error trapping is a must.
Note: Security in an Access database is kind of misleading. Protecting your code can be accomplished as described above. Protecting you data is something else entirely. There are steps you can take to make it more difficult to get at the data, but a determined person, given enough time, can overcome any hurdles you place in their way. Some common steps to take are: Password-protect the BE. *See Note. Password-protect the FE. Hide the database window. Remove all the Toolbars and provide your own custom ones. Disable the Shift-Bypass key. Disable context menus. Do not allow access to any object except a form or report.
There are many good reference articles in UA for securing a database. (Use the UA Search function to find them)
Note: To apply a database password you must sign in to the database in Exclusive mode. This can be accomplished by doing the following:
When applying a password to the BE, this should be done before the links from the FE are created. If the links are already in place, manually delete the linked tables from your development FE. Then follow the steps outlined above in the Create the FE section to re-establish the links in your FE to your new password protected BE. You will be prompted for the password during the linking process once, but after that you should not receive any more prompts. The password will be permanently stored in the link. Keep this in mind if you ever have a need to change your database password. You will need to delete and recreate all the links in the FE.
If your data requires vigilant protection, you may need to consider a BE other than Access that can provide more security.
Access 2007 boasts of increased security potential, but as of this writing, no definitive answer is available.
Q: "I cannot create an .MDE file from my .MDB."
A: Access can only create an .MDE file when the database version is the same as the version of Access you are using. (Ex. Access 2003 cannot create an .MDE when the database format is 2000) Convert the .MDB into your version of Access and create the .MDE.
Another reason that .MDE creation may fail is because there are problems with your code. Since Access has to compile all the code, there cannot be any errors.
TIP: Make sure at the top of EVERY module these two lines of code appear:
Option Compare Database Option Explicit
Without going in to a lot of detail, these lines will make it much easier for you to find errors with your code. Take the time to set this as the default, by:
This will add these lines to any new form/report/module that you create. You will be a better programmer for it.
Splitting a database may at first seem to be a lot of work, but the advantages to doing so are very great. Besides avoiding possible data corruption issues, this method will provide maximum security, a single point of manageability, and make it much easier to scale up to a larger back end when you outgrow the Access database container.
Q: "How do I maintain my new architecture?"
A: Even the best designed database structure will need maintenance from time to time. Backing up your database is perhaps the most critical step. You will need to back up your BE (which houses all of your data), as well as your FE development database (which contains all of the queries/forms/reports/macros/code). Compacting of the BE should be done on a regular basis (Tools> Database Utilities> Compact and Repair Database…), more frequently if your database performs large amounts of action queries (Make Table/Insert/Append/Delete). Compacting should take place wherever the database resides, when no one is using the database. It should NOT be performed across the network, which could lead to corruption problems. If you do not have direct access to the server where your BE resides, you can copy the BE to your local machine, perform the Compact, and then move the BE back to its original network location.
Q: "How do I know when I have outgrown Access?"
A: Ask yourself these questions: How many concurrent users do I have? Microsoft states that the maximum concurrent connections to an Access database is 255 users. This is much higher than can be expected. Though there have been documented examples of over 100 concurrent users when using very advanced programming techniques, generally the number of users is much lower when performance degradation begins to be seen. It has been my experience that with a moderately complex database using the standard "bound form" design, a more realistic number is more like 15-20 users. There are many methods for improving performance and these should be closely examined before deciding to upgrade. Realize, though, that upgrading to a more powerful engine will not solve the problems of a poorly designed database. A non-normalized structure, poor architecture, and bad data access techniques will still bring a fast engine to a screeching halt.
How large is my database? An Access database is not limited by number of records. It is limited by file size. Starting with Access 2000, Access has a maximum file size of 2 GB. (Previous versions were 1 GB) Choosing Tools> Database Utilities> Compact & Repair Database… may reduce the size of your database dramatically. If this needs to be done on a regular basis in order to keep your database under 2 GB, then it may be time to start looking for more robust options. (Ex. SQL Server, MySQL, etc.) Another less-used, but available option is to create multiple Access Back-Ends for a single application. Compartmentalizing the tables into separate databases can be a good interim step if moving to a larger BE is not an immediate option.
How secure are my data needs? No Access database is 100% secure. Many roadblocks can be put into place to keep honest users honest, but if data protection is a concern, consider moving to an engine that supports higher levels of security.
Where do I go from here?
Maintaining a split database will require a little bit more effort. You have to be aware of all the entry points to your application and take them all into account when making changes. For example, making a structural change to the BE may "break" the local FE on your user’s machine. It may not work until they receive a new copy of the FE.
There are many examples of tools you can employ to help facilitate the process of a change. Things like:
UA provides a huge warehouse of code examples, and in some cases, fully functioning applications to help you through this. Take advantage of the Search utility built in to this site. You may find the exact solution you’re looking for.
|This page has been accessed 13,670 times. This page was last modified 17:20, 3 November 2012 by Strive4peace. Contributions by Jack Leach, strive4peace2010, TheDBguy and Walter Niesz and others Disclaimers|