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

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

I have a PT query that works 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

I want the PT query to read a date variable from a textbox.

I pursued the QueryDef approach as recommended in Passing a MS Access Form date into an Oracle SQL

The VBA code:

    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

I get an error

Can't run a SELECT query

at the last line .Execute.

The VBA process of updating the PT query works and overwrites my PT query.

I had always thought I couldn'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.
Is there another approach that allows a form date in my PT query?

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