ACC2: Cannot Delete Attached Table with Relationship

• Microsoft Access version 2.0

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

If you try to delete an attached dBASE, FoxPro, Paradox, or Btrieve table, you may receive the following error message even if no relationship appears to exist between the attached table and any other tables in your database:

   Can't delete table <tablename>. It is participating in one or more
   relationships.

CAUSE

At one time, a non-enforceable relationship was established between the attached table and another table in your database. This attachment is no longer valid (for example, the file containing the attached table has been moved or deleted).

RESOLUTION

If the file containing the attached table was moved, use the Attachment Manager to refresh the relationship. After you refresh the relationship, remove the relationship in the Relationships window, and then delete the attached table.

If the file containing the attached table was deleted, you cannot refresh and then remove the relationship. Instead, you can create an Access Basic procedure to remove any relationship in which the attached table is participating, and then delete the attachment. The advantage of this approach is that you can delete the relationship even though no relationship appears in the Relationships window.

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()