Get list of custom command bars from external MS Access database

[patriciaxxx]

Hi,
I am trying to get 2 lists one is custom menus and the other is custom shortcut menus from any external MS Access database I connect to.

My problems are:
1) The external database must remain hidden whilst the data is being collected ie. It must never be shown..
2) The loop needs to collect both lists so that I can populate two separate list boxes.
3) It needs to work in all access versions and mdb, mde etc

I have tried the following code in MS Access 2000 and 2003 and it works to a point. But sometimes the database shows until the code completes whilst a different database might not show. Also I can’t work out how to get the other list in the loop. The code has become too difficult for me to sort out and get working properly and needs tidying up. Any help will be much appreciated.

Option Compare Database
Option Explicit

Private Declare Function ShowWindow Lib "user32" _
(ByVal hwnd As Long, ByVal nCmdShow As Long) As Long

Sub test()
Dim sw As Long
Dim app As Object
On Error Resume Next

Set app = CreateObject("Access.Application")
'app.Visible = False 'i have tried this line but it seems to make no difference

Dim strpath As String
'strpath = "C:\mydb97.mdb"
strpath = "C:\mydb2k.mdb"
'strpath = "C:\mydb23k.mdb"
'strpath = "C:\mydb.mde"

app.OpenCurrentDatabase strpath

sw = ShowWindow(app.hWndAccessApp, 0)
Dim i As Long
Dim j As Long
Dim sCmdBar As CommandBar
Debug.Print app.CommandBars.Count
'RowSourceType = "Value List"
Debug.Print "Number", "Name", "Visible", "Built-in"
For i = 1 To app.CommandBars.Count
Set sCmdBar = app.CommandBars(i)
If sCmdBar.BuiltIn = False And sCmdBar.Type = 2 Then
j = j + 1
Debug.Print j, sCmdBar.Name, sCmdBar.Type, sCmdBar.BuiltIn
End If
Next i
app.Quit
app.UserControl = True
Set app = Nothing
If Err.Number = 0 Then
app.Quit
Else
Err.Clear
End If
End Sub