omgang
asked on
Specify Connection string once for multiple pass-through queries
I have an Access 2003 app with multiple pass-through queries to an Oracle 10g back-end. Is it possible to specify a connection string once, i.e. in a function, and not have to re-specify in each pass-through query?
OM Gang
OM Gang
Not sure if it works in Oracle or not but I have AS400 that I do pass through to and I have created a ODBC conection on each computer that runs the DB. Then when I make my query I do this
strConnect = "ODBC;DSN=AS400 ODBC"
Set qdf = dbs.CreateQueryDef("PTBROK ERAGEORDER INFO")
qdf.Connect = strConnect
Of course there is quite a bit more code but check out the strConnect. That is the key.
strConnect = "ODBC;DSN=AS400 ODBC"
Set qdf = dbs.CreateQueryDef("PTBROK
qdf.Connect = strConnect
Of course there is quite a bit more code but check out the strConnect. That is the key.
ASKER
I did some searching here before posting the Q. I've tried the following
create a function that returns the connection string
Public Function GetConnectString() As String
On Error GoTo Err_GetConnectString
Dim strConString As String
strConString = "ODBC;Driver={Microsoft ODBC for Oracle};UID=XXXXX;PWD=XXXX XX;SERVER= TheOracleD B;"
GetConnectString = strConString
Exit_GetConnectString:
Exit Function
Err_GetConnectString:
MsgBox Err.Number & ", " & Err.Description, , "Error"
Resume Exit_GetConnectString
End Function
establish a connection to the back-end
Public Sub PingDatabase() ' Ping the back end so that the application caches the connect string and won't ask again.
Dim db As Database
Set db = OpenDatabase("", False, True, GetConnectString)
DoCmd.Beep ' Ping!
End Sub
in my pass-through query I then changed the ODBC Connect Str property to
ODBC;Driver={Microsoft ODBC for Oracle};SERVER=TheOracleDB ;
I called the public sub to establish the connection. I then attempted to open the pass-through query but received a prompt for username and pwd
OM Gang
create a function that returns the connection string
Public Function GetConnectString() As String
On Error GoTo Err_GetConnectString
Dim strConString As String
strConString = "ODBC;Driver={Microsoft ODBC for Oracle};UID=XXXXX;PWD=XXXX
GetConnectString = strConString
Exit_GetConnectString:
Exit Function
Err_GetConnectString:
MsgBox Err.Number & ", " & Err.Description, , "Error"
Resume Exit_GetConnectString
End Function
establish a connection to the back-end
Public Sub PingDatabase() ' Ping the back end so that the application caches the connect string and won't ask again.
Dim db As Database
Set db = OpenDatabase("", False, True, GetConnectString)
DoCmd.Beep ' Ping!
End Sub
in my pass-through query I then changed the ODBC Connect Str property to
ODBC;Driver={Microsoft ODBC for Oracle};SERVER=TheOracleDB
I called the public sub to establish the connection. I then attempted to open the pass-through query but received a prompt for username and pwd
OM Gang
ASKER
UniqueData - I've been using a DSN and it does work. The problem is the DSN needs to be established on each machine. I'd like to switch to DSNless connection but am looking for a method that allows me to stipulate the connection string once so I don't have to change the ODBC Connect Str property for each of the pass-through queries.
OM Gang
OM Gang
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The Q where I found the example to 'ping' the db suggested this was enough to establish a connection that Access would then cache. It appears to not work unless I am doing something wrong. I was hoping that I was missing something.
<<Or create a static or public/global connection variable to hold the connection while the queries run.>>
you mean something like????
Public pDB as Database
Public Sub PingDatabase() ' Ping the back end so that the application caches the connect string and won't ask again.
Dim db As Database
Set db = OpenDatabase("", False, True, GetConnectString)
Set pDB = db
'DoCmd.Beep ' Ping!
End Sub
OM Gang
<<Or create a static or public/global connection variable to hold the connection while the queries run.>>
you mean something like????
Public pDB as Database
Public Sub PingDatabase() ' Ping the back end so that the application caches the connect string and won't ask again.
Dim db As Database
Set db = OpenDatabase("", False, True, GetConnectString)
Set pDB = db
'DoCmd.Beep ' Ping!
End Sub
OM Gang
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes:
Public pDB as Database
Public Sub PingDatabase()
Set pDB = OpenDatabase("", False, True, GetConnectString)
DoCmd.Beep ' Ping!
End Sub
' Run queries.
pDB.Close
Set pDB = Nothing
But I think I would prefer a method like that suggested by Jim.
/gustav
Public pDB as Database
Public Sub PingDatabase()
Set pDB = OpenDatabase("", False, True, GetConnectString)
DoCmd.Beep ' Ping!
End Sub
' Run queries.
pDB.Close
Set pDB = Nothing
But I think I would prefer a method like that suggested by Jim.
/gustav
ASKER
cactus data, I didn't try that method as I liked jmoss111' suggestion as well. It works just fine and does exactly what I want it to.
Thanks to you both.
OM Gang
Thanks to you both.
OM Gang
You are welcome!
/gustav
/gustav
You're welcome omgang!
Jim
Jim
/gustav