One way to pass parameters to a pass-through query is to modify the
QueryDefobject's SQL property in code before running it, concatenating the parameter values you want to send.
I've chosen this method, rather than using stored procedures, because I do not have adequate privileges (
create function) for creating stored procedures in the Oracle database.
I'm familiar with scripting, but I don't pretend to be an expert coder.
I figure I might as well get some input from those who know what they're doing, learn from you, and get off on the right foot. I've heard that non-experts who attempt to program in Access often end-up with these barely working, horribly designed applications that bring dread into the hearts of developers.
Obviously, I'd prefer to avoid that. Here's what I've got:
'SPT stands for SQL Pass-through (query). My pass-through query is called sptROAD_INSP Private Sub cmdSubmit_Click() AssembleSptROAD_INSP Me.txtStartYear, Me.txtEndYear End Sub
Option Compare Database Option Explicit Public Sub AssembleSptROAD_INSP( _ intStartYear As Integer, _ intEndYear As Integer) Dim strSQL As String Dim strSPTName As String strSPTName = "sptROAD_INSP" strSQL = "select " _ & "b.insp_id, " _ & "b.road_id, " _ & "b.insp_date, " _ & "b.Condition " _ & "from " _ & "user1.road_insp b " _ & "where " _ & "b.insp_date = ( select max(insp_date) from user1.road_insp a " _ & "where a.road_id = b.road_id " _ & "and extract(year from insp_date) between " _ & intStartYear & " and " & intEndYear & ");" Call ModifyPassThrough(strQdfName:=strSPTName, strSQL:=strSQL) DoCmd.Close acQuery, "sptROAD_INSP" DoCmd.OpenQuery strSPTName End Sub
Public Sub ModifyPassThrough( _ ByVal strQdfName As String, _ ByVal strSQL As String, _ Optional varConnect As Variant, _ Optional fRetRecords As Boolean = True) ' Modifies pass-through query properties ' Inputs: ' strQdfName Name of the query ' strSQL New SQL string ' varConnect Optional connection string ' fRetRecords Optional returns records-- ' defaults to True (Yes) Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim strConnect As String Set db = CurrentDb Set qdf = db.QueryDefs(strQdfName) If IsMissing(varConnect) Then strConnect = qdf.Connect Else strConnect = CStr(varConnect) End If qdf.Connect = strConnect qdf.ReturnsRecords = fRetRecords qdf.SQL = strSQL End Sub
What can I do to make this as sustainable and robust as possible?