Use Access function in SQL pass-through query

Question

I have an MS Access front-end with an Oracle SQL back-end database.

I'm trying to create a pass-through query that contains a function within Access. The function is for setting the jobnumber based on what the user types into the login screen.

SELECT 
    CC_QAQC_SPEC_MASTER.JOBNUMBER, 
    CC_QAQC_SPEC_MASTER.SPECSECTION,
    CC_QAQC_SPEC_MASTER.SPECDESCRIPTION,
    CC_QAQC_SPEC_MASTER.ID
FROM 
    CC_QAQC_SPEC_MASTER
WHERE 
    CC_QAQC_SPEC_MASTER.JOBNUMBER=GET_QAQC_JOB()
ORDER BY 
    CC_QAQC_SPEC_MASTER.SPECSECTION, 
    CC_QAQC_SPEC_MASTER.SPECDESCRIPTION;

When I run the above I receive an error message that says:

ODBC--call failed [Oracle][ODBC][Ora]ORA-00942:table or view does not exist(#942)

Possible duplicate of [Access VBA Parameter in passthrough query to SQL Server](https://stackoverflow.com/questions/44307844/access-vba-parameter-in-passthrough-query-to-sql-server) — Andre, May 09 '19 at 23:21

Albert D. Kallal · Accepted Answer · 2019-05-14T23:48:20.953

3

Well, since the sql is sent "raw" to Oracle, then of course the server side database has no idea what to do with a VBA function.

So, one possible solution would be to re-create the VBA function as a scaler oracle function.

However, because that given function has no paramters, then we can assume that the function returns a given value - darn near close to a static, or a value that you wish/want to pass to oracle.

The approach then means we have to resolve the function client side BEFORE we attempt to use or execute that PT query.

So, I recommend that you take the above PT query, and copy it. (access side). You now have two PT queries.

Now, in code, we grab the sql, modify it, shove it into the 2nd query, and now you are free to launch + use that pass-though query (for a report, recordsets, forms or whatever)

So, your code will look like this:

Sub MyOraclePT()

  Dim strSQL     As String

  strSQL = CurrentDb.QueryDefs("PT1").SQL    ' <-- this change

  strSQL = Replace(strSQL, "GET_QAQC_JOB()", GET_QAQC_JOB())

  CurrentDb.QueryDefs("PT2").SQL = strSQL

  ' now you can open or use this query.

  '
  Dim rst     As DAO.Recordset
  Set rst = CurrentDb.OpenRecordset("PT2")

  ' or open a report/form based on that PT2 query
  ' such as
  DoCmd.OpenReport "MyReport", acViewPreview


End Sub

So, we used two PT query, because the first one is the sql you have as a above. We then modify the 2nd PT query to replace the function value with the actual value of the function.

The above assumes the function is a number (not a string). If the column CC_QAQC_SPEC_MASTER.JOBNUMBER was a string, then you would could/would place single quotes around the function name in the first PT query.

I also note a bug/syntax error, as you have:

WHERE 
    CC_QAQC_SPEC_MASTER.JOBNUMBER)=GET_QAQC_JOB()

In above, I see a stray ")" in above - you want to fix that.

edited May 14 '19 at 23:48

answered May 10 '19 at 00:31

Albert D. Kallal

42,205
3
34
51

1

For anyone else searching for a general solution to the issue, please note that this will only work as long as the function does not take any table data as parameters. – SunKnight0 May 10 '19 at 14:58
I'm a novice when it comes to VBA so please bear with me. Where do I put the "Sub MyOraclePT()" at? Am I just creating a new Module and sticking that in there? I have several queries that I'm wanting to convert to PT, but each one of them use that jobnumber function. – house May 10 '19 at 19:31
Code should run just fine if placed in a standard Access code module (not a class module). The example also assumes you created two pass-though queries. The first PT query is not required, but I just thought it would be less hassle to have the SQL saved. We use two PT query, because I am modifying the 2nd one, and thus the function name is replaced with the actual "value" that the function returns. So, next time around, the function name is gone from the raw sql text. So, each time, I grab fresh unmodified SQL from PT1, and modify it, shove it into PT2 - then reports or whatever can use it. – Albert D. Kallal May 11 '19 at 08:42
I copied the above code and when I compile it I get a "type mismatch" error on the first strSQL. – house May 14 '19 at 15:43
Ah, ok. I fixed above. I am grabbing the SQL, so I need the SQL property. See above edit: – Albert D. Kallal May 14 '19 at 23:47
@Albert, I'm not exactly sure what to do next. I created a module and named it PT. I copied the code above down to where it starts with CurrentDb.QueryDefs and then added End Sub. I'm assuming that's all that should be in the module, but maybe I'm wrong. I'm not sure how to execute this code. I have several queries that I want to make as PT's and I have several forms and reports that will load based on those queries. This is also a mutli-user environment along with separate "jobnumbers" that multiple users can be working on at the same time. I apologize for the lack of knowledge. – house May 15 '19 at 00:44
And you created the two PT queries? After you run the code, then your second PT query should have the actual value. You should be able after running that code click on the PT query from the nav pane, and it should run. At this point you can use the PT2 query for a report, form, or even the source of a recordset in VBA code. – Albert D. Kallal May 15 '19 at 15:02
Your database is assumed to be split. Each workstation gets a copy of your Access application. So, this code will setup PT2, and then you can launch a form, or report or whatever you want that will be based on PT2. – Albert D. Kallal May 15 '19 at 15:03
@AlbertD.Kallal Yes, I have PT1 and PT2. I created a module called Module1 with the code below. How do I get that module to execute? ***Sub MyOraclePT() Dim strSQL As String strSQL = CurrentDb.QueryDefs("PT1").SQL ' <-- this change strSQL = Replace(strSQL, "GET_QAQC_JOB()", GET_QAQC_JOB()) CurrentDb.QueryDefs("PT2").SQL = strSQL End Sub*** – house May 15 '19 at 20:01
I figured out how to do it. Thanks for your help Albert!! This will be a huge help! – house May 16 '19 at 15:54
@AlbertD.Kallal I went through my database and created all of the PT queries and then changed the forms record source to the new PT query. I'm running into two issues. The first one is that a sub-form will not display it's info because the linked fields are incorrect and it won't let me select new ones. The 2nd issue is that when I try adding records or editing data it's telling me the recordset is not updatable. – house May 20 '19 at 20:36
but why are you wasting all that effort to make all kinds of PT queries? You in 9 out of 10 cases don't need (or want) to use a PT query. They are read only. Read this post of mine, and read it several times: https://www.utteraccess.com/forum/index.php?showtopic=2053744&hl= – Albert D. Kallal May 21 '19 at 01:03
That makes sense. I was thinking that as well yesterday when I was messing around with it. Thanks for setting me straight. ;) – house May 21 '19 at 13:09

