Microsoft KB Archive/119481

= ACC2: Cannot Delete Attached Table with Relationship =

Article ID: 119481

Article Last Modified on 11/6/2000

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q119481



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

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

     Option Explicit   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  From the View menu, choose Immediate Window. 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.

Keywords: kberrmsg kbprb kb3rdparty KB119481

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.