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

My FAQ Archive

Come Join Us!

Posting Guidelines

Jobs

Microsoft: Access Other topics FAQ

Access Howto:

My Archive

Join Tek-Tips® Today!

Join Tek-Tips^® Today!