VBA code to connect to Oracle and to close the connection using Oracle drivers
Posted on 2003-11-18
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.