Solved

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

Posted on 2003-11-18
6
893 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

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

728 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