We help IT Professionals succeed at work.
Get Started

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

wanderingbob
wanderingbob asked
on
3,470 Views
Last Modified: 2013-11-28
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
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE