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
Solved

Specify Connection string once for multiple pass-through queries

Posted on 2008-10-27
11
792 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
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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
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 49

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 49

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 49

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL trigger 5 23
IIF in access query 19 25
90 days before current date 12 32
Local HTML file to display local Access MDB data in browser 4 27
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

828 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