P
PeterM
I've read and seen tons of theories about how to compact an active database
via VBA code for AC2003. The following code works for me.
Public Function QuitAccess()
Select Case MsgBox("Do you really want to close the MDS system?",
vbYesNo Or vbQuestion Or vbDefaultButton2, "Please Confirm!")
Case vbYes
AutoCompactCurrentProject
Application.Quit
Case vbNo
End Select
End Function
Public Function AutoCompactCurrentProject()
Dim fs, f, s, filespec
Dim strProjectPath As String, strProjectName As String
strProjectPath = Application.CurrentProject.Path
strProjectName = Application.CurrentProject.Name
filespec = strProjectPath & "\" & strProjectName
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.getfile(filespec)
s = CLng(f.Size / 1000000) 'convert size of app from bytes to Mb’s
If s > 80 Then 'edit the 80 (Mb’s) to the max size you want
to allow your app to grow.
Application.SetOption ("Auto Compact"), 1 'compact app
Dim strMsg As String
strMsg = "The Medical Diary System needs to be compacted." & vbCrLf
& vbCrLf _
& "This process will begin after you click on the OK button. " _
& "Please be patient while this process completes." & vbCrLf &
vbCrLf _
& "It may take several minutes to run."
MsgBox strMsg, vbInformation, "System Maintenance - Compact Database"
Else
Application.SetOption ("Auto Compact"), 0 'no don’t compact app
End If
End Function
It works great. If the size of the database is greater than 80mb, it turns
the Compact on Close option on, if not, it turns the Compact on Close option
off. As I said, it works great.
The problem is that the Application.Quit command is executed, Access shuts
down and is gone from the system tray. However, it is compacting the
database in the background. If you try to restart the database while it's
compacting, it ignores the call until the compact is complete. You can click
on a shortcut to open the database as many times as you want, but it will not
run the database until the compact finishes and there is no way to determine
when it's done.
I'm not obligated to use this solution. If someone has a better idea, that
would be great. Has anyone ever run into this problem? I'd appreciate any
ideas that you might have... thanks
via VBA code for AC2003. The following code works for me.
Public Function QuitAccess()
Select Case MsgBox("Do you really want to close the MDS system?",
vbYesNo Or vbQuestion Or vbDefaultButton2, "Please Confirm!")
Case vbYes
AutoCompactCurrentProject
Application.Quit
Case vbNo
End Select
End Function
Public Function AutoCompactCurrentProject()
Dim fs, f, s, filespec
Dim strProjectPath As String, strProjectName As String
strProjectPath = Application.CurrentProject.Path
strProjectName = Application.CurrentProject.Name
filespec = strProjectPath & "\" & strProjectName
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.getfile(filespec)
s = CLng(f.Size / 1000000) 'convert size of app from bytes to Mb’s
If s > 80 Then 'edit the 80 (Mb’s) to the max size you want
to allow your app to grow.
Application.SetOption ("Auto Compact"), 1 'compact app
Dim strMsg As String
strMsg = "The Medical Diary System needs to be compacted." & vbCrLf
& vbCrLf _
& "This process will begin after you click on the OK button. " _
& "Please be patient while this process completes." & vbCrLf &
vbCrLf _
& "It may take several minutes to run."
MsgBox strMsg, vbInformation, "System Maintenance - Compact Database"
Else
Application.SetOption ("Auto Compact"), 0 'no don’t compact app
End If
End Function
It works great. If the size of the database is greater than 80mb, it turns
the Compact on Close option on, if not, it turns the Compact on Close option
off. As I said, it works great.
The problem is that the Application.Quit command is executed, Access shuts
down and is gone from the system tray. However, it is compacting the
database in the background. If you try to restart the database while it's
compacting, it ignores the call until the compact is complete. You can click
on a shortcut to open the database as many times as you want, but it will not
run the database until the compact finishes and there is no way to determine
when it's done.
I'm not obligated to use this solution. If someone has a better idea, that
would be great. Has anyone ever run into this problem? I'd appreciate any
ideas that you might have... thanks