Hi,

Anyone point me in the right direction on what to consider with multiple users reading / writing to the same table in a back end database? I am setting up a new structure and am not sure what problems I might have.

Any help or pointer to an existing topic appreciated.

Thanks,

S

Hi S,

What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

As long as you properly split your database, you shouldn't run into much trouble.

Just my 2 cents...

Hi S

This Link covers it very well

Thanks Mike60smart and theDBguy!

I'm using Access 2010 at the moment. I suppose what I need to get my head around is the interraction that multiple users have with the database. The users will have a form to perform various actions on via their front ends. The database is for maintaining content lists of documents so the records the users will work on is the list of what content a document should contain. This means that the actions the users perform will create / update a few hundred records at a time as the content lists are archived / updated as required. If the content lists are all in the same table and say three users update / create 100 records each at the same time - is this going to cause a problem? Basically I have no knowledge of what interacctions that multiple users can do on the same set of data without causing a problem.

Thanks for your help!

S

Hi S,

The common approach for any database engine is to perform the update for the first user who commits the change. For example, let's say you and I decide to update the same record at the same time. Whoever submits the change to the database first, that change will be committed. When the other user tries to submit their change, the database engine will give them an error. If that is likely in your scenario, one other approach that is available is to only allow one person to "edit" a record at a time. So, let's say you and I are trying to update the same record again, whoever asks the database engine for that record first, wins. The other will get a "busy" warning from the database engine. This is similar to when you're trying to update a Word document on a network location while another person is already editing it. That's why databases are meant for multiple users because their default behavior is the first approach I mentioned. It is not highly likely in most scenarios that multiple users would be updating the same record at the same time. But if they do, there are ways to overcome the conflicts.

Just my 2 cents...

Access is pretty good at letting users share a table concurrently. The only time you'll run into issues is if two users try to concurrently update the same record in the table.

If your users are just adding new records, or are each editing their own set of records, you should have no problems.

That's exactly what I want! Thanks so much. The users will only be creating / amending records of their own so no conflict. I was beginning to wonder if I'd have to set up specific versions of each table for different areas (there's no cross referencing of records between areas).

Are there settings I can / should apply so that there is a message presented for the "busy" warning from the database engine? Is there a way to determine if the back end database is currently busy? I think I saw a reference to methods that list active users.

Thanks again.

S

Hi S,


If you don't expect conflicts, then there's really no need to worry about presenting a warning that the database is busy. But if you insist on going that route, you can try adjusting the Record Locking setting for the database to use "Pessimistic Record Locking."

For most database applications, the default setting of "Optimistic Record Locking" is usually enough.

Just my 2 cents...

Thanks theDBguy,

Excellent. Really appreciate your help. Quite a different approach to what I have done so far.

Cheers,

S

Hi S,



Mike, John, and I are all happy to help. Good luck with your project.

sarah,

i definitely would not change the default locking mechanism used in access unless you absolutely need to.

the default method still checks before saving changes, and rarely gives problems. read up on "optimistic locking"

Active, or "pessimistic locking" imposes extra requirements on the programmer to manage and release the locks timeously (ie - in a controlled fashion) and should not be adopted without a lot of understanding, imo