Trying to release a database lock (1 Viewer)

Hi,
I just upgraded an application from 2003 to 2010, and put it on a 2008 server; and it is working great. However, the users are going to go in there and start testing it in a week or so. They told me that when they put it on the server before and multiple users tried using it, they would have a lock issue. It seemed that after a user would close the application the laccdb locks would remain for the user. Is there VBA code I can add to my close command to clear this as well. I know in excel there is, but I have not been able to find it for Access.

Thank you

Have you split the db, with the backend on the server and each user having a copy of the frontend on their own computer

Thanks for your fast reply. No, I did not think of that That you feel would prevent the locking?

itmasterw said:

Thanks for your fast reply. No, I did not think of that That you feel would prevent the locking?

Click to expand...

I think it might. I am sure you will have issues and corruption down the road if it is not set up as it should be.

What is a database lock in the context of SQL? Provide an example and explanation.

A database lock is used to “lock” some data in a database so that only one database user/session may update that particular data. So, database locks exist to prevent two or more database users from updating the same exact piece of data at the same exact time. When data is locked, then that means that another database session can NOT update that data until the lock is released (which unlocks the data and allows other database users to update that data. Locks are usually released by either a ROLLBACK or COMMIT SQL statement.

You also need to verify that all users have Modify permissions for the folder on the server where the back end will be located. If they don't have the proper permissions then the lock file can't be deleted when the close out of the front end.

okay thanks

To Beetle, Is that with splitting the database as well or without it?

You need to do both, split the database and set correct permissions for each user.

When you split the database, a key part of that is to give each user their own PRIVATE copy of the front end. Then, what REALLY happens is that the locks still occur in the front-end file - but since it is a private copy, there is no one with whom to HAVE a lock conflict on the FE. That leaves the back end data. In every form, you have the option to control locking style. If you set it to DYNASET recordset and Optimistic Locking, then you still won't have zero locks, but that combination makes the lock duration very short. That is, there is a timing window where a conflict could occur, but optimistic locking makes that window very small. Small enough that you hardly ever see it unless you run a contrived experiment to try to hang the database with a lock.

Now, if this is a reasonable domain environment, the EASIEST way (and probably the most technically preferred way for security professionals) to set up permissions is to create a user group on that 2008 server and give the GROUP the Windows MODIFY permission settings to encompass the folder and all the files therein. I.e. use permission inheritance. Make the BE file become owned by the group. Put the FE file in that folder so folks can copy it. Then make each user of the database a member of the group.

NOTE: You will have those hard-headed or dim-witted users who want to directly open the FE copy from the shared folder on the 2008 server. THAT is when the locks become an issue. Which is why I had code in my database to run a startup form (default opening form, more technically) that tested for whether the FE folder and the BE folder were the same location.

The way I did it was to take CurrentDB.Name (which ALWAYS points to the currently open FE file) and pull out the file name and type (xxx.MDB). Then I tried to open the back-end file using device and path data left behind from the previous string parsing. If I could, either they had a private copy of the BE file OR they were opening the FE from the shared folder. In either case, I popped up a message box and told them to go away.