Access 2010 code to execute compact & repair

Hi, Experts -
I'm using MS Access 2010 and have a routine that runs fine under Access 2003 but which issues an error message, "Invalid procedure call or argument" under 2010.  I've attached the snippet of code below.
Question - Is there a way to modify this code so it will run under 2010, as well as be backward compatible with 2003?  The databases were created under 2003, but the company will be migrating over to Office 2010 product line later this summer.
MsgBox ("Compact & repair will commence once this msgbox is closed.  This will take several seconds.  Please do not close ACCESS window.")
    CommandBars("Menu Bar"). _
    Controls("Tools"). _
    Controls("Database utilities"). _
    Controls("Compact and repair database..."). _
    accDoDefaultAction

Open in new window

LVL 1
OGSanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TomMicrosoft ISV PartnerCommented:
Try this:
DoCmd.RunCommand acCmdCompileAndSaveAllModules
0
Jeffrey CoachmanMIS LiasonCommented:
1.Do you have the menu bar displayed in the Ribbon?
(In Access 2007, custom Menus and toolbars are now under the AddIns Tab on the Ribbon)

2. Acc 2003 and older used Menus and Toolbars, Access 2010 uses the Ribbon.
So try this instead, it does not seem to work in 2007, but perhaps it will in 2010?:
DoCmd.RunCommand acCmdCompactDatabase
Or this:
DoCmd.RunCommand acCmdRepairDatabase

;-)

JeffCoachman
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
Oh,

Or try the code here to try (Application.CompactRepair()):
http://msdn.microsoft.com/en-us/library/bb237807%28office.12%29.aspx
0
OGSanAuthor Commented:
Thanks for the replies.  I've tried the first two suggestions made above:
1.  Result of DoCmd.RunCommand acCmdCompileAndSaveAllModules
     Nothing happened, nada, zip.
2.  Result of DoCmd.RunCommand acCmdCompactDatabase
     Error message, "You cannot compact the open database by running a macro or Visual Basic code.
     Instead of using a macro or code, click the File tab and then click Compact and Repair Database."
I'm going to go with this since - even though it doesn't do anything - it at least informs the MS Access 2010 user of what to do and where to do it.  
0
Jeffrey CoachmanMIS LiasonCommented:
;-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.