We help IT Professionals succeed at work.

Return result from SELECT pass-through query in an Access 2003 VBA function

wanderingbob
wanderingbob asked
on
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.

VBA function:

Function GetCustomerNo(Optional orderNo As String)

    If IsNull(orderNo) Then
        GetCustomerNo = Null
    Else
   
        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"
        qry.Execute
        GetCustomerNo = qry.Fields(0)

        Set qry = Nothing
        Set db = Nothing
   
    End If
   
End Function

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,
Bob
Comment
Watch Question

Quick pointer in the right direction: you'll need to use the query as the source for a recordset.  Open up the VBA editor (ALT+F11) and search help for "OpenRecordset" for some examples.

Author

Commented:
Thanks for the pointer Andrew!  The OpenRecordset method worked in the VBA function, allowing the function to return the desired result (so far, I have tested the function by calling it with a macro).  The VBA code I used was:

Function GetCustomerNo(Optional orderNo As String)

    If IsNull(orderNo) Then
        GetCustomerNo = Null
    Else
   
        Dim db As DAO.Database
        Dim qry As DAO.QueryDef
        Dim rs As Recordset
        Set db = CurrentDb()
        Set qry = db.QueryDefs("qryGetCustomerNo")
        qry.SQL = "SELECT METR4APP.CUSTOMER_ORDER_API.Get_Customer_No('" & orderNo & "') FROM DUAL"
        Set rs = db.OpenRecordset("qryGetCustomerNo")
        GetCustomerNo = rs.Fields(0)
   
        Set rs = Nothing
        Set qry = Nothing
        Set db = Nothing
   
    End If
   
End Function

However, I am now returning the result of the SELECT pass-through query using a macro - which is not really what I need to do.

What I need to do is to create a query that selects from the linked Oracle tables, and also calls the VBA function above to retrieve the result from the SELECT pass-through query (this SELECT pass-through query, and all others I use for this project, will be invoking Oracle functions that return only one scalar value like a number, string, or date.  The syntax is probably wrong, but what I need is a query like:

SELECT ORDER_NO,
GetCustomerNumber(ORDER_NO),
PART_NO
.
.
.
FROM  METR4APP_CUST_ORD_INV_STAT

Thank you,
Bob
A local query can use a VBA function, Access understands that just fine.  So, where's the second query going to be used?  Is it going to be return a whacking great set of data or a single record?  There's no WHERE clause on your example.

Why I ask is that if you can link to the tables, then maybe you can use a local (non-pass-through) query for the second query, but as it will have a VBA function running on each row that has to scoot off and ask Oracle to do a little work, this might be very slow if it's across a large set of records.  But if it's for a single record, or a very few records, or is going to run overnight (you get the picture) then it might well work.

Author

Commented:
I tested embedding the VBA function in the second query and as you said, it works fine - Thank You!

The second query will return a relatively large set of data - perhaps about 20,000 rows.  This report will be used monthly to retrieve revenue, discounts, returns, and cost of goods sold for each customer order line, after the accounting month has been closed.  For example, in early July, after June has been closed, this query will be used to retrieve June data.

It can run over the course of several hours if necessary.  I understand the performance penalty of calling the VBA for each row, but that's the only way that Access can call the Oracle functions, right?  The Oracle functions call an extended web of many interrelated Oracle tables and views that I don't want to have to link into Access.  Because of the aforementioned SOX procedural and documentation requirements, creating any new objects in Oracle for this project would involve extended gyrations that I would rather avoid.

Thank you,
Bob
That makes sense.

20,000 isn't that much, if it takes 5 ms per row.  If it takes a second per row, then OW!  Make sure you do a pile of testing to check on time-outs etc.  An alternative might be to pull the data from the VBA function across into a local table, and update that frequently, so that each update is working on just a few rows and is quicker, then run the query using a join.

There's loads of ways to do this!  I've done a data validation procedure, years ago, using Access 97 and Oracle where we were running code like yours across millions of rows, and it took overnight, but it ran fine.  I've no idea where my old code's got to, so don't ask for a sample, but I know what you're doing is do-able.

I work in a bank and I understand the fun and games that SOX provides for us all.  Nothing like a challenge, eh?

Author

Commented:
Andrew's hints that I needed to use OpenRecordset, and that I could call the VGA function directly in the (local) query were exactly the help I needed.

Thanks Again!