Deleting a Column from a Table

Use the DROP COLUMN clause in the ALTER TABLE statement to delete a column from a table. You only need to specify the name of the field you want to remove. The example procedure in Hands-On 19-9 deletes the Budget2004 column from the tblSchools table.

©Hands-On 19-9: Deleting a Field from a Table

This hands-on uses the tblSchools table created in Hands-On 19-2. Make sure this table contains the Budget2004 column, which was added in Hands-On 19-6.

1. In the Visual Basic Editor window, insert a new module.

2. In the module's Code window, enter the DeleteField procedure as shown below.

Sub DeleteField()

Dim conn As ADODB.Connection Dim strTable As String Dim strCol As String

On Error GoTo ErrorHandler

Set conn = CurrentProject.Connection strTable = "tblSchools" strCol = "Budget2004"

conn.Execute "ALTER TABLE " & strTable & _

" DROP COLUMN " & strCol & ";" ExitHere:

conn.Close Set conn = Nothing Exit Sub ErrorHandler:

MsgBox Err.Number & ":" & Err.Description Resume ExitHere End Sub

Part III

0 0

Post a comment