Solved

Need to copy linked tables from SQL Server to local copy.

Posted on 2011-02-21
4
562 Views
Last Modified: 2012-05-11
I have links to SQL Server 2005 in my Access 2008 database.  But my reports are running really slow due to network issues.  So I need to make a local copy of all the tables I've linked, every night during my update process.  So, I've tried to re-create the code someone else posted on this site.  But, I'm getting the error 3170: Could not find installable ISAM.  And I don't know what else I might be getting wrong.  I'm kind of new to this stuff.  Could anyone help me?  Here's the code I've got so far.   I've added the word "Link_" to the beginning of each linked table so that the local copy can have the original name the rest of the code in the db is written for.  Thank you!

Public Sub test_Click()

    Dim dbs As Database
    Dim tdf As TableDef
    Dim strLinkedTableName As String
    Dim strSourceTableName As String
   
    On Error GoTo ErrHandler
   
    Set dbs = CurrentDb()
    For Each tdf In dbs.TableDefs
        If (tdf.Attributes And dbAttachedODBC) Then
'store the linked table name
            strLinkedTableName = tdf.name
'store the source table name
            strSourceTableName = Mid(strLinkedTableName, 6)
'import the source table
            DoCmd.TransferDatabase TransferType:=acImport, _
                                   DatabaseType:="ODBC Database", _
                                   Databasename:="DSN=ejacketprod_db;DATABASE=eJacketprod_db;UID=abc;PWD=xyz;", _
                                   ObjectType:=acTable, _
                                   Source:=strSourceTableName, _
                                   Destination:=strSourceTableName, _
                                   StructureOnly:=False, _
                                   Storelogin:=True
            On Error GoTo ErrHandler
        End If
    Next
     
ExitHandler:
    On Error Resume Next
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Sub
ErrHandler:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & ": " & Err.Description
    End Select
End Sub
 
0
Comment
Question by:sarahellis
[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
  • 2
4 Comments
 
LVL 19

Accepted Solution

by:
MINDSUPERB earned 250 total points
ID: 34943131
If you can view your link tables in the navigation pane, you can select all the link tables then copy and paste it back. Select Structure and Data and click OK.

This would be the quick and easiest way to do to go away with code errors.

Sincerely,
Ed
0
 

Author Comment

by:sarahellis
ID: 34943215
Unfortunately, this needs to happen automatically every night without user intervention.
Thanks!
0
 
LVL 40

Assisted Solution

by:als315
als315 earned 250 total points
ID: 34943740
As addition to Ed's advice (source tables are linked):
Dim SQL as String
For Each tdf In dbs.TableDefs
        If (tdf.Attributes And dbAttachedODBC) Then
'store the linked table name
            strLinkedTableName = tdf.name
'store the source table name
            strSourceTableName = Mid(strLinkedTableName, 6)
'import the source table
           SQL = "SELECT " & strSourceTableName & ".* INTO " & strLinkedTableName &" FROM " & strSourceTableName &";"
           docmd.openquery SQL
            On Error GoTo ErrHandler
        End If
    Next
0
 

Author Comment

by:sarahellis
ID: 34944173
I found what I was looking for on another site.  Thank you for your suggestion.  Your way would have worked too, so I will award points.  But I really wanted to use the transferdatabase method.  In case anyone is interested, the code is:

Public Sub test_Click()

    Dim dbs As Database
    Dim tdf As TableDef
    Dim strLinkedTableName As String
    Dim strSourceTableName As String
   
    On Error GoTo ErrHandler
   
    Set dbs = CurrentDb()
    For Each tdf In dbs.TableDefs
           
'store the linked table name
            strLinkedTableName = tdf.name
'store the source table name
            strSourceTableName = "dbo." & Mid(strLinkedTableName, 10)
'delete old copied table
            If TExists(Mid(strLinkedTableName, 6)) = True Then
                DoCmd.DeleteObject acTable, Mid(strLinkedTableName, 6)
            End If
'import the source table
            DoCmd.TransferDatabase acImport, "ODBC Database", _
                "ODBC;Driver={SQL Server}; Server=ProdServer;DATABASE=prod_db;UID=abc;PWD=xyz", _
                acTable, strSourceTableName, strSourceTableName, StructureOnly:=False

            On Error GoTo ErrHandler
        End If
NextRec:
    Next
     
ExitHandler:
    On Error Resume Next
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Sub
ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub
 

0

Featured Post

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
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 …

728 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