Solved

VBA code to connect to Oracle and to close the connection using Oracle drivers

Posted on 2003-11-18
6
890 Views
Last Modified: 2013-01-29
We are looking for vba code to instantiate an Oracle 9 connection utilizing Oracle's direct driver (part of oo4o).  This code would be instantiated periodically from within MS Access in order to pass through Oracle PL/SQL to Oracle databases.  The goal is to use this same Oracle Direct driver, but to instantiate code to pass the connection information during various functionality within an Access front end.  Then necessary sql could be passed, the data returned, and the code would close this connection.  The way we are able to manually accomplish this functionality is to have a DSN set up in odbcad32 using the Oracle direct driver, to write pass through PL/SQL in Access, and to manually execute it.  A dialog then prompts to choose the DSN and enter the pertinent connection information.

The goal instead, for example, is to trigger this same functionality based on a user event such as On Enter of a combo box in the Access front end.  For example, on a form where a combo box rowsource must be populated by a return of 'select distinct field_name1 from tbl_name where field_name2 = a value in a text box on the form.  This would happen without the prompt for DSN and connection information.

Efficient code to use a recordset within this connection object would also be very useful to us.  Any code sample?  Thanks for ideas.
0
Comment
Question by:musiciansfriend
[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
6 Comments
 
LVL 29

Accepted Solution

by:
leonstryker earned 125 total points
ID: 9776576
Make a reference to MDAC 2.5 or later and then you will need to do something like this:

Sub Button1_Click()
Dim oConn as ADODB.Connection
Dim rsRec as ADODB.Recordset
Dim strSQL as String

Set oConn = New ADODB.Connection
'/ "Data Source=" must be set to the appropriate name.  It is the alias in the tnsnames.ora file.
oConn.Open "Provider=OraOLEDB.Oracle;" & _
           "Data Source=MyOracleDB;" & _
           "User Id=myUsername;" & _
           "Password=myPassword"

Set rsRec = New ADODB.Recorset
strSQL = "SELECT DISTINCT field_name1 FROM tbl_name WHERE field_name2 = '" & cboItem.Text & "'"

rsRec.Open strSQL, oConn

If rsRec.RecordCount >0 Then
    '/ Code to do something with the records
End

rsRec.Close
Set rsRec = Nothing

oConn.Close
Set oConn = Nothing

End Sub
 

Leon
0
 
LVL 9

Assisted Solution

by:svenkarlsen
svenkarlsen earned 125 total points
ID: 9776847
Musiciansfriend,

the reason you are being prompted for the DSN every time you run the pass-through query, is because you haven't set the query properties correctly.

Open the pass-through query, open the properties window for the query (make sure ti's the query and not for a field ;-).

In the properties for the query, you should set the ODBC-connection string as minimum, then your query will run without DSN-prompt.


Regards,
Sven
0
 

Expert Comment

by:cfoxer
ID: 9780678
leonstryker,

Thanks.  We'll give this a go.

sven,
This helps us to get rid of the connection info prompt when running pass throughs.  Thanks for this information as well.
0
 
LVL 9

Expert Comment

by:ornicar
ID: 10064367
----------------------------------------------------------------------------------------
This question has been abandoned and needs to be finalized.
 You can accept an answer, split the points, or get a refund (information at http:/help.jsp#hs5)
  If you need a moderator to help you, post a question at Community Support (http:/Community_Support/)

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

ornicar
Cleanup Volunteer

---------------------------------------------------------------------------------------------
0
 
LVL 39

Expert Comment

by:stevbe
ID: 10103760
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Split: leonstryker {http:#9776576} & svenkarlsen {http:#9776847}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

stevbe
EE Cleanup Volunteer
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

756 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