Section 18.3. Playing Well with Others | Access 2007[c] The Missing Manual

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:

When you start editing a record (by clicking inside one of the fields), Access immediately refreshes just that record . This ensures that you always start with the most up-to-date copy of a record before you begin making changes.
If you can't wait 60 seconds, and you're getting nervous that something's changed since the last refresh, you can trigger an immediate refresh using the Home Records Refresh All command . And, if you click the downward-pointing arrow part of this button, you can choose to refresh only the current record where your cursor is positioned (choose Refresh instead of Refresh All).
Reports don't use automatic refreshes . If you run a report, wait, and then decide you want to update your results, you have two choices. You can close the report and reopen it, or you can use the Refresh button.

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.

Figure 18-9. The refresh interval controls how often Access checks a shared database for changes. You can choose a value (in seconds) from 1 to 32,766.

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 open a query that shows all the products in the Boutique Fudge database.
You find a recorda top-selling cheesecake known as The Chocolate Abyssthat needs changing. You click inside the Description field to start your edit.
At the same time, Bill Evans in the sales department fires up a form that also uses the Products table. He browses to the same record, andrealizing the potential for better profitsstarts to change the price. Now two people are currently working with the same record. What happens next depends on who commits their change first.
Assume Bill gets the job done first. Quick as a flash he raises the price, and then heads on to another record.
Back on your computer, you've finished touching up the Description field. You move to another record. Ordinarily, this is the point where Access commits your edit, saving it to the back end database. But in this case, Access notices a conflictnamely, the version of the record you're working with isn't the same as the version that's currently in the database.
Access warns you about the problem, and gives you three options (see Figure 18-10).

Figure 18-10. Between the time you started the edit and the time you tried to apply it, someone else made changes. Access lets you choose how you handle the conflict.

You have three ways to resolve a conflict:

Save Record is the easiest and most reckless option. If you choose it, Access overwrites the record in the database with your version. The problem is that this option obliterates the changes that the other person made. In the previous example, the new description will be saved in the database, but the price change will be lost because Access reapplies your old, out-of-date price.
Drop Changes cancels your edit. Access will refresh the record to show the most recent information, and then you can try making your change all over again. This option is reasonable if you can repeat the edit easilyit's not as good an option if you've finished a detailed revision of a large text field.
Copy to Clipboard cancels your edit, just like Drop Changes. However, the values you changed are copied to the clipboard, which makes it easier to reapply them, as shown in Figure 18-11.

Figure 18-11. If you copied your last edit to the clipboard, you need two steps to put it back into place. First, paste it into another program (like Word, shown here). Then, select just the data you want to use, and copy it back to the clipboard by pressing Ctrl+C. Finally, switch back to Access, head over to the field you want to change, and then paste the new value by pressing Ctrl+V.

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.

POWER USERS' CLINIC
Splitting Tables for Safer Edits

One way to reduce the number of overlapping edits is to split tables into smaller pieces. The basic idea is to take a single table that has lots of fields, and divide it into two smaller tables, each containing only some of the fields. For example, you can take a Customers table and divide it into a CustomerAddress table and a CustomerFinancial table. Every record in CustomerAddress is linked to a single record in CustomerFinancial using a one-to-one relationship (Section 5.3.1). You'll need both records to get all the customer information.

The best time to split a table is when you know that a typical edit will involve the fields in just one table. Maybe you know that customer service often needs to update address information, while the billing department works with the financial information, splitting the table is a great idea. The customer service department will use the CustomerAddress table almost exclusively, and the billing department will use the CustomerFinancial table. The chance of overlapping edits is greatly reduced, because the work is split between two tables.

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:

No locks is the standard setting in Access. When you use this option, Access won't use locking at all, and overlapping edits are possible.
All records tells Access to lock the entire table whenever someone begins editing a record. This setting is extremely rare. Because it locks every record, it prevents anyone else from working with the table when just one edit is taking place. This limitation can bring any organization to a grinding halt.
Edited record locks individual records as they're being edited. This prevents overlapping edits.

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.

Figure 18-12. The don't-go-there symbol warns you to wait rather than edit a record that's already in use. If you still try to type in the field, Access stubbornly ignores you.

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:

Tell everyone else to close the database .

You can't open a database in Exclusive mode if it's currently in use. In a big company, this is the hardest part. System administrators sometimes resort to mass emailing to let everyone know that it's time to shut down. Another choice is to teach the people who use your database to close it every night before they leave, which lets you slip in a late-night update without disruption.
Choose Office button Open .

The Open window appears.
Select the database file you want to open, and then click the drop-down arrow on the Open button .

A list of specialized options appears for opening your file, as shown in Figure 18-13.
Choose Open Exclusive .

Access opens the database. You can now make changes with no restrictions. But work fastthe longer you keep the database open in Exclusive mode, the longer other people will need to wait to get on with their work.

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.

Figure 18-13. If you open a file in Exclusive mode, no one else can open it until you close it. If you open a file in read-only mode, you can't make any changes.