RefreshLink error 3051 1

[tobynegus]

I have some linked Txt and EXL files, and some code to refresh link, it works fine on the txt files but fails with the xls file.
gives error 3051 and says it is already opened exclusively by aniother user!
this is a stand alone envioronmnet, and it links fine if i us the manulal Linked Table Manager.
Code shown Below
-----------------------
Private Sub optLink_Click()
Dim strFileName$
Dim tdf As TableDef
Dim strConnect As String
For Each tdf In CurrentDb.TableDefs
If tdf.SourceTableName <> "" Then
Debug.Print tdf.SourceTableName
strFileName = GetPath & "\RawData" '\" & tdf.SourceTableName
If tdf.SourceTableName = "INVMONTHMIC.txt" Then GoTo exit_next 'error they still appear in database god know why!
If tdf.SourceTableName = "INVMONTHBHI.txt" Then GoTo exit_next
If tdf.SourceTableName = "CELLPATH.csv" Then GoTo exit_next
strConnect = Mid(tdf.Connect, 1, InStr(tdf.Connect, "Database=") + 8) & strFileName

' tdf.Connect = strFileName
tdf.Connect = strConnect
tdf.RefreshLink
End If
exit_next:
Next
MsgBox "all data has been relinked"
Me.optLink = Null
End Sub
-------------------------------------
any help much aprreciated

Toby

 

[MazeWorX]

I use this function(sorry cant remember who wrote it) to refresh the links in a db
strFileName is the full path to the db containing the tables. however I only refresh the links if I cant find the tables with another function

Private Function RefreshLinks(strFileName As String) As Boolean
' Refresh links to the supplied database. Return True if successful.

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef

    ' Loop through all tables in the database.
    Set dbs = CurrentDb
    For Each tdf In dbs.TableDefs
        ' If the table has a connect string, it's a linked table.
        If Len(tdf.Connect) > 0 Then
            tdf.Connect = ";DATABASE=" & strFileName
            Err = 0
            On Error Resume Next
            tdf.RefreshLink         ' Relink the table.
            If Err <> 0 Then
                RefreshLinks = False
                Exit Function
            End If
        End If
    Next tdf

    RefreshLinks = True        ' Relinking complete.

End Function

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work