- Microsoft Access
- VBA
Return result from SELECT pass-through query in an Access 2003 VBA function
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_AP
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("qryGetCustom
qry.SQL = "SELECT METR4APP.CUSTOMER_ORDER_AP
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
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_AP
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("qryGetCustom
qry.SQL = "SELECT METR4APP.CUSTOMER_ORDER_AP
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
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.
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("qryGetCustom
qry.SQL = "SELECT METR4APP.CUSTOMER_ORDER_AP
Set rs = db.OpenRecordset("qryGetCu
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
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("qryGetCustom
qry.SQL = "SELECT METR4APP.CUSTOMER_ORDER_AP
Set rs = db.OpenRecordset("qryGetCu
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.
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.
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
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?
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?
-
![]()
received a Solution
Replacing Dlookup with a select query in Ms Access VBA -
![]()
wrote an Article
![]()
MAC addresses in Access and Excel
-
![]()
created a Video
![]()
Wrapper-1, Query. Show result of Excel Worksheet Function in Access Query
-
Explore Cloud Class® ![]()
Certification: MOS Microsoft Office Specialist - Access 2016 Part 3
Premium Content
You need an Expert Office subscription to comment.Start Free Trial