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. :p 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