Solved

Specify Connection string once for multiple pass-through queries

Posted on 2008-10-27
11
807 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 22817381
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
ID: 22817421
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
ID: 22817433
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 28

Author Comment

by:omgang
ID: 22817446
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 51

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 22817470
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
 
LVL 28

Author Comment

by:omgang
ID: 22817507
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
ID: 22818417
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 51

Expert Comment

by:Gustav Brock
ID: 22819555
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
ID: 22825753
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 51

Expert Comment

by:Gustav Brock
ID: 22826958
You are welcome!

/gustav
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22827110
You're welcome omgang!

Jim
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

615 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