From: PeterM on
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


From: Arvin Meyer [MVP] on
Try this one:

http://www.mvps.org/access/general/gen0041.htm
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access
Co-author: "Access 2010 Solutions", published by Wiley


"PeterM" <PeterM(a)discussions.microsoft.com> wrote in message
news:A1EBE708-F6C3-42E6-A047-5AE5D5B9A339(a)microsoft.com...
> 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
>
>


From: PeterM on
Arvin...

Didn't work. I get a message that it's invalid to try to compact an active
database using a macro or vba code. For my database, I do not have the file
menu displayed. Does it need to be visible before running the code?

Below is the code I run..

Public Function QuitAccess()
Select Case MsgBox("Do you really want to close the MDS system?",
vbYesNo Or vbQuestion Or vbDefaultButton2, "Please Confirm!")
Case vbYes
CommandBars("Menu Bar"). _
Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and repair database..."). _
accDoDefaultAction
Case vbNo
End Select
Application.Quit
End Function


"Arvin Meyer [MVP]" wrote:

> Try this one:
>
> http://www.mvps.org/access/general/gen0041.htm
> --
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.accessmvp.com
> http://www.mvps.org/access
> Co-author: "Access 2010 Solutions", published by Wiley
>
>
> "PeterM" <PeterM(a)discussions.microsoft.com> wrote in message
> news:A1EBE708-F6C3-42E6-A047-5AE5D5B9A339(a)microsoft.com...
> > 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
> >
> >
>
>
> .
>
From: Arvin Meyer [MVP] on
Add some error handling. Then when you debug by stepping through the code.
Show us where it errors.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access
Co-author: "Access 2010 Solutions", published by Wiley


"PeterM" <PeterM(a)discussions.microsoft.com> wrote in message
news:67A2EF69-E9FA-4D8D-B750-87B672CE9DB3(a)microsoft.com...
> Arvin...
>
> Didn't work. I get a message that it's invalid to try to compact an
> active
> database using a macro or vba code. For my database, I do not have the
> file
> menu displayed. Does it need to be visible before running the code?
>
> Below is the code I run..
>
> Public Function QuitAccess()
> Select Case MsgBox("Do you really want to close the MDS system?",
> vbYesNo Or vbQuestion Or vbDefaultButton2, "Please Confirm!")
> Case vbYes
> CommandBars("Menu Bar"). _
> Controls("Tools"). _
> Controls("Database utilities"). _
> Controls("Compact and repair database..."). _
> accDoDefaultAction
> Case vbNo
> End Select
> Application.Quit
> End Function
>
>
> "Arvin Meyer [MVP]" wrote:
>
>> Try this one:
>>
>> http://www.mvps.org/access/general/gen0041.htm
>> --
>> Arvin Meyer, MCP, MVP
>> http://www.datastrat.com
>> http://www.accessmvp.com
>> http://www.mvps.org/access
>> Co-author: "Access 2010 Solutions", published by Wiley
>>
>>
>> "PeterM" <PeterM(a)discussions.microsoft.com> wrote in message
>> news:A1EBE708-F6C3-42E6-A047-5AE5D5B9A339(a)microsoft.com...
>> > 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
>> >
>> >
>>
>>
>> .
>>