Adam · Answer 2 · 2019-05-11T10:28:29.397

Assuming the function is a function written in VBA in Access, you can't call it from the query. I believe the DML in the query is sent in its entirety to the source system, Oracle in this case. Oracle has no idea what the function is and errors.

Option-1: Submit the Query via ADO.NET in VBA

Abandon the pass-thru query objects in Access. Execute the query from VBA connecting to Oracle via ADO or something like it. There are lots of resources on how to use ADO to pull data from external data sources such as How To Open ADO Connection and Recordset Objects. Here is an example using DAO.

Option-2: Wrap the Pass-thru Query in Another Query

Access lets you create queries that call other queries. Create the pass-thru query without the WHERE predicate. This is the pass-thru query. Create another access query that calls the pass-thru query. This is the wrapping query. The wrapping query (since its native Access SQL) should have the parameter you use to filter the result set.

Complete disclosure. I didn't try this with Oracle.

Now, if the pass-thru query is grabbing a lot of data. This option won't perform well.

Option-3: Dynamically Create the Pass-Thru Query

You have an event (button click or whatever) call a VBA sub-procedure, which dynamically creates and assigns the SQL for the query:

Public Sub foo()
    Let qaqc_job_number = GET_QAQC_JOB()
    Set Query = CurrentDb.QueryDefs("<your-pass-thru-function-name>")

    Let sql_job_data = "SELECT" & _
                       "CC_QAQC_SPEC_MASTER.JOBNUMBER, " & _
                       "CC_QAQC_SPEC_MASTER.SPECSECTION, " & _
                       "CC_QAQC_SPEC_MASTER.SPECDESCRIPTION, " & _
                       "CC_QAQC_SPEC_MASTER.ID " & _
                       "FROM " & _
                       "CC_QAQC_SPEC_MASTER " & _
                       "WHERE " & _
                       "CC_QAQC_SPEC_MASTER.JOBNUMBER)= " & qaqc_job_number & " " & _
                       "Order BY " & _
                       "CC_QAQC_SPEC_MASTER.SPECSECTION, " & _
                       "CC_QAQC_SPEC_MASTER.SPECDESCRIPTION; "

    Let Query.Sql = sql_job_data

End Sub

Then you run the query.

Everything in the SQL you stick in this Access query object has to exist in Oracle and ONLY in Oracle.

Use Access function in SQL pass-through query

2 Answers2