INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Access Howto:

How do I refresh table links in code? by huzza
Posted: 27 Nov 00

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

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
'set this to the path/name of the datasource you wish to use
    
' 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"

'you will need to change this connect string to contain the relevant details for you ODBC type

            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

Back to Microsoft: Access Other topics FAQ Index
Back to Microsoft: Access Other topics Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close