ACC1x: Function to Delete Table Without Using DoMenuItem

• Microsoft Access versions 1.0, 1.1

SUMMARY

You can create and use the DeleteTable() function to delete a table without using the DoMenuItem() or SendKeys() functions.

MORE INFORMATION

Microsoft Access stores all references to objects (tables, queries, forms, reports, macros, and modules) in a system table called MSysObjects. You can delete an object from the MSysObjects table if you have permission to do so.

To view the system objects in your database,

1. From the View menu, choose Options.

2. Under the General category, change the Show System Objects item to Yes.

3. Choose OK.

Next, use the following steps to assign yourself permission to delete objects from the MSysObjects table:

1. From the Security menu, choose Permissions.

2. Choose modify rights for the table by selecting or clearing the appropriate check boxes.

NOTE: Modifying data in any system table can have serious ramifications. Do not attempt to do this unless you are thoroughly familiar with the database and know exactly where, when, and how the table you want to delete is used elsewhere in the database. It is best to use the DeleteTable() function on temporary tables only.

The Access Basic code for the DeleteTable() function is as follows:

NOTE: In the following sample code, an underscore (_) is used as a line- continuation character. Remove the underscore when re-creating this code in Access Basic.

   Option Explicit
   Function DeleteTable (TableName as String)

      Dim SQL As String, Msg As String

      If DCount("[Name]", "MSysObjects", "[Name] = '" _
            & TableName & "'") = 0 Then
         DeleteTable = FALSE
         Exit Function
      End If

      On Error GoTo DeleteErrorProc

   ' Suppress the "Do you want to delete these records?" message.

      DoCmd SetWarnings False

   ' SQL statement to delete appropriate entries from the MSysColumns
   ' table follows:

      SQL = "DELETE DISTINCTROW MSysObjects.Name, MSysColumns.*"
      SQL = SQL & " FROM MsysObjects, MSysColumns,"
      SQL = SQL & " MSysColumns INNER JOIN MSysObjects ON"
      SQL = SQL & " MSysColumns.ObjectId = MSysObjects.Id"
      SQL = SQL & " WHERE ((MSysObjects.Name = '" & TableName & "'));"

      DoCmd RunSQL SQL

   ' SQL Statement to delete appropriate entries from MSysACEs table
   ' follows:

      SQL = "DELETE DISTINCTROW MSysObjects.Name, MSysACEs.*"
      SQL = SQL & " FROM MSysObjects, MSysACEs,"
      SQL = SQL & " MSysObjects INNER JOIN MSysACEs ON"
      SQL = SQL & " MSysObjects.Id = MSysACEs.ObjectID"
      SQL = SQL & " WHERE ((MSysObjects.Name= '" & TableName & "'));"

      DoCmd RunSQL SQL

   ' SQL statement to delete references to indexes in MSysIndexes follows:

      SQL = "DELETE DISTINCTROW MSysObjects.Name, MSysIndexes.*"
      SQL = SQL & " FROM MSysObjects, MSysIndexes,"
      SQL = SQL & " MSysObjects INNER JOIN MSysIndexes ON"
      SQL = SQL & " MSysObjects.Id = MSysIndexes.ObjectId"
      SQL = SQL & " WHERE ((MSysObjects.Name= '" & TableName & "'));"

      DoCmd RunSQL SQL

   ' SQL statement to delete references to table object from MSysObjects
   ' follows:

      SQL = "DELETE DISTINCTROW MSysObjects.Name "
      SQL = SQL & " FROM MSysObjects"
      SQL = SQL & " WHERE ((MSysObjects.Name = '" & TableName & "'));"

      DoCmd RunSQL SQL

   ' If you decide to update the Database window, include the following
   ' command:

      SendKeys "{F11} %VQ%VT"'

   ' Test to make sure the object was deleted, then exit.

      If DCount("[Name], "MSysObjects", "[Name] = '" _
            & TableName & "'") = 0 Then
         DeleteTable = True
         Exit Function
      EndIf

   ' If error occurs, display the error message and terminate with error.
   DeleteErrorProc:
      Msg = "An unexpected error has occurred." & Chr(13) & Chr(10)
      Msg = Msg & "Error: " & Error$
         MsgBox Msg
      DeleteTable = False
      Exit Function
   End Function

REFERENCES

Microsoft Access "User's Guide," version 1.0, chapter 25