May 05 2021 07:10 PM
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.
May 06 2021 12:01 AM
May 06 2021 04:56 AM
May 06 2021 05:38 AM
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?
May 06 2021 01:40 PM
May 06 2021 05:20 PM
May 07 2021 12:01 AM
May 07 2021 02:23 AM
May 07 2021 07:35 AM
May 07 2021 07:44 AM
May 07 2021 08:16 AM
May 07 2021 09:15 AM
May 07 2021 12:04 PM
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
May 07 2021 12:42 PM
May 07 2021 01:37 PM
May 07 2021 03:28 PM
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
May 08 2021 12:27 PM
May 08 2021 12:33 PM
May 08 2021 01:04 PM