This is simply not possible. Compacting and repairing a database requires the database to be closed. As such, you can't compact and repair a database between steps in a sub or procedure, since the database is open when running the procedure.
You might notice the Compact and repair button on the ribbon requires an exclusive lock, closes the database, then compacts and repairs, and then reopens it.
My advice: either run the process from an external database, a VBScript file or PowerShell. Run the first part of your batch, close the file, compact and repair, reopen, run second part
Sample code
Dim fileLocation As String
DBEngine.CompactDatabase fileLocation, fileLocation & "_1"
Kill fileLocation
Name fileLocation & "_1" As fileLocation
You might also notice the Access compact and repair button doing something similar. If you run compact & repair, it moves the data to a database called Database.accdb in your current folder (name might vary based on existing names/database type), then deletes your current database, and then renames the new one.
Well, but nothing is impossible, right?
Well, some things are, but this isn't one of them, if you're willing to do some weird trickery. As I just said, the main problem is that the current database has to be closed. So, the workaround does the following:
- Programmatically create a VBScript file
- Add code to that file so we can compact & repair our database without having it open
- Open and run that file asynchronously
- Close our database before the compact & repair happens
- Compact and repair the database (creating a copy), deleting the old one, renaming the copy
- Reopen our database, continue the batch
- Delete the newly created file
Luckily, I had some time to spare, so I came up with the following solution:
Public Sub CompactRepairViaExternalScript()
Dim vbscrPath As String
vbscrPath = CurrentProject.Path & "\CRHelper.vbs"
If Dir(CurrentProject.Path & "\CRHelper.vbs") <> "" Then
Kill CurrentProject.Path & "\CRHelper.vbs"
End If
Dim vbStr As String
vbStr = "dbName = """ & CurrentProject.FullName & """" & vbCrLf & _
"resumeFunction = ""ResumeBatch""" & vbCrLf & _
"Set app = CreateObject(""Access.Application"")" & vbCrLf & _
"Set dbe = app.DBEngine" & vbCrLf & _
"Set objFSO = CreateObject(""Scripting.FileSystemObject"")" & vbCrLf & _
"On Error Resume Next" & vbCrLf & _
"Do" & vbCrLf & _
"If Err.Number <> 0 Then Err.Clear" & vbCrLf & _
"WScript.Sleep 500" & vbCrLf & _
"dbe.CompactDatabase dbName, dbName & ""_1""" & vbCrLf & _
"errCount = errCount + 1" & vbCrLf & _
"Loop While err.Number <> 0 And errCount < 100" & vbCrLf & _
"If errCount < 100 Then" & vbCrLf & _
"objFSO.DeleteFile dbName" & vbCrLf & _
"objFSO.MoveFile dbName & ""_1"", dbName" & vbCrLf & _
"app.OpenCurrentDatabase dbName" & vbCrLf & _
"app.UserControl = True" & vbCrLf & _
"app.Run resumeFunction" & vbCrLf & _
"End If" & vbCrLf & _
"objFSO.DeleteFile Wscript.ScriptFullName" & vbCrLf
Dim fileHandle As Long
fileHandle = FreeFile
Open vbscrPath For Output As #fileHandle
Print #fileHandle, vbStr
Close #fileHandle
Dim wsh As Object
Set wsh = CreateObject("WScript.Shell")
wsh.Run """" & vbscrPath & """"
Set wsh = Nothing
Application.Quit
End Sub
This does all the steps outlined above, and resumes the batch by calling the ResumeBatch function on the database that called this function (without any parameters). Note that things like click-to-run protection and antivirus/policy not liking vbscript files can ruin this approach.
