Solved

Code to convert multiple linked tables into nonlinked copies

Posted on 2007-04-06
12
388 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
  • 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

831 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