Relationships Preventing Delete on Linked Table

[Cotswold]

Hi, I have created a database in Acces2010, which is split Frontend & Backend. I have code that will disconnect
the linked tables and re-link them. I have this to allow the backend to be moved to another location if required.
Basicaly the linked tables are removed, the new path is located and the tables are re-attched. This has never been
a problem before but in this new database when the code runs it fails because some tables are related.
I have software developed in Access 2000 that I have amended to run in Access2010 and they work just fine. Some
with over 20 linked tables. After tables are removed and re-linked all of the relationships remain intact.

The new developed program will always fail due to Error Nº 2387
ErrMsg = "You can't delete the table "TableName" it is participating in one or more relationships"
I use this to remove the linked Table
DoCmd.DeleteObject acTable, "TableName"

I then normally re-link with the next line of code but use variables for the items in ""
DoCmd.TransferDatabase AcLink, "Microsoft Access", "PathAndBackendDatabase", acTable, "TableName", "TableName", 0

I have looked through OPTIONS but cannot see a switch. Document deletions are off. If the conversions from A2000 are Ok
I cannot see why the on in A2010 isn't. I know I can delete and add relationships but I don't really want to do that because I
can't check if they fail to create.
Maybe someone else has come across this before? I'll be upgrading to Access2019 soon but can't see that will fix the issue.

Regards,Cotswold

 

[arnelgp]

how about if you just Relink it using VBA:

Public Sub RelinkTable(ByVal DbPath As String, ByVal TableName As String, Optional ByVal LinkName As String)

    Dim db As DAO.Database
    Dim td As DAO.TableDef
   
    If Len(LinkName) < 1 Then
        LinkName = TableName
    End If
   
    Set db = CurrentDb
    Set td = Nothing
    If DCount("1", "MsysObjects", "Name = '" & LinkName & "' And Type = 6") > 0 Then
        db.TableDefs.Delete LinkName
    End If
    Set td = db.CreateTableDef(LinkName)
    td.SourceTableName = TableName
    td.Connect = ";DATABASE=" & DbPath
    db.TableDefs.Append td
   
    db.TableDefs.Refresh
    Application.RefreshDatabaseWindow
    Set td = Nothing
    Set db = Nothing
End Sub

RelinkTable "PathAndBackendDatabase", "TheTableToLink", "TheLinkName"

 

[Pat Hartman]

I agree, you don't need to delete the tables to relink them.

Although local relationships are just for documentation, perhaps they might interfere with deleting the linked table. Check the relationship window in the FE to see if you have one.

 

[isladogs]

I use DSN-less connections and ALWAYS break connections before relinking is done.
I've done this for 20+ years to handle the situation described by the OP. All versions of Access from 2000 through to 365.

Before distributing updates to clients via my website, I remove all links to the BE
The various client sysadmins download the files then run the relink to match their own network settings .. .
.... though the whole process is effectively automated with just one button click at startup.

It should still work for the OP

 

[Cotswold]

@isladogs
I am the same since Access97. Having updated a program developed on my PC, I'd issue it for customers to
install on a network, so always a different location. It would open on theirs, delete all the links, lookup
in a local system file for the new path and re-connect on their network. Never a problem, or error with
relationships or anything else.
All earlier programs converted from earlier access to 2010 and .accdb are fine. It has been a new
development that the issue arose. So I can only think that the conversion from A2000 to A2010 retains some
compatibilty from earlier versions because all conversions will run TransferDatabase and DeleteObject where
the object is a table, without error. A2010 simply refuses to use DoCmd.TransferDatabase, in a development
created from start in A2010. It suggests it is replaced by ImportExportData but I do not want to use Macros,
in my applications, only VBA code. (except for Autoexec of course) DoCmd.DeleteObject still falls over and
refuses to remove a linked table with a relatonship in A2010. Which is as I say fine in a conversion from
A2000 to A2010.

I'll get a copy of A2019 in the next day or so and upgrade the software to that. I've been a somewhat remiss
in leaving it for so long. Mind it is only in the last few years that my last of my Access97 software was
replaced. A couple of them still running on WindowsXP! Access is really quite stable and gets bad press from
time to time.
Just like to say that @arnelgp's code runs just fine and replaces TransferDatabase code effectively, if a table
is already linked or not. It also avoids the need to remove the linked tables before relinking.

Happy New Year to one & all.

 

[isladogs]

Glad you have a solution...

Not sure why you're having issues removing a linked table with a relationship. All linked table relationships should only be in the BE.

Arnel's relinking code is basically the same as I use though mine also includes code to show progress on the relink form and other related items.
I never use DoCmd.TransferDatabase nor ImportExportData.
The 2010 era IMEX routines are XML based and normally done using a wizard. No macros involved at any stage in this.