Solved

Code to convert multiple linked tables into nonlinked copies

Posted on 2007-04-06
12
379 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 119

Accepted Solution

by:
Rey Obrero 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
 

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
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.

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

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

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

861 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

Need Help in Real-Time?

Connect with top rated Experts

30 Experts available now in Live!

Get 1:1 Help Now