WORKING WITH TABLES USING ADO

Working with tables in ADO is similar to working with them in DAO. Instead of working with the TableDefs collection, you will be working with the Tables collection.

Looking at the Tables Collection

The Tables collection is located off the Catalog object. One difference between ADO's Tables collection and DAO's TableDefs collection is that more than just standard table and link table objects are stored in it. Table 6.4 lists the object types in the ADO Tables collection.

You can use a couple of methods to see what objects are in the Tables collection for a specific type. You can use the traditional DAO method by using the Tables collection and looking at the Table object's Type property. You can see this method used in Listing 6.6.

Listing 6.6. Chap06.mdb: Looking at the Table Type Object by Using the Tables Collection

Sub ListTablesUsingTableCollection(strTypeToList As String)

        Dim catCurr     As New ADOX.Catalog
        Dim tblCurr     As ADOX.Table

        catCurr.ActiveConnection = CurrentProject.Connection
        For Each tblCurr In catCurr.Tables

            If tblCurr.Type = strTypeToList Then

                Debug.Print tblCurr.Name

            End If

        Next

End Sub

The other method available to ADO is using the OpenSchema method off the Connection object. This method is actually faster because you don't have to create the individual Table variables.

Listing 6.7. Chap06.mdb: Using the OpenSchema Method to See What Object Types Are in the Tables Collection

Sub ListTablesUsingSchema(strTypeToList As String)

    Dim rstSchema     As ADODB.Recordset

    Set rstSchema = CurrentProject.Connection.OpenSchema(adSchemaTables)

    Do Until rstSchema.EOF

        If rstSchema.Fields("TABLE_TYPE") = strTypeToList _
           Or strTypeToList = "ALL" Then _

                Debug.Print rstSchema.Fields("TABLE_NAME")

        End If

        rstSchema.MoveNext

    Loop

End Sub

Creating a New Table with Fields and Indexes

Creating tables in ADO is very much like creating them in DAO, where you append objects to the various necessary collections. Unlike DAO, you don't have to use the CreateObject() methods. Another difference is the use of the Keys collection, which is different from the Indexes collection in that it specifies a table's primary and foreign keys. Listing 6.8 shows the creation of a table, along with fields and the primary key field.

Listing 6.8. Chap06.mdb: Creating a Table with Fields and Keys

Sub CreateTableWIndexes()

    Dim catCurr       As New ADOX.Catalog
    Dim tblNew       As New ADOX.Table
    Dim keyNew      As New ADOX.Key

    catCurr.ActiveConnection = CurrentProject.Connection

    ' Create a new Table object.
    With tblNew
        .Name = "MyNewTable"

        '-- Add new columns

        '- Add an autoincrement field
        .Columns.Append "MyKeyField", adInteger
        .Columns("MyKeyField").ParentCatalog = catCurr
        .Columns("MyKeyField").Properties("AutoIncrement") = True

        '-- Add a character field
        .Columns.Append "MyCharField", adWChar

        '-- Add a Memo field
        .Columns.Append "MyMemoField", adLongVarWChar
        .Columns("MyMemoField").Attributes = adColNullable

        '-- Add a Primary Key field
        keyNew.Name = "PrimaryKey"
        keyNew.Columns.Append "MyKeyField"

        .Keys.Append keyNew, adKeyPrimary

    End With

    ' Add the new table to the database.
    catCurr.Tables.Append tblNew

    Set catCurr = Nothing

End Sub

Look at Listing 6.8 step by step:

catCurr.ActiveConnection = CurrentProject.Connection

' Create a new Table object.
With tblNew
    .Name = "MyNewTable"

'- Add an autoincrement field
.Columns.Append "MyKeyField", adInteger
.Columns("MyKeyField").ParentCatalog = catCurr
.Columns("MyKeyField").Properties("AutoIncrement") = True

'-- Add a character field
.Columns.Append "MyCharField", adWChar

'-- Add a Memo field
.Columns.Append "MyMemoField", adLongVarWChar
.Columns("MyMemoField").Attributes = adColNullable

'-- Add a Primary Key field
keyNew.Name = "PrimaryKey"
keyNew.Columns.Append "MyKeyField"

.Keys.Append keyNew, adKeyPrimary

' Add the new table to the database.
catCurr.Tables.Append tblNew

Now look at how easy it is to modify the table just created and to add an index.

Modifying an Existing Table by Adding an Index

To modify the table, you will just refer to it in the Tables collection. To add an index, you will create the new index and add it to the Indexes collection.

Sub ModifyTableAndAddIndex()

    Dim catCurr       As New ADOX.Catalog
    Dim idxNew      As New ADOX.Index

    catCurr.ActiveConnection = CurrentProject.Connection

    With catCurr.Tables("MyNewTable")

        idxNew.Name = "MyCharField"
        idxNew.Columns.Append "MyCharField"

        .Indexes.Append idxNew
     End With

End Sub

These are just a few of the many things you can do by using ADO version 2.1, so get playing with them, and explore all the many other collections and objects in ADO.