Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do I refresh table links in code?

Access Howto:

How do I refresh table links in code?

by  huzza  Posted    (Edited  )
To do this use the following code. It is a cut down of a function I use to update the links on the tables in my database when I ship a new version. The location of the database source on each users' machines is different the only thing I use the DSN for is to tell me which driver is being used.

All you need to do is enter the value you need for the db location i.e the DBpath variable, which you may want to pass this in as a parameter to the function. You will also need to alter the connection string - for details see connection strings in access' help

Code: 
Function RunFirstTime()
    On Error GoTo Err_RunFirstTime
    Dim CurDB As Database, tdfLinked As TableDef
    Dim TBDef As TableDef, InstallPath As String
    Dim DBPath As String
      
    Set CurDB = CurrentDb
    DBPath =
"c:\my documents\MyDb.mdb
[color red]'set this to the path/name of the datasource you wish to use[/color]
Code: 
' Cycle through the database tables refreshing their links 
    For Each TBDef In CurDB.TableDefs
        If (TBDef.Attributes And dbAttachedODBC) Then
            Set tdfLinked = CurDB.TableDefs(TBDef.Name)
            tdfLinked.Connect = "ODBC;DSN=Calibre;HOST=170.30.153.27;UID=systpe;DATABASE=" & DBpath & ";SERVICE=sqlnw;TABLE=span_ip.prod_def"
[color red]'you will need to change this connect string to contain the relevant details for you ODBC type [/color]
Code: 
            tdfLinked.RefreshLink
        End If
    Next TBDef

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

Richard
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top