Setting up an MS-Access DB for multi-user access

Question

We're thinking of "growing" a little MS-Access DB with a few tables, forms and queries for multiple users. (Using a different back-end is another, but more long-term option that is unfortunately currently not acceptable.)
Most users will be read-only, but there will be a few (currently one or two) users that have to be able to do changes (while the read-only users are also using the DB). We're not so much concerned about the security aspects, but more about some of the following issues:

• How can we make sure that the write-user can make changes to the table data while other users use the data? Do the read-users put locks on tables? Does the write-user have to put locks on the table? Does Access do this for us or do we have to explicitly code this?
• Are there any common problems with "MS Access transactions" that we should be aware of?
• Can we work on forms, queries etc. while they are being used? How can we "program" without being in the way of the users?
• Which settings in MS Access have an influence on how things are handled?
• Our background is mostly in Oracle, where is Access different in handling multiple users? Is there such thing as "isolation levels" in Access?

Any tips or pointers to helpful articles would be greatly appreciated.

Answer 1

I find the answers to this question to be problematic, confusing and incomplete, so I'll make an effort to do better.

Q1: How can we make sure that the write-user can make changes to the table data while other users use the data? Do the read-users put locks on tables? Does the write-user have to put locks on the table? Does Access do this for us or do we have to explicitly code this?

Nobody has really answered this in any complete fashion. The information on setting locks in the Access options has nothing to do with read vs. write locking. No Locks vs. All Records vs. Edited Record is how you set the default record locking for WRITES.

• No locks means you are using OPTIMISTIC locking, which means you allow multiple users to edit the record and then inform them after the fact if the record has changed since they launched their own edits. Optimistic locking is what you should start with as it requires no coding to implement it, and for small users populations it hardly ever causes a problem.

• All Records means that the whole table is locked any time an edit is launched.

• Edited Record means that fewer records are locked, but whether or not it's a single record or more than one record depends on whether your database is set up to use record-level locking (first added in Jet 4) or page-level locking. Frankly, I've never thought it worth the trouble to set up record-level locking, as optimistic locking takes care of most of the problems.

One might think that you want to use record-level pessimistic locking, but the fact is that in the vast majority of apps, two users are almost never editing the same record. Now, obviously, certain kinds of apps might be exceptions to that, but if I ran into such an app, I'd likely try to engineer it away by redesigning the schema so that it would be very uncommon for two users to edit the same record (usually by going to some form of transactional editing instead, where changes are made by adding records, rather than editing the existing data).

Now, for your actual question, there are a number of ways to accomplish restricting some users to read-only and granting others write privileges. Jet user-level security was intended for this purpose and works fine insofar as it's "security" for any meaningful definition of the term. In general, as long as you're using a Jet/ACE data store, the best security you're going to get is that provided by Jet ULS. It's crackable, yes, but your users would be committing a firable offense by breaking it, so it might be sufficient.

I would tend to not implement Jet ULS at all and instead just architect the data editing forms such that they checked the user's Windows logon and made the forms read-only or writable depending on which users are supposed to get which access. Whether or not you want to record group membership in a data table, or maintain Windows security groups for this purpose is up to you. You could also use a Jet workgroup file to deal with it, and provide a different system.mdw file for the write users. The read-only users would log on transparently as admin, and those logged on as admin would be granted only read-only access. The write users would log on as some other username (transparently, in the shortcut you provide them for launching the app, supplying no password), and that would be used to set up the forms as read or write.

If you use Jet ULS, it can become really hairy to get it right. It involves locking down all the tables as read-only (or maybe not even that) and then using RWOP queries to provide access to the data. I haven't done but one such app in my 14 years of professional Access development.

To summarize my answers to the parts of your question:

How can we make sure that the write-user can make changes to the table data while other users use the data?

I would recommend doing this in the application, setting forms to read/only or editable at runtime depending on the user logon. The easiest approach is to set your forms to be read-only and change to editable for the write users when they open the form.

Do the read-users put locks on tables?

Not in any meaningful sense. Jet/ACE does have read locks, but they are there only for the purpose of maintaining state for individual views, and for refreshing data for the user. They do not lock out write operations of any kind, though the overhead of tracking them theoretically slows things down. It's not enough to worry about.

Does the write-user have to put locks on the table?

Access in combination with Jet/ACE does this for you automatically, particularly if you choose optimistic locking as your default. The key point here is that Access apps are databound, so as soon as a form is loaded, the record has a read lock, and as soon as the record is edited, whether or not it is write-locked for other users is determined by whether you are using optimistic or pessimistic locking. Again, this is the kind of thing Access takes care of for you with its default behaviors in bound forms. You don't worry about any of it until the point at which you encounter problems.

