Paul,
I had a similar problem. I found an access addin called compacter which I can send you(email me). I put together some code to detect when the database reached a certain size. The user then got prompted with a tes no box to compact . see code below
The addin needs to be installed before it can work.
Function DataBaseSize()
'*******************************************
'Name: DataBaseSize (Function)
'Purpose: This warns the user to compact if the DB size gets to large
'Author: Mcgrco
'Date: August 19, 2002, 11:06:59 AM
'Called by:
'Calls: Calls an addin called compactor
'Inputs:
'Output:
'*******************************************
Dim dbsize As Long
Dim dbname As String
Dim msg As String
Dim CurUser As String
dbname = DBEngine(0)(0).Name
dbsize = FileLen(dbname)
On Error GoTo CompactError:
CurUser = UCase(Mid(CurrentUser, 1, 1)) & Mid(CurrentUser, 2, Len(CurrentUser))
msg = "WARNING! " & CurUser & "@" & "The database is currently at " & _
Format((dbsize) / 1049592, "##,##0") & "mb. It is" & Chr(10) & _
"recommended that you compact before " & Chr(10) & _
"running to help speed up the process. @Compact Now???"
If dbsize > 200000000 Then '200mb
If MsgBox(msg, vbYesNo, "Compact Database") = vbYes Then
Call Application.Run("Compacter.DoCompact", True)
End If
Else
End If
CompactError:
If err = 2517 Then 'if the addinin isnt installed warn the user
MsgBox "Cannot run compact function as the compact addin hasnt been installed" & Chr(10) & Chr(10) & _
"Select tools,addins,Addin Manager. Then add new" & Chr(10) & Chr(10) & _
"Browse for the file called compactor.mda in C:\Program Files\Liquidity" & Chr(10) & Chr(10) & _
"Ignore error, cant add to the registry"
End If
Exit Function
End Function
