To further follow up on my last post regarding the docmd.quit vs.
Application.qui t using access 2007, I noticed that docmd.quit will correctly
compact the database (program file) if you have the "Compact on Close"
option set for the current database.
However, even with Compact on Close set, if you use the Application.Qui t
acQuitSaveNone, the compacting will not fire.
This behavior actually seems desirable, as you can now make a program
smarter upon exiting your program. If you have an exit button that performs
some housekeeping type procs, backup, etc., then exits the access program,
you can take advantage of the behavior above (docmd.quit vs.
application.qui t) to determine if you should compact.
To do this, make sure "Compact on Close" is set for the current database
(your access program file), then run the following function on your exit
button.
Hope this helps people who might have been looking for a similar solution.
This behavior works in Access 2007, but I'm not sure about 2003.
Public Function ExitButton() As Boolean
Dim progsize As Long, compactyn As Integer
progsize = FileLen(Current Db.Name)
If progsize 40000000 Then '=40 MB. Set a reasonable number where you
think the program should be compacted
compactyn = -1
Else
compactyn = 0
End If
'MsgBox "Program size is now: " & progsize & " bytes. Setting compact on
close to: " & compactyn
'Call AutomatedBU(0) 'run any of your procs, etc.
'Call CloseOpenTabs 'a routine to close out all open forms
'
Exit_btnExit_Cl ick:
'
If compactyn = -1 Then
DoCmd.Quit 'the docmd.quit will correctly run the compact on close
Else
Application.Qui t acQuitSaveNone 'For whatever reason, this command
Ignores the "Compact on Close" setting
End If
'
' Exit Function
Err_btnExit_Cli ck:
If Err = 2450 Then 'user mistankingly closed while previewing a report
Exit Function
ElseIf Err = 2501 Then
Resume Next
Else
MsgBox Err.Description & " - " & Err.Number & ", Function:
ExitButton", vbCritical
Resume Exit_btnExit_Cl ick
End If
End Function
Application.qui t using access 2007, I noticed that docmd.quit will correctly
compact the database (program file) if you have the "Compact on Close"
option set for the current database.
However, even with Compact on Close set, if you use the Application.Qui t
acQuitSaveNone, the compacting will not fire.
This behavior actually seems desirable, as you can now make a program
smarter upon exiting your program. If you have an exit button that performs
some housekeeping type procs, backup, etc., then exits the access program,
you can take advantage of the behavior above (docmd.quit vs.
application.qui t) to determine if you should compact.
To do this, make sure "Compact on Close" is set for the current database
(your access program file), then run the following function on your exit
button.
Hope this helps people who might have been looking for a similar solution.
This behavior works in Access 2007, but I'm not sure about 2003.
Public Function ExitButton() As Boolean
Dim progsize As Long, compactyn As Integer
progsize = FileLen(Current Db.Name)
If progsize 40000000 Then '=40 MB. Set a reasonable number where you
think the program should be compacted
compactyn = -1
Else
compactyn = 0
End If
'MsgBox "Program size is now: " & progsize & " bytes. Setting compact on
close to: " & compactyn
'Call AutomatedBU(0) 'run any of your procs, etc.
'Call CloseOpenTabs 'a routine to close out all open forms
'
Exit_btnExit_Cl ick:
'
If compactyn = -1 Then
DoCmd.Quit 'the docmd.quit will correctly run the compact on close
Else
Application.Qui t acQuitSaveNone 'For whatever reason, this command
Ignores the "Compact on Close" setting
End If
'
' Exit Function
Err_btnExit_Cli ck:
If Err = 2450 Then 'user mistankingly closed while previewing a report
Exit Function
ElseIf Err = 2501 Then
Resume Next
Else
MsgBox Err.Description & " - " & Err.Number & ", Function:
ExitButton", vbCritical
Resume Exit_btnExit_Cl ick
End If
End Function
Comment