Delete a field from a table using ADO
-
Download the
.mdb sample file (68,765 bytes) -
Download the .accdb sample file (71,432 bytes) - If you can’t create the database,
download this one (8558
bytes)
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:
- 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. - 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:
- Create a new ADO connection
- Create an ADOX Catalog (database) and make it use the ADO
connection that you created in Step 1 - Set a reference to the table that you want to change
- Delete column(s) from the table, by deleting them from the
table’s Columns collection - 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:
- Create a new ADO connection
- Create a Command object, and make it use the new ADO
connection - Execute an SQL statement that deletes the field, using the
DROP clause. - 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
