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