Solved

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

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

758 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

23 Experts available now in Live!

Get 1:1 Help Now