sarahellis
asked on
Need to copy linked tables from SQL Server to local copy.
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=ejacket prod_db;DA TABASE=eJa cketprod_d b;UID=abc; PWD=xyz;", _
ObjectType:=acTable, _
Source:=strSourceTableName , _
Destination:=strSourceTabl eName, _
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
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=ejacket
ObjectType:=acTable, _
Source:=strSourceTableName
Destination:=strSourceTabl
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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(strLinkedTable Name, 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;U ID=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
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(strLinkedTable
DoCmd.DeleteObject acTable, Mid(strLinkedTableName, 6)
End If
'import the source table
DoCmd.TransferDatabase acImport, "ODBC Database", _
"ODBC;Driver={SQL Server}; Server=ProdServer;DATABASE
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
ASKER
Thanks!