refreshing or relinking tables by code

[Rod]

I'm trying to create a simplified version of the linked table manager to
refresh or relink tables from a front end app to two different mdb files in
two different locations. I'm trying to use an approach a found where the
TableName and DataFilePath is stored in table tblLinkedTables.

This is the code:
Function RelinkByList() As Integer
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim tdf As DAO.TableDef

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblLinkedTables", dbOpenSnapshot)

' Loop through the recordset, processing rows
Do Until rst.EOF
Set tdf = dbs.TableDefs(rst!TableName)
tdf.Connect = rst!DataFilePath
tdf.RefreshLink
rst.MoveNext
Loop

Set tdf = Nothing
Set rst = Nothing
Set dbs = Nothing
End Function

The code returns an error saying: "Run-time error '3170': Could not find
installable ISAM."

On debug, the line referenced is tdf.RefreshLink.

What am I doing wrong?

Rod

 

[Klatuu]

I did not write this, I inherited it, but it does work:

Private Sub cmdChangeLinks_Click()

Dim DataCnt As Integer
Dim datafile As Integer
Dim dbs As Database
Dim intCount As Integer
Dim LocalCnt As Integer
Dim localfile As Integer
Dim Response As Variant
Dim tdf As TableDef

Set dbs = CurrentDb()
On Error GoTo linkerror

' Be sure the user has specified all fields!
If Len(Nz(Me.datapath)) = 0 Or Len(Nz(Me.localpath)) = 0 Then
MsgBox "You must specify all the linkage paths on the form before I can
relink!", vbExclamation, "Specify linkage paths."
Exit Sub
End If

DataCnt = 0
LocalCnt = 0
DoCmd.Hourglass (True)

intCount = 0
Response = SysCmd(acSysCmdInitMeter, "Relinking Tables - Please Wait ",
dbs.TableDefs.Count - 1)

For Each tdf In dbs.TableDefs
intCount = intCount + 1
Response = SysCmd(acSysCmdUpdateMeter, intCount)

If Len(tdf.Connect) > 0 Then
' Its a linked table. Re-link

datafile = InStr(1, tdf.Connect, "CISCMSdata")
localfile = InStr(1, tdf.Connect, "LocalData")

If localfile > 0 Then
tdf.Connect = ";database=" & Me.localpath
tdf.RefreshLink
LocalCnt = LocalCnt + 1

ElseIf datafile > 0 Then
tdf.Connect = ";database=" & Me.datapath
tdf.RefreshLink
DataCnt = DataCnt + 1

End If
Else
' Not a connected table; don't do anything.
End If
Next tdf
DoCmd.Hourglass (False)
Response = SysCmd(acSysCmdRemoveMeter)
MsgBox "Files relinked! " & vbCr & DataCnt & " data files and " & LocalCnt
& " local Files were relinked"
Exit Sub

 

[Neil]

Check out:

http://www.mvps.org/access/tables/tbl0009.htm

There's a nice piece of code here that I've used a few times very succesfully.

HTH

Neil
www.nwarwick.co.uk