Compact & Repair throws error saying file is in use

Copper Contributor

When trying to compact and repair any database created on my computer i get an error saying file is already in use. I have tried every possible way to fix this - reinstalled Office 365 tried multiple file locations to save a database and no matter what i get the same error message when trying to compact and repair.

21 Replies
How are you trying to do this?
1. From the ribbon?
2. Using code in the open database
3. From another database using code
4. Using a command line switch
From the ribbon. Also, you should know it even happens if i create a new blank database hit Compact & Repair and I get the same error message

@lytell 

Hmm. It can happen when code is running in the background but certainly shouldn't happen in the latter case.

It sounds like you may have another copy of Access running hidden in the background. 

Close your Access database normally. Does the lock file get deleted? Now run Task Manager, change to detailed view and check for any running instances of Access. If you find any, click End Task.

 

Reopen your Access database & try to compact again.

If it still fails, first repeat the above, then open your database with the shift key held down to bypass any startup code. Now compact it. Any luck?

 

 

Hmm. Very odd.
Was the lock file deleted? Did you find any running instances of Access in background processes?
If you try to make changes to a database do you get any warnings that there is another copy already running?

One last try. Use the Run command to compact it. To do so, you need to type the Access program file path followed by your application file path followed by /compact and enclose both sections in quotes. Does that make sense?

Same Error message. This is insane.
Yes I agree but please could you answer each of my questions from my last post
This often happens if you run the C&R on a file off a network share. Moving the file to the local disk usually resolves the issue.
Was the lock file deleted? Did you find any running instances of Access in background processes? NO
If you try to make changes to a database do you get any warnings that there is another copy already running? NO
There is no Network share, I have created numerous databases all over C: drive (numerous folders), D: drive (another drive on my computer), removable USB drive. I have shut down and restarted numerous times. I have reinstalled Office 365. I have shut down anti-virus, shut down cloud based backups like Amazon and Google. I tried running in safe mode but access will not run in safe mode. I tried your suggestion with command prompt - all failed.
I have no more ideas - other than renaming and using the resulting Db1.accdb file onwards.
I appreciate all your help.

@lytell 

I'm also out of ideas. I've tried compacting local files opened both read only and exclusively. Both of those worked.
The only way I can get errors us when I deliberately open a second copy of a running database and then try to compact it. That was the reason for all my questions, Doing that causes this error message

 

Screenshot 2021-05-07 195843.png

One final suggestion. I don't think you've yet tried compacting from another database which was item 3 in my First reply. Please try that as well
I did not try that please elaborate

@lytell 

Create a function similar to this in a standard module

It should work whether or not the target database to be compacted is open as a backup copy is made and then the copy is compacted

 

Public Function CompactTargetDatabase()

'creates a compacted copy of the target database 

    Dim fso As Object
    Dim strOldPath As String, strNewPath As String, strTempPath As String
        
    Set fso = CreateObject("Scripting.FileSystemObject")                
    strOldPath = "full path to target database to be compacted"         
    strTempPath = "full path for temporary copy of target database"
    strNewPath = "full path for compacted copy of target database"
               
            'copy database to a temp file
            fso.CopyFile strOldPath, strTempPath
            Set fso = Nothing
                                  
            'compact the temp file 
            DBEngine.CompactDatabase strTempPath, strNewPath
            
            'delete the tempfile
            Kill strTempPath  
    
End Function

 No additional references should be needed

Seems to be looking for .MDB as opposed to .accdb in strTempPath
Nevermind I forgot to include the name of the DB. I think this worked it went from 18,560KB to 10,476KB
Clearly it did work! I use that approach to compact a BE database from the FE but it can be used to compact any database. The code can also be used to compact the current database to a backup file. Try changing strOldPath to the path of the current database and see if it works for you as well

NOTE: A minor modification will allow it to compact password protected databases as well