Removing a Field from a Table

The procedure in Hands-On 11-5 illustrates how to access the ADOX Columns collection of a Table object and use the Columns collection Delete method to remove a field from a table. This procedure will fail if the field you want to delete is part of an index.

© Hands-On 11-5: Removing a Field from a Table

1. In the Visual Basic Editor window, choose Insert | Module.

2. In the module's Code window, type the Delete_Field procedure shown below.

3. Choose Run | Run Sub/UserForm to execute the procedure.

Sub Delete_Field()

Dim cat As New ADOX.Catalog

Set cat = New ADOX.Catalog cat.ActiveConnection = CurrentProject.Connection cat.Tables("tblFilters").Columns.Delete "Type" Set cat = Nothing End Sub

A run-time error will occur if you attempt to delete a field that is part of an index. To see this error in action, open the tblFilters table and set a primary key on the Id column. Next, in the Delete_Field procedure, replace the statement:

cat.Tables("tblFilters").Columns.Delete "Type"

with the following line of code:

cat.Tables("tblFilters").Columns.Delete "Id"

and rerun the procedure.

Because there is an index on the Id field in the tblFilters table, Visual Basic cannot delete this field and the run-time error appears as shown in Figure 11-3.

Rtrrtlme error -2147467239 (30004005)' :

Csrnot cfelete a field that is pfft of.5n nite: or is needed by die system-

Figure 11-3: Visual Basic displays a run-time error when you attempt to

Rtrrtlme error -2147467239 (30004005)' :

Csrnot cfelete a field that is pfft of.5n nite: or is needed by die system-

delete a field that is part of an index.

To ensure that the deletion goes smoothly, include error trapping code that deletes the index if necessary. You will learn how to work with indexes by using ADO in Chapter 12.

Part II

0 0

Post a comment