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=18.104.22.168;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