Does Access do this for us or do we have to explicitly code this?

Basically, other than setting editability at runtime (according to who has write access), there is no coding necessary if you're using optimistic locking. With pessimistic locking, you don't have to code, but you will almost always need to, as you can't just leave the user stuck with the default behaviors and error messages.

Q2: Are there any common problems with "MS Access transactions" that we should be aware of?

Jet/ACE has support for commit/rollback transactions, but it's not clear to me if that's what you mean in this question. In general, I don't use transactions except for maintaining atomicity, e.g., when creating an invoice, or doing any update that involves multiple tables. It works about the way you'd expect it to but is not really necessary for the vast majority of operations in an Access application.

Perhaps one of the issues here (particularly in light of the first question) is that you may not quite grasp that Access is designed for creating apps with data bound to the forms. "Transactions" is a topic of great importance for unbound and stateless apps (e.g., browser-based), but for data bound apps, the editing and saving all happens transparently.

For certain kinds of operations this can be problematic, and occasionally it's appropriate to edit data in Access with unbound forms. But that's very seldom the case, in my experience. It's not that I don't use unbound forms -- I use lots of them for dialogs and the like -- it's just that my apps don't edit data tables with unbound forms. With almost no exceptions, all my apps edit data with bound forms.

Now, unbound forms are actually fairly easy to implement in Access (particularly if you name your editing controls the same as the underlying fields), but going with unbound data editing forms is really missing the point of using Access, which is that the binding is all done for you. And the main drawback of going unbound is that you lose all the record-level form events, such as OnInsert, BeforeUpdate and so forth.

Q3. Can we work on forms, queries etc. while they are being used? How can we "program" without being in the way of the users?

This is one of the questions that's been well-addressed. All multi-user or replicated Access apps should be split, and most single-user apps should be, too. It's good design and also makes the apps more stable, as only the data tables end up being opened by more than one user at a time.

Q4. Which settings in MS Access have an influence on how things are handled?

"Things?" What things?

Q5. Our background is mostly in Oracle, where is Access different in handling multiple users? Is there such thing as "isolation levels" in Access?

I don't know anything specifically about Oracle (none of my clients could afford it even if they wanted to), but asking for a comparison of Access and Oracle betrays a fundamental misunderstanding somewhere along the line.

Access is an application development tool.

Oracle is an industrial strength database server.

Apples and oranges.

Now, of course, Access ships with a default database engine, originally called Jet and now revised and renamed ACE, but there are many levels at which Access the development platform can be entirely decoupled from Jet/ACE, the default database engine.

In this case, you've chosen to use a Jet/ACE back end, which will likely be just fine for small user populations, i.e., under 25. Jet/ACE can also be fine up to 50 or 100, particularly when only a few of the simultaneous users have write permission. While the 255-user limit in Jet/ACE includes both read-only and write users, it's the number of write users that really controls how many simultaneous users you can support, and in your case, you've got an app with mostly read-only users, so it oughtn't be terribly difficult to engineer a good app that has no problems with the back end.

Basically, I think your Oracle background is likely leading you to misunderstand how to develop in Access, where the expected approach is to bind your forms to recordsources that are updated without any need to write code. Now, for efficiency's sake it's a good idea to bind your forms to subsets of records, rather than to whole tables, but even with an entire table in the recordsource behind a data editing form, Access is going to be fairly efficient in editing Jet/ACE tables (the old myth about pulling the whole table across the wire is still out there) as long your data tables are efficiently indexed.

Record locking is something you mostly shouldn't have any cause to worry about, and one of the reasons for that is because of bound editing, where the form knows what's going on in the back end at all times (well, at intervals about a second apart, the default refresh interval). That is, it's not like a web page where you retrieve a copy of the data and then post your edits back to the server in a transaction completely unconnected to the original data retrieval operation. In a bound environment like Access, the locking file on the back-end data file is always going to be keeping track of the fact that someone has the record open for editing. This prevents a user's edits from stomping on someone else's edits, because Access knows the state and informs the user. This all happens without any coding on the part of the developer and is one of the great advantages of the bound editing model (aside from not having to write code to post the edits).

For all those who are experienced database programmers familiar with other platforms who are coming to Access for the first time, I strongly suggest using Access like an end user. Try out all the point and click features. Run the form and report wizards and check out the results that they produce. I can't vouch for all of them as demonstrating good practices, but they definitely demonstrate the default assumptions behind the way Access is intended to be used.

If you find yourself writing a lot of code, then you're likely missing the point of Access.