title: Access pass-through query with parameters not updating with prompted parameters
body: <p>An Access pass-through query works when using the default parameters.  When used in an Access report, the prompts that are used returns records based on the default parameters in the ptq and not the answered prompts.  Default data is being returned.</p>
  
  <p>I have a SQL Server based stored procedure that works, <code>uspWorkCentreReport</code>, that uses <code>@TheDate DATE, @WC VARCHAR(15), @Shift INT</code> for parameters and returns, through a SELECT statement, these columns:</p>
  
  <pre><code>[JOB NUMBER], [REL #], [JOB NAME], QTY.  
  </code></pre>
  
  <p>Here's the ALTER line of the stored procedure code:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[uspWorkCentreReport]
       @TheDate DATE,
       @WC VARCHAR(15),
       @Shift INT
  </code></pre>
  
  <p>The Access pass-through query, ptq_uspWorkCentreReport, passes these default parameters '2019-05-30','PCOT',1 and uses a DSN-less ODBC connection that works to return default data.  I forgot to try but I think it will return correct data with whatever default parameters I use to replace '2019-05-30','PCOT',1.  EDIT - I tried it this morning and indeed any appropriate replacement parameters return the appropriate associated records.  Here's the ptq's one line:</p>
  
  <pre><code>exec uspWorkCentreReport '2019-05-30','PCOT',1
  </code></pre>
  
  <p>I provide the ptq with default parameters based on <a href="https://stackoverflow.com/questions/44307844/access-vba-parameter-in-passthrough-query-to-sql-server">Albert D. Kallal's SO reply</a>.</p>
  
  <p>I use an Access select query, qry_ptq_uspWorkCentreReport, to receive [JOB NUMBER],[REL #],[JOB NAME],QTY and pass the parameters TheDate, set to Date With Time, WC, set to Short Text, and Shift, set to Integer.  </p>
  
  <p>qry_ptq_uspWorkCentreReport uses the pass-through query.  The parameters are set using Access' Parameters applet and not within the query fields.  Running this select query prompts for the 3 parameters but only returns data based on the default parameters set in the ptq's one line.  I did not think to look at the Access SQL statement but will do so when I get to work tomorrow morning.  EDIT - Here's the SQL statement for qry_ptq_uspWorkCentreReport:</p>
  
  <pre><code>PARAMETERS TheDate DateTime, WC Text ( 255 ), Shift Short;
  SELECT ptq_uspWorkCentreReport.[JOB NUMBER], ptq_uspWorkCentreReport.[REL #], ptq_uspWorkCentreReport.[JOB NAME], ptq_uspWorkCentreReport.QTY
  FROM ptq_uspWorkCentreReport;
  </code></pre>
  
  <p>Of course the above three functions culminate in an Access report, rpt_qry_ptq_WorkCentreReport to make the records human readable.</p>
  
  <p>I have used the same scenario for another report the takes From and To dates as parameters.  When that report runs, the prompts take the dates and return records based on those dates and not the dates in the ptq.  Here's that ptq:</p>
  
  <pre><code>exec uspMergeAandPJobs '2018-01-01','2019-01-01'
  </code></pre>
  
  <p>Indeed, I tried using</p>
  
  <pre><code>exec uspMergeAandPJobs '',''
  </code></pre>
  
  <p>And the report returns 0 records!</p>
  
  <p>Not sure what I am missing and would appreciate any feedback.  TIA.</p>
  
  <p>I tried the following with the help of a tutor:</p>
  
  <pre><code>Sub Report_Load()
  
      Dim strFromDate     As String
      Dim strToDate       As String
      Dim strWC           As String
      Dim intShift        As Integer
      Dim strSQL          As String
  
      strFromDate = InputBox("From Date and Time: ")
      strToDate = InputBox("Enter To Date and Time: ")
      strWC = InputBox("Enter Work Center: ")
      intShift = InputBox("Enter Shift: ")
  
      Dim qdf As DAO.QueryDef, rst As DAO.Recordset
      Set qdf = CurrentDb.CreateQueryDef("")
      qdf.SQL = "exec dbo.uspWorkCentreReport " &amp; "'" &amp; strFromDate &amp; "', " &amp; "'" &amp; strToDate &amp; "', " &amp; "'" &amp; strWC &amp; "', " &amp; intShift &amp; ";"
  
      qdf.Connect = "ODBC;DRIVER=ODBC Driver 13 for SQL Server;SERVER=OURS\NTSQL;Trusted_Connection=Yes;DATABASE=TablesCoE;ApplicationIntent=READONLY;"
  
      qdf.ReturnsRecords = True
      Set rst = qdf.OpenRecordset
  
      rst.Close
      Set rst = Nothing
      Set qdf = Nothing
  
  End Sub
  </code></pre>
  
  <p>After the prompts VBA spits up a Run-Time error 3129 - Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.  Neither of us were able to determine what was causing the error.  In VBA the "qdf.SQL..." line is highlighted in yellow.</p>
  
  <p>EDIT - Adding stored proc's SQL code:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[uspWorkCentreReport_TEST] @FromDate DATETIME,@ToDate DATETIME,@WC VARCHAR(15),@Shift INT
  
  AS
  
  BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
  
  --  Build table variable SumTable structure
  DECLARE @SumTable TABLE(matl_nbr VARCHAR(60),QTY DECIMAL(4,0),matl_dsc VARCHAR(50))
  
  --  P jobs and their summed WorkCentre traversals using crosstab - each traversal is added up
  INSERT INTO @SumTable(matl_nbr,matl_dsc,QTY)
  SELECT     SRC1.matl_nbr,SRC1.matl_dsc,
      SUM(CASE WHEN SRC1.locn_to = @WC THEN 1 ELSE 0 END) AS QTY
  FROM 
  (
  SELECT matl_nbr,matl_dsc,locn_to
  FROM mtrk_CompanyE.dbo.trxn_hstd th
  WHERE (last_upd &gt;= @FromDate AND last_upd &lt;= @ToDate) AND
      locn_to = @WC
  )SRC1
  GROUP BY matl_nbr,matl_dsc
  
  --  These updates take all the summed WorkCentre (locn_to) columns and turn each into "1" for later summing
  UPDATE @SumTable
  SET QTY = 1 
  WHERE QTY &gt;1
  
  --  Shortening the material number from 123456_00_00_R1_00 to 1234560
  UPDATE @SumTable 
  SET matl_nbr = LEFT(matl_nbr,6) + right(LEFT(matl_nbr,9),1)
  
  SELECT LEFT(A.matl_nbr,6)[JOB NUMBER],SUBSTRING(A.matl_nbr,7,1)[REL #],matl_dsc AS [JOB NAME],QTY
  FROM (SELECT matl_nbr,matl_dsc,
          SUM(CASE WHEN QTY = 1 THEN 1 ELSE NULL END) AS QTY
  FROM @SumTable
  GROUP BY matl_nbr,matl_dsc)A
  ORDER BY QTY DESC;
  
  END
  </code></pre>
  
  <p>EDIT - Finished sub:</p>
  
  <pre><code>Private Sub Report_Open(Cancel As Integer)
  
      Dim strFromDate     As String
      Dim strToDate       As String
      Dim strWC           As String
      Dim intShift        As Integer
      Dim strSQL          As String
  
      strFromDate = InputBox("Enter From Date and Time: ")
      strToDate = InputBox("Enter To Date and Time: ")
      strWC = InputBox("Enter Work Center: ")
      intShift = InputBox("Enter Shift: ")
  
      strSQL = "exec dbo.uspWorkCentreReport_TEST " &amp; "'" &amp; strFromDate &amp; "', " &amp; "'" &amp; strToDate &amp; "', " &amp; "'" &amp; strWC &amp; "', " &amp; intShift &amp; ";"
  
      CurrentDb.QueryDefs("ptq_uspWorkCentreReport").SQL = strSQL
  
      DoCmd.OpenReport "rpt_qry_ptq_uspWorkCentreReport", acViewReport
  
      Me.lblFromDate.Caption = strFromDate
      Me.lblToDate.Caption = strToDate
      Me.lblWC.Caption = strWC
      Me.lblShift.Caption = intShift
  
  End Sub
  </code></pre>