Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2003-11-18
6
Medium Priority
?
900 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 500 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 500 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

618 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