MS Access using forms in a pass-through query

Question

I have a form named FietsAantDagen, a query named QueryFietsAantDagen and a textbox named Txtinput. I am trying to use a pass-through query to SQL Server and use a text form's input as a input in my query.

Query:

SELECT
   Fiets_id,
   Fiets_Type,
   SUM(DATEDIFF(DAY, Huurovereenkomst_Begin_datum, Huurovereenkomst_Eind_datum)) AS AantalDagen 
FROM
   Fiets 
   INNER JOIN
      HuurovereenkomstFiets 
      ON HuurovereenkomstFiets_Fiets_id = Fiets_id 
   INNER JOIN
      Huurovereenkomst 
      ON Huurovereenkomst_id = HuurovereenkomstFiets_Huurovereenkomst_id 
WHERE
   YEAR(Huurovereenkomst_Begin_datum) = [Forms]![FietsAantDagen]![Txtinput] 
   AND YEAR(Huurovereenkomst_Eind_datum) = [Forms]![FietsAantDagen]![Txtinput] 
GROUP BY
   Fiets_id,
   Fiets_Type

While running this query as a pass-through query I get the following error:

ODBC: Runtime error [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'WHERE'/ (#156)

Is the problem that I am using an Access text form value in a pass-through query, if so what can I do to solve it?

I read in another Overflow question you needed to add (), which I did and now I get the error:

JOIN-expression is not supported.

I'm going crazy...

Answer 1

If you don’t have direct use of SQL server, then likely best to create two pass though queries.

Query #1 – this is your raw SQL as you have Eg:

SELECT
    Fiets_id,
    Fiets_Type,
    SUM(DATEDIFF(DAY, Huurovereenkomst_Begin_datum,
    Huurovereenkomst_Eind_datum)) AS AantalDagen 
FROM
   Fiets 
INNER JOIN
  HuurovereenkomstFiets 
  ON HuurovereenkomstFiets_Fiets_id = Fiets_id 
INNER JOIN
  Huurovereenkomst 
  ON Huurovereenkomst_id = HuurovereenkomstFiets_Huurovereenkomst_id 
WHERE
  YEAR(Huurovereenkomst_Begin_datum) = [StartYear]
  AND YEAR(Huurovereenkomst_Eind_datum) = [EndYear]
GROUP BY
  Fiets_id,
   Fiets_Type

Query #2 – this is an application wide query you make that you can re-use over and over for any raw t-SQL (SQL server pass though). You then in code go like this:

Dim rst     As DAO.Recordset
Dim strSQL  As String

strSQL = CurrentDb.QueryDefs("MyQ1").SQL
srtSQL = Replace(strSQL, "[YearStart]", [Forms]![FietsAantDagen]![Txtinput])
strSQL = Replace(strSQL, "[YearEnd]", [Forms]![FietsAantDagen]![Txtinput])
With CurrentDb.QueryDefs("qryPassR")
   .SQL = strSQL
  .ReturnsRecords = True
  Set rst = .OpenRecordset
End With

However, if you have the ability to use SQL server, and create a proc, then I would suggest you create store procedure like this:

CREATE PROCEDURE SelectDates
@StartYear int,
@EndYear int
AS
BEGIN
SET NOCOUNT ON;
 SELECT
   Fiets_id,
   Fiets_Type,
   SUM(DATEDIFF(DAY, Huurovereenkomst_Begin_datum, Huurovereenkomst_Eind_datum)) AS AantalDagen 
FROM
Fiets 
   INNER JOIN
    HuurovereenkomstFiets 
  ON HuurovereenkomstFiets_Fiets_id = Fiets_id 
INNER JOIN
   Huurovereenkomst 
   ON Huurovereenkomst_id = HuurovereenkomstFiets_Huurovereenkomst_id 
WHERE
YEAR(Huurovereenkomst_Begin_datum) = @StartYear
AND YEAR(Huurovereenkomst_Eind_datum) = @EndYear
GROUP BY
   Fiets_id,
   Fiets_Type

END

Then in access, you use this:

Dim rst     As DAO.Recordset

With CurrentDb.QueryDefs("qryPassR")
   .SQL = "exec SelectDates " & [Forms]![FietsAantDagen]![Txtinput] & "," & _
          [Forms]![FietsAantDagen]![Txtinput]
  .ReturnsRecords = True
  Set rst = .OpenRecordset
End With

So by passing parameters you reduce most of issues in regards to SQL injection. Of course if you can’t create store procs, or don’t have permissions, then you have to adopt the first idea above. You can also of course in the first suggestion insert the raw SQL into the code editor, but I find using up an extra query to “just” hold the raw SQL, and then modifying the SQL into the 2nd pass-through query eliminates the need for messy SQL in the VBA code editor.