CODEWITHSUNDEEP
ms-accessms-access-2007vba
Brett G
Brett G

Reputation: 51

"Invalid procedure or argument" error

I have inherited an Access DB, and it is erroring out when trying to open with this error message:

Run-time error '5':
Invalid procedure or argument

I have traced it to the following code in the VBE:

Set cbMainMenu = CommandBars.Add(Name:="OIG Main Menubar", _
Position:=msoBarTop, MenuBar:=True, Temporary:=True)

I have added a check before this from some Googling research, but it does not seem to work, as it is still erroring (with and without the error handling change):

    On Error Resume Next
    Application.CommandBars(cbMainMenu).Delete
    On Error GoTo 0

Anyone have any ideas, or can point me in the right direction to fix this error? I am going to keep plodding at it for a bit.

EDIT:

This does not appear to be the main, initial error. I am working to find it now, and will re-post if/when I find it.

EDIT2:

This is what is actually causing the error, going to look around and see if I can take care of it:

Set cbcToolsDBCompact = cbpToolsMenu.Controls.Add(Id:=CommandBars("Menu Bar").Controls("Tools").CommandBar.Controls("Database Utilities").CommandBar.Controls("Compact And Repair Database...").Id)

EDIT3: Changing the above code to:

Set cbcToolsDBCompact = cbpToolsMenu.Controls.Add(Id:=2071)

Fixed this issue, and the option for comact/repair is there now. (and it even works). Since no one really posted a specific answer, other than in comments, I am going to use the 'Answer your own Question' option at the bottom so this has an answer for the future. Also, I found the code for this here:

MS Access: how to compact current database in VBA

Upvotes: 3

Views: 2426

Answers (2)

Brett G
Brett G

Reputation: 51

Answering my own question here.

This was an issue with the "Compact and Repair Database" option being moved from where it was in Access 2003.

The VBA code is adding it as a menu option, and in 2003, this is what the code looked like:

Set cbcToolsDBCompact = cbpToolsMenu.Controls.Add(Id:=CommandBars("Menu Bar").Controls("Tools").CommandBar.Controls("Database Utilities").CommandBar.Controls("Compact And Repair Database...").Id)

In 2007, as per this issue, found here, MS Access: how to compact current database in VBA is how this needs to be handled:

Set cbcToolsDBCompact = cbpToolsMenu.Controls.Add(Id:=2071)

Upvotes: 1

ray
ray

Reputation: 8699

My guess is that it's a reference issue.

In the Visual Basic IDE, Click Tools/References: enter image description here

In the dialog that pops up, look for Microsoft Office x.0 Object Library:

enter image description here

Does it have the word "missing" next to it? If so, that's your problem.

Uncheck the missing reference and scroll down and check the one installed with your version of Office.

Upvotes: 1

Related Questions