1. Access の CurrentData オブジェクトを使用する方法
2. DAO の TableDefs コレクションを使用する方法
3. ADO の OpenSchema メソッドを使用する方法
4. ADOX の Catalog オブジェクトを使用する方法

Sub listAllTablesName()
'CurrentData オブジェクトからテーブル名一覧を表示
'Access2000 以降のみ 2011/02/12 pPoy
    Dim objAC As Object         'Access.Application
    Dim objTmp As Object        'AccessObject
    Dim dbs As Object
    Dim stPath As String
    Dim i As Long
    
    stPath = "c:\Test\dbTest2010.accdb"
    If Dir(stPath) = "" Then Exit Sub
    
    Set objAC = CreateObject("Access.Application")
    objAC.OpenCurrentDatabase (stPath)
    
    'AllTables コレクションから検索
    Set dbs = objAC.Application.CurrentData
    i = 1
    '新規シートをシートの先頭に追加し書き出し
    Worksheets.Add before:=Sheets(1)
    With Sheets(1)
        .Activate
        For Each objTmp In dbs.AllTables
            If Left(objTmp.Name, 4)  <> "MSys" Then
                .Cells(i, 1).Value = objTmp.Name
                i = i + 1
            End If
        Next objTmp
    End With
    
    objAC.CloseCurrentDatabase
    objAC.Quit
    Set dbs = Nothing: Set objAC = Nothing
End Sub

Sub listDAOTablesName()
' DAO でテーブル名一覧を取得 2011/02/12 pPoy
    Dim dbeng As Object             'DAO.DBEngine
    Dim dbs As Object               'DAO.Database
    Dim tbl As Object               'DAO.TableDef
    Dim stPath As String
    Dim i As Long

    stPath = "c:\Test\dbTest2010.accdb"                 '★1
    If Dir(stPath) = "" Then Exit Sub

    'DAO 12.0 の場合 (Access2007,Access2010)
    Set dbeng = CreateObject("DAO.DBEngine.120")        '★2
    Set dbs = dbeng.workspaces(0).OpenDatabase(stPath)
    
    With ActiveSheet
        i = 1
        For Each tbl In dbs.TableDefs
            'システムテーブルは除外
            If Left(tbl.Name, 4) <> "MSys" Then
                .Cells(i, 1).Value = tbl.Name
                i = i + 1
            End If
        Next tbl
    End With
    
    dbs.Close
    Set dbs = Nothing: Set dbeng = Nothing
End Sub

    mdbPath = "C:\Test\dbTest.mdb"

    mdbPath = "C:\Test\dbTest.mdb"

    Set dbeng = CreateObject("DAO.DBEngine.36")

Sub ListADOTables()
'ADO OpenSchema メソッドを使用 2011/02/12
    Dim cnn As Object            'ADODB.Connection
    Dim rst As Object            'ADODB.Recordset

    Dim stSQL As String
    Dim stProvider As String
    Dim stPath As String
    Dim i As Long
    
    Const adSchemaTables = 20

    'Access2007,Access2010の場合
    stProvider = "Microsoft.ACE.OLEDB.12.0"             '★1

    stPath = "c:\Test\dbTest2010.accdb"                 '★2
    If Dir(stPath) = "" Then Exit Sub

    Set cnn = CreateObject("ADODB.connection")
    cnn.Provider = stProvider
    cnn.Open stPath
   
    Set rst = cnn.OpenSchema(adSchemaTables, _
                  Array(Empty, Empty, Empty, "TABLE"))
    i = 1
    While Not rst.EOF
        With ActiveSheet
            .Cells(i, 1).Value = rst.Fields("TABLE_NAME").Value
            i = i + 1
            rst.MoveNext
        End With
    Wend

    rst.Close: cnn.Close
    Set rst = Nothing: Set cnn = Nothing
End Sub

    mdbPath = "C:\Test\dbTest.mdb"

    mdbPath = "C:\Test\dbTest.mdb"

    cnn.Provider = "Microsoft.Jet.OLEDB.4.0"

1. ADO を使用する為のコンポートネント (MDAC) は、Windows2000 以降は、OS に含まれていますので、
   追加取得する必要はありません。
   
   
2. OpenSchema の引数でクエリ制約に、"TABLE" を指定している為、システムテーブルやリンクテーブルは除外されます。
   
   
3. OpenSchema の引数でクエリ制約に何も指定しない場合、「システム テーブル」、「リンク テーブル」、「選択クエリ」 も一緒に取得されます。

Sub listADOXTablesName()
' ADOX でテーブル名一覧を取得 2011/02/12 pPoy
    Dim cat As Object               'ADOX.Catalog
    Dim tbl As Object               'ADOX.Table
    Dim stProvider As String
    Dim stPath As String
    Dim i As Long
    
    stPath = "c:\Test\dbTest2010.accdb"             '★1
    If Dir(stPath) = "" Then Exit Sub

    'Access2007,Access2010の場合
    stProvider = "Microsoft.ACE.OLEDB.12.0"         '★2

    Set cat = CreateObject("ADOX.Catalog")
    cat.ActiveConnection = _
        "Provider=" & stProvider & ";" & _
        "Data Source=" & stPath & ";"
    
    With ActiveSheet
        i = 1
        For Each tbl In cat.Tables
            'システムテーブルは除外
            If Left(tbl.Name, 4) <> "MSys" Then
                If tbl.Type <> "VIEW" Then
                    .Cells(i, 1).Value = tbl.Name
                    i = i + 1
                End If
            End If
        Next tbl
    End With
        
    Set cat = Nothing
End Sub

    mdbPath = "C:\Test\dbTest.mdb"

    mdbPath = "C:\Test\dbTest.mdb"

    cnn.Provider = "Microsoft.Jet.OLEDB.4.0"

1. Microsoft Jet の OLE DB Provider では、Tables コレクションに View も含まれてしまいます。
   テーブル名一覧を取得する為には、システムテーブルと View を除外する処理が必要となります。
   
   
2. ADOX を使用する為のコンポートネント (MDAC) は、Windows2000 以降は、OS に含まれていますので、
   追加取得する必要はありません。

1. Access の CurrentData オブジェクトを使用する方法は、実行する PC に Access が必要です。
   非表示の Access を開く為、処理も遅くなります。
   対象の Access データベースに、起動時のマクロや VBA がある場合は、それが実行されます。
   ただし、この方法は対象が Access 2000 以降でしたら、バージョンを選びません。
   
   
2. DAO を使用する場合は、実行する Excel のバージョンによって、DBEngine のバージョンを区別する必要があります。
   
   
3. ADO や ADOX を使用する場合は、実行する Excel のバージョンによって、指定する Provider 名を区別する必要があります。
   
   
4. Accessのクエリー名一覧を取得するマクロは、こちら です
   
   
5. Accessデータを取得するマクロは、こちら です。
   
   
6. Office System ドライバ用 コンポーネントは、以下のマイクロソフトのサイトからダウンロードできます。
   Microsoft Access データベース エンジン 2010 再頒布可能コンポーネント
   
   
7. WindowsXP 用の以下のコンポーネントが、マイクロソフトのサイトからダウンロードできます。
   Jet 4.0 Service Pack 8 (SP8) for Windows XP