Deleting Tables

Use the DROP TABLE statement to delete an existing table from a database. Note that a table must be closed before it can be deleted. The procedure in Hands-On 19-4 demonstrates how to use the DROP TABLE statement inside a VBA procedure. We will delete the tblSchool table that was created in Hands-On 19-2.

©Hands-On 19-4: Deleting a Table

This hands-on requires the prior completion of Hands-On 19-2.

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

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

Sub DeleteTable()

Dim conn As ADODB.Connection Dim strTable As String

On Error GoTo ErrorHandler

Set conn = CurrentProject.Connection strTable = "tblSchools" conn.Execute "DROP TABLE " & strTable Application.RefreshDatabaseWindow ExitHere:

conn.Close Set conn = Nothing Exit Sub ErrorHandler:

If Err.Number = -2147217900 Then

DoCmd.Close acTable, strTable, acSavePrompt Resume 0

Else

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

You can also execute the DROP TABLE statement directly in the Microsoft Office Access user interface's Data Definition Query window by following these steps:

■ Click the Close button in the Show Table dialog box.

From the Query menu, choose SQL Specific | Data Definition.

■ Enter the following statement in the query window:

Drop Table tblSchools;

■ Run the query by selecting Run from the Query menu.

Part III

0 0

Post a comment