Convert Linked Tables to local

[Jaye7]

I have the following script that either converts a single linked table to a local table or ALL linked tables to local tables, however I am getting the following 2 error messages on several tables for the ALL tables script.

error 3709, the search key was not found in any record

Error 3300, cannot create a relationship.

Sub Convert_Linked_Tables_To_Local()

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb

GoTo Multi1

'================================================================================
'INDIVIDUAL TABLE
Dim SingleTable As String

'THE FOLLOWING LINE IS THE ONLY LINE THAT YOU NEED TO CHANGE FOR A SINGLE TABLE

SingleTable = "CS - Agent abbreviations"

For Each tdf In dbs.TableDefs
    ' If the table has a connect string, it's a linked table.
    If Len(tdf.Connect) > 0 Then
    If tdf.Name = SingleTable Then
        'Debug.Print tdf.Connect
        DoCmd.SelectObject acTable, SingleTable, True
        RunCommand acCmdConvertLinkedTableToLocal
    End If
    End If
Next tdf

'COMMENT OUT THE FOLLOWING LINE TO USE FOR ALL LINKED TABLES
GoTo END1
'================================================================================

Multi1:
'ALL LINKED TABLES TO LOCAL TABLES
Dim MultiTable As String

For Each tdf In dbs.TableDefs
MultiTable = tdf.Name

    ' If the table has a connect string, it's a linked table.
    If Len(tdf.Connect) > 0 Then
        'Debug.Print tdf.Connect
     DoCmd.SelectObject acTable, MultiTable, True
     On Error GoTo ERR1
     RunCommand acCmdConvertLinkedTableToLocal
    End If
Next tdf

END1:
Exit Sub

ERR1:
Debug.Print "Error Number: " & Err.Number & " Table that Failed to convert: " & tdf.Name
Resume Next
End Sub

 

[Guus2005]

It seems to me that relational integrity is preventing you to convert the table to local.

You have to follow a certain sequence e.g. if you want to convert the table "orderitem", make sure that the table "order" is already present. And for "order" you probably need "customer" e.t.c.

Or perhaps it is best to drop the relationships altogether convert the tables and rebuild all the relationships again.

HTH

 

[Jaye7]

Thanks for replying.

I am looking for code to do this though so that it is automated.

The relationships are causing a problem, maybe I need a script to simply make copies of the linked tables and then delete the linked tables.
But possibly I will still have the same problem when deleting linked tables.

 

[The_Doc_Man]

If RI is enabled, one of those deletes isn't going to work correctly.

Seems to me that you could do an export of the back-end table to your database (which does not delete the source), then do the same for the other tables. After that, you might have to manually unlink the tables, rename them, and then regenerate the relationships. (Might not, either, but I've never tried to automate that particular step.)

 

[Jaye7]

Thanks for replying.

I don't like having to do anything manually, I would write code for my car to drive me to work if I could as I don't see the point of having to perform meaningless tasks if there is a smarter way.

 

[gemma-the-husky]

I think that error message probably indicates corruption, which would affect the copying of tables.
Every time I have seen that message there has been a corrupt record, Chinese characters etc.

just try and manually copy the table within the backend - you will probably get the same issue. You will need to try and resolve this. Do a windows copy of the backend before you try anything.