Microsoft Access can't save design changes because another user has the file open... but I am the only user?

Question

Just a little background: I am using Access 2010 to create forms and VBA code in an Access 2003 format database. For some reason, Access 2007 format databases always corrupt on me when I make changes and save them with a particular group of objects, but that's for another discussion.

When writing VBA code in this Access 2003 database, any time my code breaks (via breakpoint or an unhandled error) and I make a correction, Access tells me that it can't save back to the database because another user has it open. However, I am the only user working on the database; this is a local copy of the database and it's sitting on my desktop.

The LDB file can't be deleted because Access is using it. When I first load the database, I see my machine name and "Admin" when opening the LDB in a text or hex editor. After a break, I see that plus a duplicate entry, but this time around "admin" has a lower-case "A."

Closing the database and reopening it fixes the problem but makes it needlessly cumbersome to debug my code. Anyone else encounter this issue and/or have a fix for it?

Answer 1

I thought I might answer here, since I stumbled upon this question while having a similar issue. Essentially, it boiled down to this: I could either edit forms, VBA, etc. or edit information in the local database (which I'm using as a cache) with currentDB. I also have a backend database, but the locking was clearly on the frontend database.

The solution ended up being weird, but stupidly simple. When the frontend starts up, I have it immediately create a connection to the backend using OpenRecordset (and similarly to you, that backend was still on my own computer for testing purposes). I tried temporarily disabling that code, and suddenly it wasn't an issue anymore. And it turns out, once I call currentDB, I can then call OpenRecordset to open the connection to the backend, and suddenly it isn't a problem anymore.

Tl;Dr: if you're calling OpenRecordset somewhere in your code to connect to a backend, be sure to call something like set db = currentDB beforehand, then everything works. (That is, probably until I publish this answer and Access then decides it doesn't want to anymore).

Why this fixed it is beyond me, someone with more knowledge can maybe answer that.

Answer 2

Same symptom of not being able to save form or code mods after application had started. I found a workaround today! In the startup of my first form of the app, I had issued a "DAO.DBEngine.SetOption dbMaxLocksPerFile, 20000". Commenting this statement removed the problem. I did no further testing, but FYI, the DBEngine call was before any reference or attempt to use CurrentDB(). Also the current default on my Access 16 install is 9,500.

Answer 3

The solution:

options > current database > click enable -track name auto correct info

Answer 4

I have discovered a way to cause the problem discussed above (and thereby to correct it). Turns out if you create a database object and set it to the current database, you get this problem. That is,

dim cdb as database

set cdb = currentdb

From this point on, you're cooked. Instead, figure a way around this by possibly using currentdb directly or not using it at all. This worked for me.

Answer 5

It might be helpful to know what your code is doing when this happens. Certainly that's not normal behavior. For instance, are you opening another database with New Access.Application? Are you using ADO or DAO to access records in the database with a connection string?

There are no external connections to the database at all.

It may not matter if there are external connections to the database if you are using a connection string to connect to the open database; not sure but that may be seen as an external connection... you may want to use CurrentDB for DAO, or CurrentProject.Connection as your ActiveConnection for any ADO queries.

I am assuming that this problem persists through reboots; but for the sake of argument, try closing out Access and going to the task manager to make sure you have no other instances of MSAccess.exe running. You might even try closing all Office products and/or making sure that Access is the only Office product running. I have seen some weird conflicts between Microsoft Communicator and Outlook; so it's not entirely out of the question for Access to have issues with another MS product.

You may also want to check the size of the database to make sure it's not exceeded 2GB. That causes the infamous "Invalid parameter" error; perhaps it might be causing this as well.

With no other details about how your program works, we may only be able to offer generic advice like this.

Answer 6

In your VBA Try checking that all your open Connections to the database are closed. Until the connection is open the LDB fill will be there.