Refreshing linked tables in Access 2002

[a2ross]

I'm ready to give up!!!!!!

I have a front and back end of a simple database. Everytime I open it up from a different computer on our network the table links are broken so I want it to check for this and prompt for the location of the back end.

I have some code that tries to refresh the link and calls a separate sub if the link is broken. The code for the sub is below:

Private Sub ResetLinks()
Dim db As DAO.database
Dim recLinkTables As DAO.Recordset
Dim tbdTemp As DAO.TableDef
Dim strTemp, strPath As String
Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.Show
'retrieve the path from the file dialog
strPath = fd.SelectedItems(1)
MsgBox strPath

Set db = CurrentDb
Set recLinkTables = db.OpenRecordset("tblLinks", dbOpenDynaset)
If recLinkTables.RecordCount > 0 Then
With recLinkTables
.MoveFirst
While Not .EOF
db.TableDefs(!TableName).Connect = ";DATABASE =" & strPath
db.TableDefs(!TableName).RefreshLink
.MoveNext
Wend
End With
End If
End Sub

I have had message boxes in places returning my connection strings etc. In short, the .connection property houses the original database and path early in the sub. The .connection is set to the new database and path just prior to the line that should refresh the link. The .refreshlink call returns an error number 3001 "Invalid Argument".

If I look at the .connection property when the links are first being tested for refresh, it is identical to the one that gives the problem later. (I have simply been renaming the back end file by one letter each time to break the link and test the subs)

Any suggestions?????

 

[Page410]

This may not be an ideal solution to your problem but it may be a work around until you come across a better one.

Have you considered deleting the table defs for linked tables in the current db and simply re-linking them rather than trying to refresh the linked tables. You could use DoCmd.TransferDatabase acLink to get this done.

 

[a2ross]

Thanks.

I'll give it a go.

 

[beetee]

Here's some code I use for linking tables:

Function LinkTable(SourceDbName As String, TableName As String, ErrorMsg As String, Optional OkIfExists As Boolean = True) As Boolean
On Error GoTo LinkTableError
Dim MyDb As Database
Set MyDb = CurrentDb

LinkTable = False

LinkTableDoLink:
With MyDb
If OkIfExists Then
On Error Resume Next
.TableDefs.Delete TableName
On Error GoTo LinkTableError
End If

Dim MyTdf As TableDef
Set MyTdf = Nothing

Set MyTdf = .CreateTableDef(TableName)
MyTdf.Connect = ";DATABASE=" & SourceDbName
MyTdf.SourceTableName = TableName

.TableDefs.Append MyTdf
.TableDefs.Refresh
MyTdf.RefreshLink
End With
LinkTable = True

LinkTableExit:
Set MyTdf = Nothing
MyDb.Close
Set MyDb = Nothing
Exit Function

LinkTableError:
ErrorMsg = "Cannot link " & TableName & " from " & SourceDbName & ": " & Err.Description
Resume LinkTableExit
End Function

Function UnLinkTable(TableName As String, ErrorMsg As String) As Boolean
On Error GoTo UnLinkTableError
UnLinkTable = False
Dim CurTableDefs As TableDefs
Set CurTableDefs = CurrentDb.TableDefs

Dim MyTdf As TableDef
Set MyTdf = CurTableDefs(TableName)
If Not MyTdf Is Nothing Then
If (MyTdf.Attributes And dbAttachedTable) = dbAttachedTable Then
CurTableDefs.Delete TableName
End If
End If
UnLinkTable = True
UnLinkTableExit:
Exit Function
UnLinkTableError:
ErrorMsg = "Cannot Unlink: " & TableName & ": " & Err.Description
Resume UnLinkTableExit
End Function


Sub LinkEmAll(DataDbName As String)
Const c_MyName As String = "LinkEmAll"

On Error GoTo LinkEmAllError
Dim DataDb As Database
Dim ErrorMsg As String
Dim ErrorNumber As Integer
Set DataDb = DBEngine(0).OpenDatabase(DataDbName)
If Not DataDb Is Nothing Then
With DataDb
Dim MyTblDef As TableDef
For Each MyTblDef In DataDb.TableDefs
If (MyTblDef.Attributes And (dbHiddenObject Or dbSystemObject)) = 0 Then
Dim TableName As String
TableName = MyTblDef.Name
UnLinkTable TableName, ErrorMsg
If Not LinkTable(DataDbName, TableName, ErrorMsg) Then
' perhaps indicate an error occured
GoTo LinkEmAllExit
End If
DoEvents
End If
Next MyTblDef
.TableDefs.Refresh
End With
DataDb.Close
Set DataDb = Nothing
End If

LinkEmAllExit:
Exit Sub

LinkEmAllError:
Resume LinkEmAllExit
End Sub

 

[a2ross]

Thanks beetee.

Worked a treat.