Microsoft KB Archive/94042

{|
 * width="100%"|

ACC1x: Function to Delete Table Without Using DoMenuItem

 * }

Q94042

-

The information in this article applies to:


 * 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.

All the system tables should now be visible in the Database window.

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.

The DeleteTable function accepts the name of a table as its parameter. It finds the record in MSysObjects that corresponds to that table name and deletes that record. The function also deletes records from other system tables, including MSysACEs, MSysIndexes, and MSysColumns, that relate to the deleted table. The function returns a value of True if it successfully deletes the table you specified; it returns a value of False if it cannot find a table with the name you specified.

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(&quot;[Name]&quot;, &quot;MSysObjects&quot;, &quot;[Name] = '&quot; _           & TableName & &quot;'&quot;) = 0 Then DeleteTable = FALSE Exit Function End If

On Error GoTo DeleteErrorProc

' Suppress the &quot;Do you want to delete these records?&quot; message.

DoCmd SetWarnings False

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

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

DoCmd RunSQL SQL

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

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

DoCmd RunSQL SQL

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

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

DoCmd RunSQL SQL

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

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

DoCmd RunSQL SQL

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

SendKeys &quot;{F11} %VQ%VT&quot;'

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

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

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