Solved

Code to convert multiple linked tables into nonlinked copies

Posted on 2007-04-06
12
404 Views
Last Modified: 2008-02-01
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.    

0
Comment
Question by:ginafred
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
12 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 18864466
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 18864490
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 18864814
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ginafred
ID: 18864860
Thank you for the suggestions!  I will try these methods today and will post my results this afternoon.

Gina
0
 

Author Comment

by:ginafred
ID: 18865134
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 18865155
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18865157
you need to add to your references
Microsoft Dao x.x Object Library
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 18865163
ginafred, in vba window., go to tools/references and ensure you have added Microsoft DAO Object Library - version as specified by jerry
0
 

Author Comment

by:ginafred
ID: 18866537
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 18866570
I am confused on what didn't work, but it wouldn't have done anything for your back-end tables anyway.
0
 

Author Comment

by:ginafred
ID: 18867615
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18867620
it could be that the type should be 4 not 6
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question