Code to convert multiple linked tables into nonlinked copies

I've got about 70 tables linked to our main db.  I want to copy those linked tables into regular nonlinked tables (including structure and data).  Simple make table queries work just fine, but the number of tables involved makes this a tedious prospect.  I am using Access 2003.

The naming convention is dbo_TableName for the linked tables and 0_TableName for the nonlinked table.
For example, the code for a single table is :  
SELECT dbo_tblCoursesArchive.* INTO 0_tblCoursesArchive FROM dbo_tblCoursesArchive;

I would like some code that would loop through each linked table and make a nonlinked table.    

ginafredInstructional DesignerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rockiroadsCommented:
U could try a bit of vba
here Im assuming all linked tables are to a backend access database only, not linked to excel or anything like that



Public Sub CopyLinkedTables()

    Dim tbl As DAO.TableDef
    Dim sTblName As String
    Dim sSrcName As String
    Dim sFile As String
    Dim sInfo() As String
   
   
    On Error Resume Next
   
    For Each tbl In CurrentDb.TableDefs

        'Go thru your list of tables avoiding system and hidden ones        
        If Left$(tbl.Name, 4) <> "MSys" And Left$(tbl.Name, 1) <> "~" Then
       
            'If it has a connect string then its linked
            If InStr(tbl.connect, "DATABASE=") > 0 Then

                'get the name of the original table in the backend            
                sSrcName = tbl.SourceTableName
           
                'Get the namedatabase name
                sInfo = Split(tbl.connect, "DATABASE=")
                sFile = sInfo(1)
               
                Debug.Print "Importing Table", sSrcName, "from", sFile

                DoCmd.TransferDatabase acImport, "Microsoft Access", sFile, acTable, sSrcTable, "O_" & sSrcTable
            End If
        End If
    Next
End Sub

0
jerryb30Commented:
dim rs as recordset
dim strsql as string
dim strsql2 as string
dim strtable as string
strsql = "select name from msysobjects where type = 6 and name not like '~*'"
set rs = currentdb.openrecordset(strsql)
rs.moveFirst
do while not rs.eof
strtable = "0" & mid(rs!name, instr(rs!Name, "_"))
strsql2 = "select * into " & strtable & " from " & rs!name
docmd.runsql(strsql2)
rs.movenext
loop
0
Rey Obrero (Capricorn1)Commented:
is the backend from an SQL server

try this too
Dim rs As DAO.Recordset
Dim sSql As String
Dim iSql As String
Dim sTable As String
sSql = "select [Name],Connect,type"
sSql = sSql & " from msysobjects"
sSql = sSql & " where left(msysobjects.Name,1)<>'~'"
sSql = sSql & " and msysobjects.Connect Is Not Null"
sSql = sSql & " And type=4"

Set rs = CurrentDb.OpenRecordset(sSql)
rs.MoveFirst
Do While Not rs.EOF
    sTable = "0" & Mid(rs!Name, InStr(rs!Name, "_"))
    iSql = "select * into " & sTable & " from " & rs!Name
    CurrentDb.Execute iSql, dbFailOnError
    rs.MoveNext
Loop

End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ginafredInstructional DesignerAuthor Commented:
Thank you for the suggestions!  I will try these methods today and will post my results this afternoon.

Gina
0
ginafredInstructional DesignerAuthor Commented:
I've tried all three with the following results:

rockiroads:
Got a compile error (user-defined type not defined) on this line:
    Dim tbl As DAO.TableDef

jerryb30:
Got a type mismatch on this line:
    Set rs = CurrentDb.OpenRecordset(strsql)

capricorn1:
Got a compile error (user-defined type not defined) on this line:
    Dim rs As DAO.Recordset

Adjustments?  Suggestions?

Thanks!
0
jerryb30Commented:
I am guessing you do not have DAO 3.6 library selected as a reference for 1 & 3.

Is this indeed a SQL back-end?
0
Rey Obrero (Capricorn1)Commented:
you need to add to your references
Microsoft Dao x.x Object Library
0
rockiroadsCommented:
ginafred, in vba window., go to tools/references and ensure you have added Microsoft DAO Object Library - version as specified by jerry
0
ginafredInstructional DesignerAuthor Commented:
It is a SQL backend, as far as my source tells me (the data environment here is challenging and in disarray).  On a second round of tries, after adding the DAO reference:

rockiroads:  No error but no action either.  
jerryb30:  Still got a type mismatch on this line:
    Set rs = CurrentDb.OpenRecordset(strsql)
capricorn1:  It worked!
0
jerryb30Commented:
I am confused on what didn't work, but it wouldn't have done anything for your back-end tables anyway.
0
ginafredInstructional DesignerAuthor Commented:
I am confused about it, too.  Seems like it should have worked like a charm.  I am going to play around with it some more, and if I find anything of interest, I will let you know.  Thank you very much for your help -- sorry I did not split the points a bit; I was in a rush and didn't choose the right option.
0
Rey Obrero (Capricorn1)Commented:
it could be that the type should be 4 not 6
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.