I'm not sure if this applies to your situation, but I set up a routine that let me work with whichever Oracle environment I needed. We had 4 environments.
stratdev1 DEVL..........Develoment
stratpre1 STRTPRE.....PreProduction/Maintenance
strategis SGIP...........Corporate Production
stratuat SUAT.........User Acceptance Testing
I've been retired for a number of years, but this was part of the code involved.
Code:
'---------------------------------------------------------------------------------------
' Procedure : RelinkCOCATables
' DateTime : 2006-01-03 17:19
' Author : j
' Purpose : Relink all existing COCA_DBA tables in the proper Oracle environment
'
'Parm: Env string
' stratdev1 DEVL
' stratpre1 STRTPRE
' strategis SGIP
' stratuat SUAT
'---------------------------------------------------------------------------------------
'
Sub RelinkCOCATables(Env As String)
Dim connectEnv As String 'environment to connect to
Dim cocaDev As String 'current coords DEVL
Dim cocaPre As String 'current coords STRTPRE
Dim cocaProd As String 'current coords SGIP
Dim cocaUat As String 'current coords SUAT
Dim Msg As String 'msg string to show activity
Dim sResponse As String 'response to msg buttons
Dim tbl As DAO.TableDef
Dim db As DAO.Database
Set db = CurrentDb
'Oracle Tables ODBC
cocaDev = "Ora901 devl;PWD=XXXXX;"
cocaPre = "Ora901 strtpre;PWD=YYYYYY;"
cocaProd = "Ora901 sgip;PWD=PPPPPPP;"
cocaUat = "Ora901 suat;PWD=WWWWWWW;"
Select Case Env
Case "stratdev1"
connectEnv = cocaDev
Case "stratuat"
connectEnv = cocaUat
Case "stratpre1"
connectEnv = cocaPre
Case "strategis"
connectEnv = cocaProd
Case Else
End Select
' -- RELINK EACH COCA_DBA TABLE
For Each tbl In db.TableDefs
If tbl.Name Like "COCA_DBA*" Then
'drop current linked table
DoCmd.RunSQL ("DROP TABLE " & tbl.Name)
'relink the table in proper environment
DoCmd.TransferDatabase acLink, "ODBC Database", _
"ODBC;UID=COCA_USER;DSN=" & connectEnv, _
acTable, "COCA_DBA." & Mid(tbl.Name, 10), _
tbl.Name, , True
Msg = Msg & Mid(connectEnv, 1, InStr(connectEnv, ";")) & " Relinked : " & tbl.Name & vbCrLf
Else
End If
Next tbl