Error 3075 Syntax Error but Syntax seams fine

[Zedster]

I have a form with a project selection button. A user selects a project then clicks a button. When this happens a pass through SQL query is produced.

The code is here:

Private Sub cmdIssueList_Click()
On Error GoTo err_cmdIssueList_Click

    Dim strSql As String
    Dim strQdfName As String
     
    If IsNull(Me!cboSelectProject) Then
        MsgBox "Please select a primary project before proceeding"
    Else
        Gbl_Project = Me!cboSelectProject.Value
       
        strQdfName = "qdfIssueListByProject"
        strSql = "SELECT i.I_ID As [ID], "
        strSql = strSql & "i.I_Title As [Title], "
        strSql = strSql & "i.I_Severity As [Sev], "
        strSql = strSql & "i.I_LastUpdated As [Last Update], "
        strSql = strSql & "i.I_Status As [Status], "
        strSql = strSql & "e.Employee As [Champion], "
        strSql = strSql & "i.I_TargetDate As [Target], "
        strSql = strSql & "i.I_System As [System], "
        strSql = strSql & "i.I_Component As [Component] "
        strSql = strSql & "FROM tblIssues AS i INNER JOIN tblProjectIssues AS pi ON i.[I_ID] = pi.[I_ID] LEFT JOIN V_Employees e ON i.[I_Champion] = e.[StaffNo] "
        strSql = strSql & "WHERE pi.P_ID='" & Me!cboSelectProject.Value & "'; "
       
        Debug.Print strSql
       
        Call CreateNamedQDF(strQdfName, strSql)
       
        DoCmd.OpenForm "frmIssuesList", acNormal, , , , acWindowNormal
    End If

exit_cmdIssueList_Click:
    Exit Sub
   
err_cmdIssueList_Click:

        MsgBox "Error No: " & Err.Number & vbCrLf & "Description: " & Err.Description & vbCrLf & "Error Line: " & Erl
        Call Logger("Error", "risks-" & cstLatestVersion & ".Form_frmSwitchboard.cmdIssueList_Click", Erl, Err.Number, Err.Description)
        Resume exit_cmdIssueList_Click
   
End Sub

When executed I get the following Error:

When I copy and paste the SQL produced by debug.print (shown below) it runs fine both in access as a query and also within SSMS, suggesting the syntax is fine. Furthermore despite generating the error the code actually produces the required query, which runs fine. So I don't understand why am I getting the error.

SELECT i.I_ID As [ID], i.I_Title As [Title], i.I_Severity As [Sev], i.I_LastUpdated As [Last Update], i.I_Status As [Status], e.Employee As [Champion], i.I_TargetDate As [Target], i.I_System As [System], i.I_Component As [Component] FROM tblIssues AS i INNER JOIN tblProjectIssues AS pi ON i.[I_ID] = pi.[I_ID] LEFT JOIN V_Employees e ON i.[I_Champion] = e.[StaffNo] WHERE pi.P_ID='UsersProject';

V_Employees is a view on the SQL server.

 

[cheekybuddha]

You'll need to post the code in CreateNamedQDF()

Also, it gives a line number where the error occurs, but you show no line numbers in your code.

 

[Zedster]

Sorry about that function code below, I have made a few changes it now occurs on line 70. Which suggests it doesn't like query, but as I say if i copy the debug.print of the query into a new query in Access it runs fine. Also if I oaste the query into a new SQL query in SSMS it runs fine there too.

Public Sub CreateNamedQDF(strNameOfQDF As String, strSql As String)

10    On Error GoTo err_CreateNamedQDF

          Dim db As Database
          Dim qdf As QueryDef
          Dim rst As Recordset
          Dim strServer As String: strServer = "xxx\yyy"
          Dim strDatabase As String: strDatabase = "Issues"
20        Dim strConnection As String: strConnection = "ODBC;DRIVER=SQL Server;SERVER=" & strServer & ";DATABASE=" & strDatabase & ";Trusted_Connection=Yes"
30        Debug.Print strConnection
40        Call KillQdfIfExists(strNameOfQDF)
          
50        Debug.Print strSql

60        Set db = CurrentDb
70        Set qdf = db.CreateQueryDef(strNameOfQDF, strSql)
80        qdf.Connect = strConnection
90        qdf.ReturnsRecords = True
100       Set rst = qdf.OpenRecordset
          
110       rst.Close
120       db.Close
130       Set rst = Nothing
140       Set qdf = Nothing
150       Set db = Nothing
          
exit_CreateNamedQDF:
160       Exit Sub
          
err_CreateNamedQDF:

170           MsgBox "Error No: " & Err.Number & vbCrLf & "Description: " & Err.Description & vbCrLf & "Error Line: " & Erl
180           Call Logger("Error", "Issues.basFunctions.CreateNamedQDF", Erl, Err.Number, Err.Description)
190           Resume exit_CreateNamedQDF

End Sub

 

[cheekybuddha]

Try setting the .Connection before the SQL, so the querydef knows it's a pass-through query.

The SQL is invalid in Access SQL (in Access you have to nest your joins in parentheses ).

In other words, try adjusting like:

' ...
60        Set db = CurrentDb
70        Set qdf = db.CreateQueryDef(strNameOfQDF)
80        qdf.Connect = strConnection
85        qdf.SQL = strSQL
90        qdf.ReturnsRecords = True
100       Set rst = qdf.OpenRecordset
' ...

(NB Untested!)

 

[Zedster]

David, you are a star, thank you very much, I have spent far too much time trying to track down the problem, but one simple change was all that was needed. I will try to remember that one!

 

[cheekybuddha]

Glad it worked!

Also, I think lines 100, 110, 120, 130 and the rst declaration are unnecessary in this sub.