Concurrency Control Question

[methodmano]

I've developed a DB (forms, reports, etc) using MSAccess2003 for a company where the users only have access to shared applications and a shared network drive (very locked down environment). I'll be placing the DB on the shared network drive (only possibility). How do I go about handling concurrency control in this situation where each user will be accessing the same file? As I understand it, normally the user would have their own "frontend" file on their computer that would communicate across the network to the "backend" portion. In this case though, there is no "frontend", only a "backend". I'm unfamiliar with MSAccess in this regard and would appreciate some help/advice/info. Thanks a lot!!

 

[Trevil]

Hello,
You *can* have your users accessing the same database on your server, but the list of disclaimers is too long. Access *can* handle concurrency issues -- depending on complexity of your application, number of concurrent users, transaction volume, etc.

In the short term, you can try it and see what happens. If performance becomes an issue you may be forced to make a front-end for the user workstations.

I suggest you create an MDE for the users to access.
Wayne


35+ years of 'progress' -- can't we all just go wire boards again?

 

[methodmano]

Thanks for the reply. Good to know that it is possible. Again, I'm not familiar with this aspect of Access and am still a bit unsure.

Do I need to do something before I let the users access the DB or will it automatically handle multiple users?
How does Access handle two users accessing the same record at the same time? Will it let either one update the record?
The MDE is just a level or security right? (wrapping the project into a single file)

I'm not too worried about performance issues or problems as the absolute maximum number of people using it will be 6 (most likely only 2 at any given time). Thanks in advance with any insight!

John

 

[psemianonymous]

The basics of multi-user stuff is:

-Access is multi-user by default, nothing additional required.

-Concurrency can be handled many ways; by default it's optimistic. Look up the help files on record locking to see the details of what Access does in terms of this; the quick explanation is "it lets two people edit the same record, then the last person to 'commit' the save is provided some sort of warning/option to cancel before saving." Try it out--you can open the same database in two Access windows on your machine.


Don't expect problems on the record-locking end. Worry more about data corruption/backups and how you're going to do updates on the live database (hint: you can't).

 

[methodmano]

Awesome - thanks for the details. One last thing though (I think!):

"Worry more about data corruption/backups and how you're going to do updates on the live database (hint: you can't)."

What do you mean by this? Can you give me an example or a quick explanation? Again, thank you very much for your help.

 

[psemianonymous]

Imagine all the forms, reports, and queries are stored in the database on one row. If you want to edit any of these, then you have to update them all...at once. If you actually do manage to commit a change while someone else is in the db, they'll get a "passive shutdown" error message telling them to log out and log back in (and then they actually have to).

What you have to do is split the database into a frontend and a backend...that is, you have to unless you can update the database while no one's on, or doing something else to work around this problem.

 

[Trevil]

When needing to make structure / object changes to a shared database, one solution that I use for small-scale db's is to just come in early, open the database with exclusive permission and make my changes. Oh, and close the database afterwards.

Code: Where the vision is often rudely introduced to reality!