How do I refresh linked tables in Access Automatically

[levelhot]

I'm need to up date a set of linked tables programmatically. The tables are linked to tab delimtied text files and need to be refreshed by custom VBA function.
The names of the tables are WETSCND, WETSRES, WETSMUL, WETSVAC. WHat's the best way to do this.

Thanks
Level Hot

 

[votegop]

I've used the following code to successfully re-link my backend. I used it in the "on load" event of my splashscreen at first, which may work just fine. I found that it made my database startup way too slow, so I call it from an "on click" event now.

I did not write this code, so credit is due to someone here (not me).

Function RunFirstTime() 'this code for refreshing table links to back end
On Error GoTo Err_RunFirstTime
Dim CurDB As Database, tdfLinked As TableDef
Dim DBPath As String

Set CurDB = CurrentDb
DBPath = "t:\MyBackEndFolder\MyBackEnd.mdb"
set this to the path/name of the datasource you wish to use

Cycle through the database tables refreshing their links
For Each tdfLinked In CurDB.TableDefs
If Len(tdfLinked.Connect) > 0 Then
tdfLinked.Connect = ";DATABASE=" & DBPath
tdfLinked.RefreshLink
End If
Next tdfLinked

Exit_RunFirstTime:
Set CurDB = Nothing
Set tdfLinked = Nothing
Exit Function
Err_RunFirstTime:
MsgBox Err.Number & ": " & Err.Description, vbOKOnly + vbCritical, "An error has occured in procedure RunFirstTime"
Resume Exit_RunFirstTime
End Function


Hope this helps.
Jay

 

[no1biscuit]

Your code was a life saver. Thanks
Thom