Good Morning Experts,
I'm attempting to "pull" data out of an Oracle 10g database using MS Access 2003. The Access database is local to my workstation (Windows XP professional) and I have unlimited flexibility with the Access database, but the Oracle database is a production instance - I can't install anything on or even access the server on which the oracle database is running, such as for Heterogeneous data services. Because of excessive SOX procedures and documentation requirements, I would like to do as much on the Access side as possible, with few or ideally, no objects needed on the Oracle side.
Using a System DSN I set up in ODBC, I have been able to link tables from the Oracle database into the Access database, and I can query the data in the linked tables from Access. However, I need values that can only be retrieved from functions in the Oracle database. To do this, I created pass-through queries in Access, using the System DSN to connect to the Oracle database. However, as discussed previously in other posts, pass-through queries cannot accept input parameters and VBA code must be used to dynamically build the pass-through query with the parameter information embedded. I want to create functions that dynamaically build the pass-through queries with the parameters, cause the SELECT pass-through queries to be executed with the parameters, and return the result of the SELECT pass-through query as the result of the function.
An example of one such pass-through query is:
SELECT METR4APP.CUSTOMER_ORDER_API.Get_Customer_No('P200208') FROM DUAL
I created the following VBA function to dynamically build the SELECT pass-through query, including the parameter, but when I attempt to run it an exception is generated.
Function GetCustomerNo(Optional orderNo As String)
If IsNull(orderNo) Then
GetCustomerNo = Null
Dim db As DAO.Database
Dim qry As DAO.QueryDef
Set db = CurrentDb()
Set qry = db.QueryDefs("qryGetCustomerNo")
qry.SQL = "SELECT METR4APP.CUSTOMER_ORDER_API.Get_Customer_No('" & orderNo & "') FROM DUAL"
GetCustomerNo = qry.Fields(0)
Set qry = Nothing
Set db = Nothing
Exception: Run-time error '3065':
Cannot execute a select query.
I feel that there is probably a minor syntax issue preventing this from working. I have worked almost exclusively with Oracle and SQL Server for the last five years and my Access is more than a little rusty.
Thanks in advance,