Solved

Specify Connection string once for multiple pass-through queries

Posted on 2008-10-27
11
765 Views
Last Modified: 2013-11-27
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
0
Comment
Question by:omgang
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
 
LVL 7

Expert Comment

by:UniqueData
Comment Utility
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
 
LVL 28

Author Comment

by:omgang
Comment Utility
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
 
LVL 28

Author Comment

by:omgang
Comment Utility
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
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 28

Author Comment

by:omgang
Comment Utility
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
 
LVL 18

Assisted Solution

by:jmoss111
jmoss111 earned 250 total points
Comment Utility
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
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
 
LVL 28

Author Comment

by:omgang
Comment Utility
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
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
You are welcome!

/gustav
0
 
LVL 18

Expert Comment

by:jmoss111
Comment Utility
You're welcome omgang!

Jim
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now