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

[techyspecy]

Let me add something to it. Use this view and it will give you all
columns and there attributes information like length, column
name, size, precision, scale blah blah blah ... My automated
validation is based on this view which allows me to validate my
textboxes on the fly. Moreover, you can pass the table name to it to get the individual table elements information.

CREATE VIEW dbo.Table_Cols AS SELECT a.name AS tablename, 
b.name AS columnname, c.name AS col_type_name, b.length AS 
Length, b.prec AS Prec, b.scale AS Scale, c.xtype AS col_type_id 
FROM dbo.sysobjects a INNER JOIN dbo.syscolumns b ON a.id = 
b.id INNER JOIN dbo.systypes c ON b.xtype = c.xtype