3

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

Community
  • 1
  • 1
Brett G
  • 51
  • 7
  • 1
    open the database when holding **shift** and then try compiling it, to see if the line can be identified. If compiling does not give an error, then you can open each form in turn to see which line is actually giving the error – SeanC Aug 10 '12 at 14:26
  • 1
    Try changeing `Application.CommandBars(cbMainMenu).Delete` to `Application.CommandBars("OIG Main Menubar").Delete` – talbright Aug 10 '12 at 14:30
  • I am thinking that this is not the initial error anymore, as I have done some experimenting, and the DB makes it to some code that I put in for debugging AFTER this point. Going to hunt around a bit more, and see where this is actually happening. Thanks for the help so far. – Brett G Aug 10 '12 at 14:41
  • 2
    That is Access 2003 style menu bar, are you working with an mdb? The code in that line does not cause a problem for me, when run by itself. – Fionnuala Aug 10 '12 at 14:41
  • Yeah, it is an .mdb, but I can not get into the db to save it as in 2007 format, when I go in while holding 'SHIFT' I only get the option to save the procedure that I am looking at with 'SAVE-AS' – Brett G Aug 10 '12 at 14:43
  • Converting the DB (finally) does not fix the problem – Brett G Aug 10 '12 at 14:47
  • @talbright Thanks, that helped some, and I believe has gotten me down to the real error message. I am going to update the question, and do some searching as well, see if I can take care of it. – Brett G Aug 10 '12 at 14:50
  • +1 to Remou's suggestion. The version differences in the Office applications are drastic in some cases in terms of which menu does what (or how they're labeled). My guess is `"Compact And Repair Database..."` or some other label doesn't exist anymore or has moved. – Gaffi Aug 10 '12 at 14:57
  • @Gaffi Indeed, this appears to be it. That option has moved in 2K7, so when it is trying to add it to the menu, well, it barfs. Searching how to add it in 2K7 now. – Brett G Aug 10 '12 at 15:04

2 Answers2

1

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.

ray
  • 8,521
  • 7
  • 44
  • 58
1

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)
Community
  • 1
  • 1
Brett G
  • 51
  • 7