18.3. Playing Well with Others
Multiuser access is a perpetual juggling act. If all people want to do is read information, life is easy. But some significant challenges appear the moment people want to make changes. For example, what happens when two people try to change the same record at the same time? Or when you try to change a record while someone else tries to delete it? Or, if you want to read the latest information while an update's in progress?
Clearly, Access needs a way to manage the chaos. In this section, you'll learn what Access does to keep everything under control, and how you can adjust its settings. You'll also learn how to stave off the dangers of data corruption.
18.3.1. Seeing Changes As They Happen
Picture the following scenario. You're on the phone with a big-spending customer of Boutique Fudge. Using your trusty Access database, you run through the products that are available, giving your customer the price of each one. But unbeknownst to you, the head chef is looking at the same table at the same timeand raising the prices on the most popular dishes. The question is this: When do you notice the price increase?
Access deals with situations like this using automatic refreshes . Once every 60 seconds, Access checks the back-end database to find out what's changed. Access then updates the corresponding information on your screen, whether you're looking at a form, a query, or directly at a table. In the Boutique Fudge example, the new prices appear the next time Access performs a refreshand no later than 60 seconds after the changes are made.
There are a few exceptions to the refresh rule:
If you don't like the 60-second rule, you can fine-tune how often Access performs its automatic refreshes. To do so, choose Office button Access Options. In the Access Options window, choose the Advanced category of settings, and then scroll down the list until you see the "Refresh interval box (Figure 18-9).
Note: The refresh interval is an Access setting that affects all the shared databases you open on that computer. If you want everyone to use the same refresh interval, you need to tell them all to update their Access settings.
The shorter the refresh interval, the faster you'll see other people's changes. However, shorter refresh intervals also create more network traffic. Most Access fans find they can safely lower the refresh interval without a problem, unless they're using a slow network.
18.3.2. Dealing with Editing Conflicts
Shared databases are a bit of a free for all. Ordinarily, Access doesn't impose any limits on multiuser changes. If you're lucky, people will make their changes in an orderly fashion, one after the other. But sooner or later, changes will overlap, with potentially frustrating consequences.
Here's an example that shows what happens when two changes overlap:
You have three ways to resolve a conflict:
The best of the three choices is usually to copy changes to the clipboard and try to repeat the edit. Unfortunately, you can't force people to do the right thing. Lazy workers may choose the quicker Save Record option, which quietly wipes out someone else's work. Worst of all, the person who made the original change has no way to know it's been thrown away. If you have a high number of overlapping edits in your organization, you'll need to spend a good bit of time teaching everyone the right way to handle it.
Note: Access fans often wish they had a way to merge changesthat is, to update only the fields you changed. In the previous example, this option would let you apply a new description without disturbing the previous user 's price change, because both updates affect different fields. However, Access doesn't provide this option. One reason is that there's no way to know if the two sets of changes will be consistent . And there's nothing worse than having a record that contradicts itself.
18.3.3. Using Locks to Stop Overlapping Edits
If overlapping changes are causing too many headaches , you have an option. You can use a software trick called a lock to prevent overlapping edits.
Essentially, a lock uses the same concept that protects two people from ending up in the same bathroom stall. When one person enters, he or she switches on the lock, and everyone else has to wait until the deed is done. Similarly, when a person attempts to change a record, Access starts by grabbing a lock on that record. Anyone else who wants to make a change is forced to wait until the first operation is finished.
The easiest way to use locks is to switch them on through the Access settings. To do so, choose Office button Access Options. Then, choose the Advanced category, and look for the "Default record locking setting. You have three choices:
The last option is the most common locking choice. When you use individual record locking, Access won't let you begin editing a record if someone else is currently modifying it. When you try, Access displays an icon that indicates the record is locked, as shown in Figure 18-12.
Locks prevent your database from becoming a mess of scrambled information, but they impose other headaches. It takes extra work for Access to keep track of every-one's lockit has to play the role of an overworked washroom attendant who doles out the washroom keys. Access keeps track of locks by creating a .laccdb file. For example, the first time someone opens the shared database BoutiqueFudge_be. accdb, Access creates a file named BoutiqueFudge_be.laccdb . (The l stands for locking.) When the last person closes the database, Access removes the locking file.
Tip: If you look in the shared folder and don't see a .laccdb file, you know that no one is currently using the database, or someone's opened it in Exclusive mode (Section 18.3.4).
Locks also slow other people down, forcing them to wait for the information they want. A careless user can tie up a record indefinitely, leaving it in edit mode.
Note: If you head out for a lunch break, you may end up tying up the entire company without even knowing it. Even worse, although other would-be editors will see that the record is locked, they have no way to know who the culprit is. Their only recourse is to wait and wait.
If you decide to use locking, it's a much better idea to apply it through individual forms rather than switch it on for every database using the Access options. You could use the standard "No locks" setting for your entire database, but configure all the forms that use a particularly important tablesay, Invoicesto use locking. To change the way a form works with locking, open the Property Sheet and look for the Record Locks property. It supports the same three settings: No Locks (the default), All Records, and Edited Record.
Note: This trick leaves an open back door. If someone decides to make a change by directly opening the table, they'll bypass the locking that you've implemented in your forms. As always, it's easy to guide people to the right path but harder to force them to stay on it.
18.3.4. Opening a Database in Exclusive Mode
One of the limitations of shared databases is that you can't change the design of your tables while other people are using the database. Before you can make more radical alterations, you need to open the database in Exclusive mode .
Exclusive mode temporarily restricts a shared database to a single person. While you have the database open in Exclusive mode, no one else will be able to access it, no matter what front-end database they use. You have a few precious moments to make the more radical changes that you wouldn't normally be able to do.
Here's how to open a database in Exclusive mode:
Note: You can configure Access so that it always tries to open every database in Exclusive mode. However, you rarely have a good reason to use this setting, because it defeats the purpose of database sharing.