Question About Opening Access Database Exclusively

[jackb1117]

Hello All,

I am relatively new to developing with Access, and brand new to this forum. I have a question about locking down an access database.

We have a database that lives on a shared network drive, and is shared by ~12 users. Becsuse of the nature of the function of the database and the rapidly changing data that it contains, we want to make sure that no users are able to open the database while another user is in the database.

Currently, users are using a shortcut calling the database to open it exclusively ("C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "Z:\Database\Our Database.mdb" /excl), which serves its function of keeping other users out if someone is currently in the database. However, as the team using this database grows, we are having issues because it doesn't let us know which user is in the database, and sometimes users forget to log out when they leave for lunch/the day/ the weekend.

Additionally, an .ldb file is not generated when you open using this method, so I can't use some of the utilities that have been built leveraging the .ldb to kick out user information.

Is there a way I can somehow display which user is currently in the DB? We are currently using Access 2003 SP3. Thanks for reading, and I look forward to your responses!

 

[ghudson]

This is an odd setup you are using, only one user at a time. The database should be split and the users should have their own front end on their computer and all front ends tables should be linked to the one back end where all the shared tables are located.

 

[jackb1117]

Thanks for the reply, ghudson. I agree- an odd setup for sure.


There unfortunately is a business need for this. Cliff notes from a process perspective: End users have some data they run against a couple of tables in the DB. Based on the results, they make some decisions, and then either add / delete / modify some records in the tables they were initially querying against. We don't want anyone to be running their queries or adding records to the dataset while another user is analyzing results, as they will not be hitting a current dataset. Unfortunately it is a bit of a waterfall effect in that one users actions will affect subsequent users, so we can't have concurrent users. Undoubtedly not a great process, but thats what I am faced with and trying to engineer around.


I am certainly open to other approaches (I had looked at splitting the front-end from the back-end, but looked like it would not meet our unique needs- but this would not be the first time I was wrong ). Thanks again!

 

[MentatDragon]

Jack,

I have two ways to accomplish that. The easiest is to have a table with two fields, InUse and CurrentUser. When a user opens the database and the first form opens, you have an on open event that verifies that InUse is set to false, otherwise it triggers a message box and a close. I store the current user info in CurrentUser and use it for the message box. On exit, I set the InUse flag to false.

The other option is a bit more complex. Let me know if you still have an interest, as this is an old thread.