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)

Answer 1

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.