geeta_m9
asked on
Can't update records when database is opened by more than one person
I have a database that is on the shared drive that is sometimes opened by more than one person. From time to time, I need to make updates to some of the records in the database, but am unable to do it unless the other person or person(s) exit out of it. Is there a way for me to be able to make the updates even if it is being used by someone else?
I've seen this done, but I don't recommend it:
Essentially oyu have one access DB that holds the data (like a SQL database normally would).
From there you seperate out the UI into a 2nd access file, and link all your tables to the first access DB.
When you open up the the access-UI file, only that gets locked and multiple users can update records.
Essentially oyu have one access DB that holds the data (like a SQL database normally would).
From there you seperate out the UI into a 2nd access file, and link all your tables to the first access DB.
When you open up the the access-UI file, only that gets locked and multiple users can update records.
ASKER
How can I tell whether a user has modify permissions on the folder?
ASKER
I checked and the default mode for the database is already in shared mode.
I've seen this done, but I don't recommend it:Why? This is the preferred method for proper multiuser user of a database. Users should never share the same FE (i.e. the UI portion).
You'd have to review the Permissions for that folder, and determine if the user has implicit or explicit permissions. Explicit permissions would be permissions assigned directly to the User. Implicit permissions would be permissions assigned to the user through their association with a group, for example.
How can I tell whether a user has modify permissions on the folder?
And again, you will need Admin permissions to determine this. If you are a Standard user, you won't be able to do this - you'd have to get someone with an Admin login to handle this for you.
ASKER
I also checked the folder permissions and it appears that all the staff have "Full Control". I presume that would also include Modify permissions?
ASKER
The permissions for the staff appear to be implicit, i.e., group permission.
Is the file on a share? If so there's a seperate set of share permissions that you also need to check.
(EG: You could have full control for the directory but read only in the share).
(EG: You could have full control for the directory but read only in the share).
ASKER
How do I check that?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No, they are just updating records.
ASKER
The thing that got me is that if the rest of the staff didn't have write permissions, I am wondering that they should not even be able to open the file, since Access needs to open or write to the .accdb locking file when a user opens an Access database.
LMSConsulting, you could be right about the Exclusive lock being invoked. I found out that whenever one of the staff was updating a record, he would make the edit and then try to close the form. When he did that, I think Access must have assumed that he was trying to go into "Design Mode". Instead, I told him that after making the update, to just skip to the next record before trying to close the form. That seemed to do the trick.
LMSConsulting, you could be right about the Exclusive lock being invoked. I found out that whenever one of the staff was updating a record, he would make the edit and then try to close the form. When he did that, I think Access must have assumed that he was trying to go into "Design Mode". Instead, I told him that after making the update, to just skip to the next record before trying to close the form. That seemed to do the trick.
ASKER
Thanks.
I am wondering that they should not even be able to open the file, since Access needs to open or write to the .accdb locking file when a user opens an Access database.If the user does not have sufficient permissions to write to the folder hosting thee database file, then Access would write the lockfile somewhere else (like on the user's workstation, perhaps).
You must also ensure that all users have at least Modify permissions on the folder hosting that database. If not, the first user in could lock the database. Note these are Windows permissions - you'll have to have Admin permissions to on the machine to handle those.