VB - Retrieve Table/Field Schema

[Armbruster]

Option Explicit
 
Dim cnTables As ADODB.Connection
Dim rsTables As ADODB.Recordset
Dim rsFields As ADODB.Recordset
Dim strCn As String
Dim strSQL As String
Dim i As Integer
 
'Requires ADO reference
'Written by Carl R. Armbruster
'Free to use - it's yours!
 
Private Sub Form_Load()
    'database connnection string
    strCn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Persist Security Info=False;" & _
            "Data Source=" & App.Path & "\Northwind.mdb"
End Sub
Private Sub Command1_Click()
    'recordset of field names
    Set rsFields = New ADODB.Recordset
    'connection to recieve tabel schema
    Set cnTables = New ADODB.Connection
    'open connection
    cnTables.Open strCn
    
    'get table schema
    Set rsTables = cnTables.OpenSchema(adSchemaTables)
    'loop through table recordset
    Do While Not rsTables.EOF
        'only get the tables (table schema also retrieves views)
        If rsTables!TABLE_TYPE = "TABLE" Then
            'get an empty recordset(1 never equals 2!)
            strSQL = "SELECT * FROM [" & rsTables!TABLE_NAME & "] WHERE 1 = 2"
            rsFields.Open strSQL, strCn, adOpenStatic, adLockReadOnly
            'loop through the field collection
            For i = 0 To rsFields.Fields.Count - 1
                Debug.Print "Table: " & rsTables!TABLE_NAME & "; Field: " & rsFields.Fields(i).Name
            Next i
            rsFields.Close
        End If
        rsTables.MoveNext
    Loop
    
    'close and cleanup
    rsTables.Close
    Set rsTables = Nothing
    
    cnTables.Close
    Set cnTables = Nothing
    
    Set rsFields = Nothing
End Sub