Turn Linked Tables into Local Tables?

[LaZyNala]

Hi everyone

I vaguely remember reading a post on this forum regarding this, but I haven't had any luck finding it.

I need to copy all linked tables and paste them as local tables, then delete the linked tables. I need to also rename the copied tables if they've been renamed with the "1" after the file name due to duplication.

The reason I ask, I have a large number of linked tables in a shared network database. Occasionally, I need to create a copy of the FE that isn't linked but a stand alone copy for demonstration purposes (speed). I also provide this copy to corporate for review. In that case, the networked version isn't an option.

Considering how time consuming it is to manually copy over and rename all the tables, I would love to have some help with adding automation to the process.

Thanks in advance for any tips,
Jen

 

[giacomo]

This code worked for me on an access linked table, however it should work fine regardless of the source. Another way to go might be to use docmd.transferDatabase not sure which is faster. The advantage of using sql is that you could limit the number of records copied on a huge table.

Sub copyAllLinkedTables()
Dim strLinkedTable As String
Dim strNewTable As String

For Each t In CurrentDb.TableDefs
    
    'Assumes all linked tables are prefixed with "lnk"
    If t.Name Like "lnk*" Then
        strLinkedTable = t.Name
        strNewTable = "Copy_" & t.Name
        
        'Copy data into a new table
        DoCmd.RunSQL "SELECT [" & strLinkedTable & "].* INTO [" & strNewTable & "] FROM [" & strLinkedTable & "];"
        'Delete the old table
        DoCmd.DeleteObject acTable, strLinkedTable
        'rename the new table
        DoCmd.Rename strLinkedTable, acTable, strNewTable
    End If
Next

End Sub

hth,
Giacomo

 

[LaZyNala]

Tyvm!

I'll try this out tomorrow morning when I get back to work and let you know how it goes

Jen

 

[LaZyNala]

I tried this and I'm receiving Run-time Error 3001: Invalid Argument.

What is so strange, it worked great for the first 4 tables.

When I debug, this is the line it hits the error on:

DoCmd.RunSQL "SELECT [" & strLinkedTable & "].* INTO [" & strNewTable & "] FROM [" & strLinkedTable & "];"

I have the code inserted into a module that I manually click on to run. I don't know if that may be where I'm having the problem?

Any help is appreciated. Thanks!
Jen

 

[LaZyNala]

Update (SOLVED):

Why this happened I don't know. I'll have to investigate. But the problem was with 1 table. Each time it hit that table, it errored out. By going through and changing the "lnk*" criteria to match all tables but that one it went through perfectly.

I can live with manually doing 1 table until I figure out what's causing the problem there.

Thanks so much for the solution giacomo!!

 

[giacomo]

what was the name of the one table? You can change the if to say t.name like "lnk*" and t.name not like "the name of the table giving you problems" so that way you don't have to do them one by one...

 

[LaZyNala]

The problem was a corrupted record inside of the problem table. Once I made that discovery and fixed it, I ran it again on another linked copy of the db. Worked perfect.

Thanks so much!

 

[giacomo]

No problem! Glad you were able to figure it out.

Giacomo