Solved

Specify Connection string once for multiple pass-through queries

Posted on 2008-10-27
11
777 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
 
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

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…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

863 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

25 Experts available now in Live!

Get 1:1 Help Now