ODBC with multiple databases

[jacquid]

I have set-up one System-DSN with a generic data source name. I hoped to just override the database name to point to the database I would like to access at that point in time.
But I now discovered that when you link the table, the table properties store the database in the description: "ODBC;DSN=Calibre;HOST=170.30.153.27;UID=systpe;DATABASE=cortal;SERVICE=sqlnw;TABLE=span_ip.prod_def".

How do I set-up a link or ODBC where I can change the database in the ODBC setting and the link will then point to the new database, automatically?

 

[skfoster]

I think one way you can do is using inputbox and assign different ODBC setting as a tring to different variable.

 

[huzza]

This is a cut down of a function I use to update the links on the tables in my database each time 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 (in your case it looks just to be the database name) you may want to pass this in as a parameter to the function

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 = '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"
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

I hope this helps,
Richard

 

[jacquid]

Thanx