DataWright Information Services

Consulting and Resources for Excel and Access




Delete a field from a table using ADO

This is Part 7 of 7 in a tutorial on using code to integrate
Excel and Access.

In Part 3 of this tutorial, I demonstrated how to add a new field
to an existing table. In this final section we will see two ways to
delete a field, using either ADOX or SQL.

In the sample file, New Field has a button for
removing a field. Pressing it will delete the field Region_2 that
was created by clicking Insert Field_SQL on the
same sheet. If you haven’t created the field previously, do it now
or you will get an error message when you try to delete the
non-existent field.

Note:

  1. Because all of the code for this tutorial exists in a single
    module, I have placed a
    constant at the top of the module that
    defines the name of the database. This removes the need to keep
    redefining it for each procedure, and means that you only need
    to change one place in the code to refer to a different
    database.
  2. You will need to
    set a reference
    to the Microsoft ActiveX Data Objects 2.x Library to run this
    code. If you use the ADOX method for creating the field, you
    will also need to reference the ADO Extension Library, as noted
    in the link above.

The constant is shown below. It needs to go before the first Sub
procedure in the module:

Const TARGET_DB = "DB_test1.mdb"

Delete the field

Delete a field using ADOX

As we have seen in part
3 of the tutorial
, ADOX is an extension of ADO that lets us
alter database and table structures.

To delete a field using ADOX you need to do 5 things:

  1. Create a new ADO connection
  2. Create an ADOX Catalog (database) and make it use the ADO
    connection that you created in Step 1
  3. Set a reference to the table that you want to change
  4. Delete column(s) from the table, by deleting them from the
    table’s Columns collection
  5. Clean up the references

Note: To successfully run this code, the field must exist in the
table first. If you haven’t run the Insert Field_SQL
button on the New Field sheet, do so before running
either of these procedures.

The code is shown below:

Sub DeleteAField_ADOX()

   Dim cnn As ADODB.Connection
   Dim MyConn
   Dim cat As ADOX.Catalog
   Dim col As ADOX.Column
   Dim tbl As ADOX.Table

   MyConn = ActiveWorkbook.Path & Application.PathSeparator & TARGET_DB

   'connect to the database
   Set cnn = New ADODB.Connection
   With cnn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Open MyConn
   End With
   Set cat = New ADOX.Catalog
   cat.ActiveConnection = cnn

   'delete the column
   Set tbl = cat.Tables("tblPopulation")
   tbl.Columns.Delete "Region_2"

   'clean up references
   Set cat = Nothing
   Set col = Nothing
   cnn.Close
   Set cnn = Nothing
End Sub

An alternative, and the method used in the sample file, is to
delete the field using the SQL DDL language.

Delete the field using SQL

To delete a field using SQL you need to do the following:

  1. Create a new ADO connection
  2. Create a Command object, and make it use the new ADO
    connection
  3. Execute an SQL statement that deletes the field, using the
    DROP clause.
  4. Clean up references.

The code is shown below:

Sub DeleteAField()

   Dim cnn As ADODB.Connection
   Dim cmd As ADODB.Command
   Dim MyConn

   Set cnn = New ADODB.Connection
   MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB

   'open the connection, and make the Command object use the connection
   Set cnn = New ADODB.Connection
   With cnn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Open MyConn
   End With

   Set cmd = New ADODB.Command
   Set cmd.ActiveConnection = cnn

   'delete the field
   cmd.CommandText = "ALTER TABLE tblPopulation DROP Column Region_2"
   cmd.Execute

   'clean up references
   Set cmd = Nothing
   cnn.Close
   Set cnn = Nothing
End Sub

<<Previous:
Part 6