Microsoft Access VBA Programming for the Absolute Beginner

Deletes a database object.

Syntax

DoCmd.DeleteObject([ObjectType][, ObjectName])

with the following parameters:

ObjectType

An AcObjectType constant indicating the type of object to delete. Possible values are acDataAccessPage, acDefault (the object selected in the Database window), acDiagram, acForm, acFunction, acMacro, acModule (a VBA module), acQuery, acReport, acServerView, acStoredProcedure, and acTable.

ObjectName

The name of the object of type ObjectType to be deleted.

Example

The example cycles through backup tables (tables that include “Backup” in the filename), prompts the user whether each should be deleted, and deletes the table if the user responds affirmatively.

Public Sub DeleteBackupTables() Dim tbl As Variant ' Iterate tables For Each tbl In CurrentData.AllTables If InStr(1, tbl.Name, "Backup", vbTextCompare) > 0 Then If vbYes = MsgBox("Delete table " & tbl.Name & "?", _ vbYesNoCancel Or vbQuestion, _ "Confirm Table Deletion") Then DoCmd.DeleteObject acTable, tbl.Name End If End If Next End Sub

Comments

• If no object type or object name is specified, Access deletes the object selected in the Database window. This can be controlled programmatically with the DoCmd.SelectObject method.

• If ObjectName does not exist, Access generates runtime error 7874.