Solved

Code to convert multiple linked tables into nonlinked copies

Posted on 2007-04-06
12
407 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

Certified OpenStack Administrator Course

We just refreshed our COA course based on the Newton exam.  With 14 labs, this course goes over the different OpenStack services that are part of the certification: Dashboard, Identity Service, Image Service, Networking, Compute, Object Storage, Block Storage, and Orchestration.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

617 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