ACC2: Cannot Delete Attached Table with Relationship

MORE INFORMATION

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Building Applications" manual.

The following example demonstrates how to create and use an Access Basic procedure to delete an attached table.

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

1. Create a new module and enter the following line in the Declarations section:
   

         Option Explicit
   						

2. Create the following procedure in the module:
   

         Function DeleteAttachment()
   
         Dim MyDB As Database, TableName As String, I As Integer
         On Error GoTo DeleteErr
         Set MyDB = DBEngine.WorkSpaces(0).Databases(0)
   
         ' Prompt for the name of the attached table.
         TableName = InputBox$("Enter the name of the attached_
              table you want to delete.")
   
         ' First, determine if the table is an attached table. If so,
         ' examine all relationships in the database.
         If Not ((MyDB.TableDefs(TableName).Attributes And_
              DB_ATTACHEDTABLE) = 0) Then
   
               For I = 0 To MyDB.Relations.Count - 1
                   ' If the attached table is part of the current
                   ' relationship, delete the relationship.
                   If (MyDB.Relations(I).Table = TableName) Or_
                      (MyDB.Relations(I).ForeignTable = TableName) Then
                       MyDB.Relations.Delete MyDB.Relations(I).Name
                   End If
               Next I
   
             ' After all relationships involving this table have been
             ' deleted, delete the attached table.
             MyDB.TableDefs.Delete TableName
         End If
   
         DeleteExit:
             Exit Function
   
         DeleteErr:
              If Err = 3265 Then
                  MsgBox "There is no table named " & TableName
              Else
                  MsgBox "An unexpected error (" & Err & ") occurred: "_
                      & Error(Err)
              End If
              Resume DeleteExit
   
          End Function
   						

3. From the View menu, choose Immediate Window.
4. In the Immediate window, type the following line, and then press ENTER:
   
   ? DeleteAttachment()

NOTE: After you delete a table using this method, you may need to refresh the Database window. To do so, choose the Query button in the Database window, and then choose the Table button.