eBlue: Sacra Blue Online

Issue 203 - June 1999

Access By
The Page

Norm Chezem

Contact Norm Chezem at 916-784-7038 during mentor hours (see the Mentor List) or by e-mail at nchezem@cnets.net.

Shared Database Checklist

It is always a little chilling when visiting a client's office to be greeted with a resounding, "Oh, Norm, we are really glad to see you!" What that means to me is that somebody or something is in serious need of assistance right now. This happens more often when the client has a more complex installation, perhaps including a shared database. Several PCs probably share not only the database but also the problems.
To get to the bottom of the difficulties, I ask a few questions. Have you changed the hardware configuration recently? Have you added any new applications to your computers recently? Have you changed the way you are doing things lately? What kind of problems are you having?
Usually, I will get an answer that goes like, "Well, we did upgrade the hard drive on one PC, but we reloaded Access and re-attached the tables and everything went fine. It is just that whenever John adds a new record, it doesn't show up on any other person's PC." In this case, it is time for a detailed review of the installation specs for your shared database!
Just to review the concept, a shared database really means that you are sharing the tables where the application's data is stored. In Access, to share the tables, you must split the application into two pieces. In one database, you put the tables and nothing else. In the other database, you put everything else meaning the reports, forms, macros, modules and queries. These two database are normally called the back-end or server (for the tables) and the front-end or application (for everything else).
Some other things also need to be done to share the data on a peer-to-peer network.

Locate the tables database (back-end) in a separate folder on that PC's C drive. Set the shared properties of that folder to Full, meaning read/write. This means that any other PC on the network can have full access to the tables so everyone can update the tables or fetch data from them.
Install the application (front-end) database on each of the other PCs.

    Since any PC can now read to or write from the shared database on the back-end machine, some options need to be set to prevent interference between these front-end PC's. On each front-end, go to Tools | Options | Advanced. Set the options for Default Record Locking to: Edited Record. Set the option for Default Open Mode to: Shared. The other option selections for Default Record Locking and Open Mode are not appropriate for a shared data installation and will cause problems if set.
    The tables in the back-end database must be attached to the front-end database. This can be done either manually or through code. Do not allow copies of the back-end database to be installed on the front-end PC's. Do not allow anyone to open the back-end database directly.
    The purpose of the list of rules above is to prevent anyone from locking out everyone else from the tables and to prevent anyone from over-writing data that someone else just entered and "saved."
    To illustrate how over-writing can occur, picture this: John has his Open Mode set to Shared but he opens the back-end database directly, over the network and proceeds to enter data. Mary also has her Open Mode set to shared but she also opens the database directly and updates the same records. Mary goes for coffee. John closes his database. Mary comes back and closes her database. John's data is gone because Mary's database was closed last. John gets upset but it was as much his fault as Mary's.
One of the contributors to the problem is Microsoft because the default option for Open Mode when Access is installed is Exclusive rather than Shared. They can't take all the blame for problems though since all the options need to be set whenever a multiple user application is installed.
    The most frequent problems that I run into are options not being set properly when Access is re-installed or when a new PC is added to an application. Next are people who change options or open the program on the server PC.
    During the early 1970s, the word "entropy" was used to describe what happened when a computer system was left alone without attention for too long a period. Things seemed to spontaneously degenerate into chaos if left alone. They still do.
It is a good idea to review your multi-user specifications from time to time to keep things running smoothly.

Issue 203 - June 1999
	Copyright © 1999 Sacramento PC Users Group, Inc. All rights reserved. Read our disclaimer and copyright page for more information.