Dear MS Access Expert.
I know how to compact a database by using a hidden form and upon closing the database compact it if it is over a certain file size. Please see code below.
What I want is to check the size of the database every 30 minutes; If it reaches a certain size I want to compact the database without closing it.
I have a need to compact so often because I am continously importing / refreshing / deleting tables on a 1 minute timer. This is severly bloating the database very very quickly.
I can use the below subroutine to compact the database by closing it if it becomes bloated and compacting; then opening it up again... but there must be a better way? If there isn't I guess I must resort to closing and re-opening the database?
I know how to compact a database by using a hidden form and upon closing the database compact it if it is over a certain file size. Please see code below.
What I want is to check the size of the database every 30 minutes; If it reaches a certain size I want to compact the database without closing it.
I have a need to compact so often because I am continously importing / refreshing / deleting tables on a 1 minute timer. This is severly bloating the database very very quickly.
I can use the below subroutine to compact the database by closing it if it becomes bloated and compacting; then opening it up again... but there must be a better way? If there isn't I guess I must resort to closing and re-opening the database?
Code:
Public Sub CompactDatabase()
'Run this code before closing the Database.
Dim vStatusBar As Variant
On Error GoTo Err_CompactOnClose
If FileLen(CurrentDb.Name) > 2000000 Then
Application.SetOption ("Auto Compact"), 1
Application.SetOption "Show Status Bar", True
vStatusBar = SysCmd(acSysCmdSetStatus, "The application must be compacted, please do not interfere with the Compacting process!")
Else
Application.SetOption ("Auto Compact"), 0
End If
Exit_CompactOnClose:
Exit Sub
Err_CompactOnClose:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_CompactOnClose
End Sub