Pass a MS Access form date to Oracle via a Pass-Through Query

I have a PT query that works fine with a fixed date:

    SELECT MMLD.MMLD_CREDENTIAL_AUDIT.OLD_CREDENTIAL_ID, Max(MMLD.MMLD_CREDENTIAL_AUDIT.OLD_LAST_STATE_CHANGE) AS MaxOfOLD_LAST_STATE_CHANGE
    FROM MMLD.MMLD_CREDENTIAL_AUDIT
    WHERE (((MMLD.MMLD_CREDENTIAL_AUDIT.OLD_LAST_STATE_CHANGE)>= TO_DATE('1/1/2023', mm/dd/yyyy')) AND ((MMLD.MMLD_CREDENTIAL_AUDIT.OLD_STATE_ID)<>NEW_STATE_ID AND (MMLD.MMLD_CREDENTIAL_AUDIT.OLD_STATE_ID)=24))
    GROUP BY MMLD.MMLD_CREDENTIAL_AUDIT.OLD_CREDENTIAL_ID

However because the date changes regularly by user I want the PT query to read a date variable from a textbox. I pursued the QueryDef approach as recommended in SO topic:

Passing a MS Access Form date into an Oracle SQL

and the VBA code has no errors:

    Dim strSQL As String
    Dim startDate As Date

    startDate = Forms!frm40Aging!tbxFrom.Value

    strSQL = "SELECT MMLD.MMLD_CREDENTIAL_AUDIT.OLD_CREDENTIAL_ID, Max(MMLD.MMLD_CREDENTIAL_AUDIT.OLD_LAST_STATE_CHANGE) AS MaxOfOLD_LAST_STATE_CHANGE " & _
    "FROM MMLD.MMLD_CREDENTIAL_AUDIT " & _
    "WHERE (((MMLD.MMLD_CREDENTIAL_AUDIT.OLD_LAST_STATE_CHANGE)>= TO_DATE(STARTDATE,'mm/dd/yyyy')) AND ((MMLD.MMLD_CREDENTIAL_AUDIT.OLD_STATE_ID)<>NEW_STATE_ID AND (MMLD.MMLD_CREDENTIAL_AUDIT.OLD_STATE_ID)=24)) " & _
    "GROUP BY MMLD.MMLD_CREDENTIAL_AUDIT.OLD_CREDENTIAL_ID "

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

    CurrentDb.QueryDefs("qryPToutofAIPQEB").Execute

When I run the code, I get an error

Can't run a SELECT query

and debugs at the last line .Execute. The VBA process of updating the PT query works fine and overwrites my PT query with this code but fails on execution. That is puzzling to me since it fails at execution level only.

I had always thought I can't use VBA to execute a select query, and it seems I cannot. I’m puzzled because the example in the other SO post is a SELECT query as well.

Please help and if there is another approach that gets me around the VBA restriction and allows me to use a form date in my PT query, I would be grateful. I can use a 2 query approach just fine (the PT with the larger data set, and a regular MS Access query on the PT query for a smaller subset based on the form input), but that isn't the goal. That would mean I have to settle which I don't prefer to do.

Solution

The error

Can't Execute a Select Query

is because the Execute method can't be used when the command returns a recordset.

In those cases you need to use the OpenRecordset method: https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/querydef-openrecordset-method-dao

Eg:

Const DB_PATH As String = "C:\Temp\Northwind.mdb"
Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim rs As DAO.Recordset
 
Set db = OpenDatabase(strDbPath)
Set qry = db.QueryDefs("Invoices")
Set rs = qry.OpenRecordset