Listing Tables and Fields

Earlier in this chapter you learned how to enumerate tables in the Northwind database by accessing the Tables collection of the ADOX Catalog object. The procedures in Hands-On 11-12 and 11-13 demonstrate how to use the OpenSchema method of the ADO Connection object to obtain more information about a database table and its fields.

© Hands-On 11-12: Using the OpenSchema Method to List Database Tables

1. In the Visual Basic Editor window, choose Insert | Module.

2. In the module's Code window, type the ListTbls2 procedure shown below.

3. Choose Run | Run Sub/UserForm to execute the procedure. Sub ListTbls2()

' This procedure lists database tables using the OpenSchema method Dim rst As ADODB.Recordset

Set rst = CurrentProject.Connection.OpenSchema(adSchemaTables)

Do Until rst.EOF

Debug.Print rst.Fields("TABLE_TYPE") & " ->" _

& rst.Fields("TABLE_NAME") rst.MoveNext Loop End Sub

Obtaining the names of fields requires that you use adSchemaColumns as the parameter for the OpenSchema method. The ListTblsAndFields procedure in Hands-On 11-13 retrieves the names of fields in each table of the Northwind database.

© Hands-On 11-13: Listing Tables and Their Fields Using the OpenSchema Method

1. In the Visual Basic Editor window, choose Insert | Module.

2. In the module's Code window, type the ListTblsAndFields procedure shown below.

3. Choose Run | Run Sub/UserForm to execute the procedure.

Sub ListTblsAndFields()

Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Dim curTable As String Dim newTable As String Dim counter As Integer

Set conn = New ADODB.Connection conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=" & CurrentProject.Path & _ "\Northwind.mdb"

Part II

Set rst = conn.OpenSchema(adSchemaColumns)

counter = 1

Do Until rst.EOF

curTable = rst!table_Name If (curTable <> newTable) Then newTable = rst!table_Name Debug.Print "Table: " & rst!table_Name counter = 1 End If

Debug.Print "Field" & counter & ": " & rst!Column_Name counter = counter + 1 rst.MoveNext Loop rst.Close conn.Close Set rst = Nothing Set conn = Nothing End Sub

0 0

Post a comment