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

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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;" & _

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

Set rsRec = Nothing

Set oConn = Nothing

End Sub


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

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.


Thanks.  We'll give this a go.

This helps us to get rid of the connection info prompt when running pass throughs.  Thanks for this information as well.
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/)


Cleanup Volunteer

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.

EE Cleanup Volunteer
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.