title: Access VBA Parameter in passthrough query to SQL Server
body: <p>I have several queries in an MS Access database. Some of these use parameters. I use the following code in VBA to provide the query with these parameters:</p>
  
  <p><strong>VBA</strong></p>
  
  <pre class="lang-vb prettyprint-override"><code>Dim startDate As Date
  Dim endDate As Date
  
  Dim dbs As DAO.Database
  Dim qdf As DAO.QueryDef
  Dim rst As DAO.Recordset
  
  If IsNull(Me.dpFrom) Or IsNull(Me.dpTo) Then
      MsgBox "Please select a date!"
  ElseIf (Me.dpFrom.Value &gt; Me.dpTo.Value) Then
      MsgBox "Start date is bigger than the end date!"
  Else
      startDate = Me.dpFrom.Value
      endDate = Me.dpTo.Value
  
      Set dbs = CurrentDb
  
      'Get the parameter query
          Set qdf = dbs.QueryDefs("60 Dec")
  
          'Supply the parameter value
          qdf.Parameters("startDate") = startDate
          qdf.Parameters("endDate") = endDate
  
          'Open a Recordset based on the parameter query
          Set rst = qdf.OpenRecordset()
  
              'Check to see if the recordset actually contains rows
          If Not (rst.EOF And rst.BOF) Then
              rst.MoveFirst 'Unnecessary in this case, but still a good habit
              Do Until rst.EOF = True
                  'Save contact name into a variable
                  Me.tbBUDdec.Value = rst!Som
                  rst.MoveNext
                  Me.tbLEYdec.Value = rst!Som
                  rst.MoveNext
                  Me.tbMDRdec.Value = rst!Som
                  rst.MoveNext
                  Me.tbODCdec.Value = rst!Som
                  rst.MoveNext
              Loop
          Else
              MsgBox "There are no records in the recordset."
          End If
          rst.Close 'Close the recordset
          Set rst = Nothing 'Clean up
  </code></pre>
  
  <p><strong>Access Query</strong></p>
  
  <pre class="lang-sql prettyprint-override"><code>PARAMETERS startDate DateTime, endDate DateTime;
  SELECT WarehouseCode, COUNT(DeliveryPoint) AS Som
  FROM [50 resultaat]
  WHERE EntryDate between [startDate] and [endDate]
  GROUP BY WarehouseCode;
  </code></pre>
  
  <p>This is working fine. However, I am now trying to use the same code to call a passthrough query to a SQL server. This query uses a different syntax to declare and set the parameters:</p>
  
  <p><strong>SQL Server query</strong></p>
  
  <pre class="lang-sql prettyprint-override"><code>DECLARE @InvLineEntryDateBegin AS date
  DECLARE @InvLineEntryDateEnd AS date
  SET @InvLineEntryDateBegin = '2017-01-01'
  SET @InvLineEntryDateEnd = '2017-05-31'
  
  Select WarehouseCode, Count(PickOrderNr) as Som
  FROM ( bla bla bla ...
  </code></pre>
  
  <p>I can't get my VBA code to work with the different SQL syntax. I've read several options but couldn't find anything concrete. Does anyone have experience with this query structure?  </p>
  
  <p>In other words: How can I, in VBA, insert parameters in a stored procedure that queries on a SQL server?</p>