Home

Adding and Deleting Tables with ADO

This sample project demonstrates how to use ADO 2.1 data definition language to create a database, and then add, list, and delete tables.

Download a copy of the project.

Making the ADO DDL Project

  1. Open a new project in Visual Basic 6.0, and add a reference to the Microsoft ADO Extensions 2.1 for DDL and Security.
  2. Add four command buttons to the form. Set properties on the controls as listed in the following table.

    Object Property Value
    1st command button Name cmdCreate
    1st command button Caption Create a Database
    2nd command button Name cmdAdd
    2nd command button Caption Add Tables
    3rd command button Name cmdList
    3rd command button Caption List Tables
    4th command button Name cmdDelete
    4th command button Caption Delete a Table


  3. Copy the following code into the form.

    Option Explicit

    Sub CreateDB()
    'Creates a database at c:\new.mdb.
        Dim catNewDB As ADOX.Catalog

        'Create new Catalog variable.
        Set catNewDB = New ADOX.Catalog

        'Create a new catalog.
        catNewDB.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\new.mdb"

        Set catNewDB = Nothing

    End Sub

    Sub AddTables()
    'Creates two tables and adds them to c:\new.mdb.
        Dim catDB As ADOX.Catalog
        Dim tblNew As ADOX.Table

        'Create a new Catalog object and set its connection.
        Set catDB = New ADOX.Catalog
        catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\new.mdb"

        'Create a new Table object and add columns.
        Set tblNew = New ADOX.Table
        With tblNew
            .Name = "Equipment"
            With .Columns
                .Append "EquipmentID", adVarWChar
                .Append "Name", adVarWChar
                .Append "Manufacturer", adVarWChar
                .Append "Location", adVarWChar
            End With
        End With

        'Add the new table to the Tables collection.
        catDB.Tables.Append tblNew
        Set tblNew = Nothing

        'Do this all again with the second table.
        Set tblNew = New ADOX.Table
        With tblNew
            .Name = "WorkOrders"
            With .Columns
                .Append "WorkOrderID", adVarWChar
                .Append "Task", adVarWChar
                .Append "Priority", adVarWChar
                .Append "SchedDate", adVarWChar
            End With
        End With
        catDB.Tables.Append tblNew

        Set catDB = Nothing

    End Sub

    Sub ListTables()
    'List tables contained in c:\new.mdb.
        Dim catDB As ADOX.Catalog
        Dim tblList As ADOX.Table

        'Create a new Catalog object and set its connection.
        Set catDB = New ADOX.Catalog
        catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\new.mdb"

        'Write table information to the Immediate window.
        For Each tblList In catDB.Tables
            Debug.Print tblList.Name & vbTab & tblList.Type
        Next

        Set catDB = Nothing

    End Sub

    Sub DeleteTable()
    'Delete the WorkOrders table
        Dim catDB As ADOX.Catalog
        Dim tblTarget As ADOX.Table

        'Create a new Catalog object and set its connection.
        Set catDB = New ADOX.Catalog
        catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\new.mdb"

        'Remove table from the Tables collection.
        catDB.Tables.Delete "WorkOrders"

        Set catDB = Nothing

    End Sub

    Private Sub cmdAdd_Click()
    'Call the AddTables procedure
        Call AddTables
    End Sub

    Private Sub cmdCreate_Click()
    'Call the CreateDB procedure
        Call CreateDB
    End Sub

    Private Sub cmdDelete_Click()
    'Call the DeleteTable procedure
        Call DeleteTable
    End Sub

    Private Sub cmdList_Click()
    'Call the ListTables procedure
        Call ListTables
    End Sub
  4. Save the project, then run it.
Last changed 9/15/00