Forum Discussion
Compact & Repair throws error saying file is in use
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
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 FunctionNo additional references should be needed
- 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
- Seems to be looking for .MDB as opposed to .accdb in strTempPath
- 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.
- There is no Network share, I have created numerous databases all over C: drive (numerous folders), 😧 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.
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?
- 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
