What is ADO? - Examples 4-6

(Page 10 of 12 )

 

Example 4

This example uses the Fields collection of the recordset to loop through all of the fields in a recordset, then prints the details in the debug window:

' Declare the object variables
Dim objRs As ADODB.Recordset
Dim objFld As ADODB.Field
Set pbjRs = New ADODB.Recordset

' Open a recordset on the 'authors' table using the
' OLE DB Driver for ODBC
' without using a connection
objRs.Open "authors", "DSN=pubs", _
         adOpenForwardOnly, adLockReadOnly, adCmdTable

' Loop through the Fields collection printing the
' name of each Field
' The Fields collection is discussed in Chapter 6
For Each objFld In objRs.Fields
     Debug.Print objFld.Name; vbTab;
Next
Debug.Print

' Loop through the records in the recordset
While Not objRs.EOF
     ' Loop through the fields, this time printing out
     ' the value of each field
     For Each objFld In objRs.Fields
          Debug.Print objFld.Value; vbTab;
     Next Debug.Print
     objRs.MoveNext
Wend

' Close the recordset and release the memory
objRs.Close
Set objRs = Nothing
Set objFld = Nothing

Example 5

This is an ADOX example; it uses a Catalog and its associated Tables collection to list the tables in a data store:

' Declare the object variables
Dim objCat    As ADOX.Catalog
Dim objTable As ADOX.Table
Dim strConn  As String
Set objCat = New ADOX.Catalog

' set the connection string
strConn="Provider=Microsoft.Jet.OLE DB.4.0; Data Source= _
             C:\temp\pubs.mdb"

' point the catalog at a data store
objCat.ActiveConnection = strConn

' loop through the tables collection
For Each objTable In objCat.Tables
     Debug.Print objTable.Name
Next

' clean up
Set objTable = Nothing
Set objCat = Nothing

Example 6

This example also shows ADOX, and uses a Catalog, its Tables collection, and the Indexes and Columns collections for a table to print a list of columns for the index:

Dim objCat       As ADOX.Catalog
Dim objTbl       As ADOX.Table
Dim objIdx       As ADOX.Index
Dim objCol       As ADOX.Column
Dim strConn     As String
Set objCat = New ADOX.Catalog

' set the connection string
strConn="Provider=Microsoft.Jet.OLE DB.4.0; Data Source= _
             C:\temp\pubs.mdb"

' point the catalog at a data store
objCat.ActiveConnection = strConn

' loop through the tables
For Each objTbl In objCat.Tables
     ' has the table got indexes
     If objTbl.Indexes.Count > 0 Then
          Debug.Print objTbl.Name
     ' loop through the indexes
     For Each objIdx In objTbl.Indexes
               Debug.Print vbTab; objIdx.Name; vbTab;
                          objIdx.PrimaryKey
               'loop through the columns in the index
               For Each objCol In objIdx.Columns
                    Debug.Print vbTab; vbTab; objCol.Name
               Next 
          Next
     End If
Next

' clean up
Set objCol = Nothing
Set objTbl = Nothing
Set objIdx = Nothing
Set objCat = Nothing

This is from ADO Programmer's Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

Next: Language Differences >>
 

More ASP.NET Articles
More By Apress Publishing

Comments On: What is ADO?

>>> Be the FIRST to comment on this article!