Compact Database

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

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" wrote in message
...
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

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" wrote in message
...
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




.

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" wrote in message
...
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" wrote in message
...
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




.