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
LVL 28
omgangIT ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
If you open a connection to the database using that connection string and keep it open during the calls of the queries, you should not need to re-specify the connection string for each query.

/gustav
0
UniqueDataCommented:
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("PTBROKERAGEORDERINFO")
        qdf.Connect = strConnect

Of course there is quite a bit more code but check out the strConnect. That is the key.
0
omgangIT ManagerAuthor Commented:
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=XXXXXX;SERVER=TheOracleDB;"
   
    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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

omgangIT ManagerAuthor Commented:
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
0
Gustav BrockCIOCommented:
I guess you have to wrap the calls of the queries into PingDatabase as - I would assume - after the Beep the connection will be closed.

Or create a static or public/global connection variable to hold the connection while the queries run.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
omgangIT ManagerAuthor Commented:
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
0
jmoss111Commented:
An example:

gUsrNm contains windows username
Public Const strConnect1 = "ODBC;DRIVER={SQL SERVER};SERVER=999.999.999.999\SQL1;DATABASE=MyDATA;UID="
Public Const strConnect2 = ";PWD=xxxxx;ADDRESS=999.999.999.999;"
 
Sub BuildConnect
Set db = CurrentDb
db.QueryDefs("qptGetICUser").Connect = strConnect1 & gUsrNm & strConnect2
db.QueryDefs("qptGetData").Connect = strConnect1 & gUsrNm & strConnect2
db.QueryDefs("qptGetPaymentNumber").Connect = strConnect1 & gUsrNm & strConnect2

Open in new window

0
Gustav BrockCIOCommented:
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
0
omgangIT ManagerAuthor Commented:
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
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
jmoss111Commented:
You're welcome omgang!

Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.