Don't have exclusive access to database and so cannot save changes

Question

I'm working on a MS Access database. I've made some changes to one of the modules. I want to go out for lunch, but when I try closing the database, I get the following message:

"You do not have exclusive access to the database. Your design changes cannot be saved at this time. Do you want to close without saving your changes?"

I'm pretty sure nobody else on the network has the database file open, and I don't have any other Access databases open. I'm probably missing something obvious, but would really appreciated some help!

Update:

In the end I copied all the code, closed the database without saving, re-opened it and pasted the code back in. I was then able to save the database. I'm not sure if this was a one off, but I'll report back if it happens again.

Answer 1

If you're sure no one else is in the db but you, it's an additional connection to your db from your own pc. You can verify this with the LDB viewer, downloadable in the free JetUtils.exe download from Microsoft:

http://support.microsoft.com/kb/176670

Look through your code and check if you have two separate database objects in the default workspace or another database object in a separate workspace. That will cause this problem.

To fix it, make sure the database objects are set to nothing before they go out of scope, and if you opened the database object in code, you also need to close it before setting the database object to nothing.

=============================================

Update in August 2022:

The MS link above no longer works. The document remains available on Archive.org, but is outdated. A document that appears to provide the current version of its information is at:

https://learn.microsoft.com/en-us/office/troubleshoot/access/determine-who-is-logged-on-to-database

This provides VBA code for a sub to obtain a list of users. The writer of this update has tested that code successfully in Access 2019.

Answer 2

Very simple. Close all of your MSaccess files. Open task manager (by right click on task bar). Select Processes tab in that. If the list has a msaccess*32 process close that by clicking on End Process.

This worked for me. I think it closes all the recordset which we have not closed in the codes or which is closed forcefully.

Answer 3

If even a word mail merge is linked to the access database, that counts as an access connection.

Answer 4

If you close the database and are sure nobody else has it opened, check to see if there is a .ldb file (it will have the same name as your database file). If the file is there, then there is a good chance it is still in use. Is it being access by a service, like a website?

You could copy the database to another sub-directory and make your changes. If that doesn't work, I will have to look that up. Of course there is always the database tool, "repair and compress database..."

Is the file located on a file server? If so check to see if any users have a file handle to it.

If it still doesn't work, update your post with your new information and we'll go further.

UPDATE (9/26): Another thing I do when having strange issues with access databases with contain vba code is decompile. I don't know if this is documented yet, I haven't looked in years, but it's was (at least) an undocumented switch to msaccess.

From a cmd line:

change directory to where msaccess.exe is located.
Run the following command

msaccess \path to access file\databasefile.mdb /decompile

usually runs very quick then opens the database. Open any module and compile.
Doesn't always work, but sometimes can remove strange happenings.

Did you ever trying to copy the database to another directory and making your edits? That should of worked; you could then rename the original and copy the file back.
Anyway, I am glad you are working again.