VBAでAccessの各テーブルのフィールドを取得する

• Excel VBA から Accessの各テーブルのフィールドを取得する。DBへの接続部分は VBAでAccessのDBに接続する を参照
• Code

• Sub getTableSheme()
  
      Dim myTable As ADOX.Table
      Dim myCol As ADOX.Column
      Dim rowIndex As Long, colIndex As Long
  
      Call connectDB("student.accdb")
      Sheets(1).Activate
      colIndex = 0
  
      For Each myTable In cat.Tables
          If myTable.Type = "TABLE" Or myTable.Type = "VIEW" Then
              colIndex = colIndex + 1
              Cells(1, colIndex) = myTable.Name
              Cells(2, colIndex) = myTable.Type
  
              For Each myCol In myTable.Columns
                  rowIndex = Cells(Rows.Count, colIndex).End(xlUp).Row + 1
                  Cells(rowIndex, colIndex) = myCol.Name
              Next
          End If
      Next
  
      Call disconnectDB
  
  End Sub

• typeも取得する

  • Sub getTableFields()
    
        Dim myTable As ADOX.Table
        Dim myCol As ADOX.Column
        Dim rowIndex As Long, colIndex As Long
    
        Call connectDB("student.accdb")
        Sheets(2).Activate
        colIndex = 0
    
        For Each myTable In cat.Tables
            If myTable.Type = "TABLE" Then
                colIndex = colIndex + 2
                Cells(1, colIndex) = myTable.Name
                Cells(2, colIndex) = myTable.Type
    
                For Each myCol In myTable.Columns
                    rowIndex = Cells(Rows.Count, colIndex).End(xlUp).Row + 1
                    Cells(rowIndex, colIndex) = myCol.Name
                    Cells(rowIndex, colIndex + 1) = myCol.Type
                Next
            End If
        Next
    
        Call disconnectDB
    
    End Sub