SOLVED

Strange problem compacting Access databases

Occasional Contributor

My installation of MS Access (Office 365 subscription) suddenly lost the ability to compact and repair databases, after more than two years of working fine.  I always get the error "Could not use [db]; file already in use."

 

Strangely, every time I attempt to compact and repair a database, an event appears in the system log seemingly not at all related to Access.  It's Event 19, Source OED, Provider Name Intel-SST-OED, description "Check the remaining resource budget. Module exceeds resource budget, failed to AllocateFwCps, STATUS = Insufficient system resources exist to complete the API.."

 

Intel-SST is Intel Smart Sound Technology.  Sound on the machine (Dell Inspiron 14 5000) works fine.  Nothing else is obviously wrong.  Why would compacting and repairing an Access database fail and cause an error in the audio driver?

10 Replies

Actually never mind the part about Event 19. Maybe that is just associated with the beep that happens when the compact-repair fails, and not related to the failure itself. (The beep sounds fine, but perhaps the audio driver is still sending an error). Still, I have a spontaneous loss of ability to compact and repair, after no obvious changes to the machine. The only recent automatic update was KB4023057, which seems to relate to the update engine itself and seems not much related to Access.

Have you tried compacting this externally e.g. from another database?

@isladogsI dread resorting to a laborious work-around for something that should work, and worked fine until recently.

 

Compacting from the command line, or compacting while the database is closed, both give the same error.

I can understand why you don't want to do laborious workrounds.
However, have a look at the very useful utility in this link: https://www.devhut.net/windows-explorer-ms-access-database-right-click-context-menu/

Over the years, Access has got less and less tolerant of issues that can cause corruption. Compacting an open database can indeed cause corruption in certain situations.
Perhaps your database has a lot of open connections when you try to compact? However, that wouldn't explain the errors occurring when run externally

 

Perhaps this is a side effect of the most recent Access bug? Which Access 365 build are you using?

FWIW: Several recent posts here and on other forums seem to indicate that the fix for the most recent bug is not effective for everyone. It's important to know which build you are on.
I currently have Version 2201 (Build 14827.20198), which is dated Feb 16 according to Microsoft. I first noticed the problem on February 18, and didn't attempt to compact anything on the 17th, so maybe the problem is actually caused this latest build.

My current version has a help message that mentions a bug in Version 2201 (Build 14827.20158) with the relevant symptoms, which was supposedly fixed in Version 2201 (Build 14827.20192) dated February 8.

Side note: I can't compact any database; it's not specific to a particular file. I use Access as a GUI for creating data that I load into SQL server, so I have dozens of files, none larger than 8MB, and none much connected to anything. The files don't have long histories. The only other odd thing about my system: in order to load Access data into SQL server locally for testing, I needed to download and install AccessDatabaseEngine_X64.exe from Microsoft. This provides the Access ODBC driver. I have been doing this for 20 years and it's always been fine. But on my current Windows 10 computer, most Windows updates seem to eliminate the ODBC driver, and I need to do a repair-reinstall of the Access database engine to get going again. This is a minor nuisance I have been living with for more than 2 years, and never affected the basic function of the MS Office Access application.

Hello @EasyOrImpossible,

I am glad to assist you.

You may operate some ordinary checks. Here are the things you must check before using the compact and repair method or any online repair tool.

 

  • All users running this MS Access have the privilege where the MDB file in the directory exists. If JET cannot create an LDB file when logging into a database it will generate a 3045 error.
  • You may check and confirm that the Access database is not marked as read-only.
  • Check your file, that should not be scanned or opened in any anti-virus present on your system.
  • Check your code to make it clear that you are not doing an explicit Close to the thing opened on your system and setting object variables to nothing as soon as you are done with something.

Check out this thread as well, if this could help your issue somewhere:

 

https://answers.microsoft.com/en-us/msoffice/forum/all/err-3045-impossible-to-use-nameddb-file-in-us...

 

Thanks,

SuyashThomson

My Access was just updated to Version 2202 Build 16.0.14931.20132 today, and unfortunately it still can't compact and repair databases.
best response confirmed by EasyOrImpossible (Occasional Contributor)
Solution
Some people have reported that this problem lingers IF they also have MacAfee Anti-Virus installed. Try disabling it to see if that corrects the problem. Also, see if you can white-list Access in the MacaFee AV if that is the case.
I do have MacAfee AV (also for years). When the problem first occurred, the first thing I tried (AV being always the prime suspect for anything) was to disable real-time scanning in MacAfee. That didn't help. But based on your suggestion, I went back and tried more variations. Closing Access, then turning off real time scanning, then reopening Access seemed to make the problem go away. Databases would compact again, strangely fast actually. It kept working even after I turned real-time AV scanning back on. So it seems to depend on the state of AV scanning at the moment the Access application is started.

Thanks for that suggestion.