Hi, Justin.
Question, is there a way that the database automatically compact itself after
it hits a certain amount of megs (whatever number I set it to be).
With VBA in Access 2000 and later versions, yes. First, create a new table,
tblAdmin, with the following structure:
ID, AutoNumber, primary key
LastCompact, Date/Time
Next, if you don't already have a similar function to determine the file's
size, create a new standard module and paste the following code into it:
Public Function getFileSize(sFilePath As String, Optional sSize As String)
As Long
On Error GoTo ErrHandler
Dim nByteSize As Currency
Dim nFileSize As Currency
Const KILO As Long = 1024
nByteSize = FileLen(sFilePath)
If (UCase$(sSize) = "M") Then
nFileSize = nByteSize / KILO / KILO
ElseIf (UCase$(sSize) = "K") Then
nFileSize = nByteSize / KILO
Else
nFileSize = nByteSize
End If
getFileSize = nFileSize
Exit Function
ErrHandler:
MsgBox "Error in getFileSize( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Function
Next, if you don't already have a start up form, create a new form and place
the following code in its OnOpen( ) event:
Private Sub Form_Open(Cancel As Integer)
On Error GoTo ErrHandler
Dim dt As Date
Const MAX_SIZE As Long = 300
dt = Nz(DLookup("LastCompact", "tblAdmin"), #1/1/1900#)
If (dt < Date) Then
If (getFileSize(CurrentProject.Path & "\" & _
CurrentProject.Name, "M") > MAX_SIZE) Then
CurrentDb().Execute "UPDATE tblAdmin " & _
"SET LastCompact = #" & Date & "#"
CommandBars("Menu Bar").Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and Repair database..."). _
accDoDefaultAction
End If
End If
Exit Sub
ErrHandler:
MsgBox "Error in Form_Open( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Sub
If you've already got your own function to determine the file size, then
replace the call to getFileSize( ) in the form's OnOpen( ) event with your
own. If you've already got a start up form, then add the code in this
Form_Open( ) event to your own form's OnOpen( ) event, ensuring that this
compaction code occurs before any other executable code.
If you've already got a start up form, then you're done. If not, then
select the Tools -> Startup... menu to open the Startup dialog window. Set
the "Display Form/Page" combo box to the name of your new start up form and
close the dialog window. Now, you're done.
Each time the database application is opened, it will check whether the
automatic compaction has already taken place today. If it hasn't, then it
checks the file size. If it exceeds the limit, then the file is compacted.
Therefore, the file will be compacted automatically once a day, not every
single time the database is opened if it's over the limit.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
