How to enable 1 user to update the database while

I am planning to put my microsoft access application with forms and database onto a shared drive for around 10+ people to use. However, there is the issue of concurrency. Is there any way to set it such as to allow the microsoft access database to be only updated by 1 person at any one time while allowing others to read it but not update it? For now, the access database can only be updated and read by a single person while the others cannot even open the file. Thanks a lot for any help rendered!

回答1:

What i want to do is allowing only 1 person to be able to update the database while others will read-only.

Yes, it can be done, and you don't need to change your application code to do it. It can be accomplished by adjusting the permissions on the shared folder and the shared file.

Permissions on the Folder

Whenever one or more users need read/write access to an ACE/Jet database file (.accdb or .mdb), all users must have Create files / write data permission on the folder in which the file resides. This is because their copy of the Access Database Engine needs to be able to create the lock file (.laccdb or .ldb) if they are the first person into the database.

Note that even users who will just have read-only access to the file still need "Create files" permission on the folder. This is essential.

Permissions on the File (.accdb or .mdb)

This is where you differentiate between users who can update the database, and users with just read-only access. Simply use the Windows permissions on the file to give them read+write or read-only permissions as desired.

Common mistake #1: Set user/group permissions (read-write or read-only) on the folder and let the database file (.accdb or .mdb) inherit them

In this situation, "read+write" users can create the lock file but "read-only" users cannot.

• Scenario A: If a "read+write" user opens the database first then the lock file gets created and everything operates as expected. Additional "read+write" users can open the database and make updates, and additional "read-only" users open the database read-only.

• Scenario B: If a "read-only" user opens the database file first, then their copy of Access opens the file as read-only, as expected. However, if a "read+write" user (according to their file permissions) then tries to open the database file they will open it as read-only too. Everyone can get in, but nobody can make changes.

Common mistake #2: Prohibit "create files" on the folder for everyone

In this case nobody can create the lock file so everybody opens the database read-only, regardless of their permissions on the database file itself.

Additional notes:

The results above were confirmed with a mix of Access_2010 and Access_2013 clients connecting to a shared .accdb back-end on Windows Server 2012. In earlier versions of Access, if nobody could create the lock file then the first person "in" would open the database file in "Exclusive mode" and get whatever access their file permissions allowed, while all subsequent users would be denied access with an error message stating that "The file has been opened exclusively by another user."

回答2:

No. You could make something programmatically, but it was sure to be rather fragile, locking everybody out every odd day. Access is a good front-end, but not a good back-end for multiuser. Use another database as back-end. In principle SQL Server Express is the easiest option. Of course, you have to split the database into front-end and back-end, it's not clear if you have already done so. If you haven't, you could try how it works with both parts in Access, but my experiences with multiuser networked Access back-ends are not good.