compact Access DB from Excel
 

I'm using the following code in Excel to allow users to compact their Access
database.

vDB = ActiveWorkbook.Sheets("Info").Range("C5")

Set AppAcc = New Access.Application
AppAcc.Visible = False
AppAcc.OpenCurrentDatabase vDB

DoCmd.SetWarnings False

AppAcc.CommandBars("Menu Bar").Controls("Tools").Controls("Database
utilities"). _Controls("Compact and repair database...").accDoDefaultAction

DoCmd.SetWarnings True
AppAcc.Visible = True
AppAcc.Quit acQuitSaveNone

Set AppAcc = Nothing

I would like to do a couple of things differently:

(1) I would like to change the directory the users will see on the dialog
box instead of the path and directory where the macro resides.

(2) I would like to display a message if the users selects the "cancel"
option instead of saving the database.

Is it possible to do make either one of these changes? Any help getting
started would be appreciated. Thanks for the help...
--
JT

compact Access DB from Excel
 

one solution?, command line approach

CreateObject("WScript.Shell").Run "MSAccess " & _
Chr(34) & ActiveWorkbook.Sheets("Info").Range("C5") & _
Chr(34) & "/runtime /compact", 0, True

HTH

"JT" wrote:

I'm using the following code in Excel to allow users to compact their Access
database.

vDB = ActiveWorkbook.Sheets("Info").Range("C5")

Set AppAcc = New Access.Application
AppAcc.Visible = False
AppAcc.OpenCurrentDatabase vDB

DoCmd.SetWarnings False

AppAcc.CommandBars("Menu Bar").Controls("Tools").Controls("Database
utilities"). _Controls("Compact and repair database...").accDoDefaultAction

DoCmd.SetWarnings True
AppAcc.Visible = True
AppAcc.Quit acQuitSaveNone

Set AppAcc = Nothing

I would like to do a couple of things differently:

(1) I would like to change the directory the users will see on the dialog
box instead of the path and directory where the macro resides.

(2) I would like to display a message if the users selects the "cancel"
option instead of saving the database.

Is it possible to do make either one of these changes? Any help getting
started would be appreciated. Thanks for the help...
--
JT