Problem running passthrough recursive query as QDF in VBA

[Zedster]

I have a SQL recursive query (below), which runs fine on SQL server and also runs fine when I create a QDF in in MS Access navigation window.

But when I try to create it in VBA I get an error "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'."

The query that runs fine in SSMS on the server is:

WITH subs AS (
SELECT StaffNo,FN,LN,ReportsTo
FROM tblStaff
WHERE StaffNo = '47472'
UNION ALL
SELECT e.StaffNo,e.FN,e.LN,e.ReportsTo
FROM tblStaff e INNER JOIN subs s
ON e.ReportsTo = s.StaffNo
)
SELECT * FROM subs;
GO

The VBA code I am using to create it on the fly that fails with the error message is:

On Error GoTo err_AllSubordinates

    Dim db As Database
    Dim qdf As QueryDef
    Dim rst As Recordset
    Dim stServer As String
    Dim stDatabase As String
    Dim strServer As String: strServer = "base_server\custom_apps"
    Dim strDatabase As String: strDatabase = "employees"
    Dim strConnection As String: strConnection = "ODBC;DRIVER=SQL Server;SERVER=" & strServer & ";DATABASE=" & strDatabase & ";Trusted_Connection=Yes"
    Dim strSQL As String
    Dim strOutput As String
    Dim strRecurse As String
    Debug.Print strConnection
    
    strSQL = "WITH subs AS ("
    strSQL = strSQL & "SELECT StaffNo,FN,LN,ReportsTo "
    strSQL = strSQL & "FROM tblStaff "
    strSQL = strSQL & "WHERE StaffNo = '47472' "
    strSQL = strSQL & "UNION ALL "
    strSQL = strSQL & "SELECT e.StaffNo,e.FN,e.LN,e.ReportsTo "
    strSQL = strSQL & "FROM tblStaff e INNER JOIN subs s "
    strSQL = strSQL & "ON e.ReportsTo = s.StaffNo) "
    strSQL = strSQL & "SELECT StaffNo FROM subs;"
    Debug.Print strSQL

    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("")
    qdf.SQL = strSQL

When I copy the output from the debug.print strSQL into SSMS it runs fine.

Any ideas?

 

[Minty]

That's using a CTE (Common table expression) which, as far as I'm aware, that can't work in native Access SQL.

 

[Zedster]

It works fine when I write it as a passthough query in Navigator so I would have expected it to work fine in VBA also:

 

[Minty]

No - a pass-through does exactly that - simply passes a string of instructions to the server. They aren't parsed or anything else.
Try it - create a pass-through like this;

Select LeastImportant(FlibblyGibbet) From NonexistantTable

And it will be passed as-is to the Server.
Access doesn't know or care that LeastImportant() isn't a valid function or that the table or field don't exist.

 

[Zedster]

I confess to being confused. I thought if I create a query then from design tab select passthrough, I then create the connection string to the SQL server, then write the query in TSQL as opposed to ACE/JET SQL and it appeared with a world globe (like shown below) and it ran OK I had created a passthrough query.

Are you saying that isn't a passthrough query?

 

[Minty]

Yes - and no... Your statement above is correct, but that's not what you are doing with your VBA code.
You are creating a normal query def with this line;

Set qdf = db.CreateQueryDef("")

I think you may need to also specify

qdf.ReturnsRecords = True

to make access realise it's a pass-through query.
Alternatively, simply create and save a generic pass-through query, then alter the SQL and run it.

 

[Isaac]

Alternatively, simply create and save a generic pass-through query, then alter the SQL and run it.

+1 for that method. Don't worry about making a pass thru query using code, just create an empty bucket and adjust its .SQL property

 

[Minty]

I have a little Sub call for it;

Public Sub SendToPT_Generic(strQuery As String, bRetRecs As Boolean) 

    Dim db As DAO.Database
    Dim qDEF As QueryDef
    
    Set db = CurrentDb()
    
    Set qDEF = db.QueryDefs("qPT_Generic")
    qDEF.Connect = db.TableDefs("tb_Employees").Connect    ' Change to a remote table name in the server
    qDEF.SQL = strQuery
    qDEF.ReturnsRecords = bRetRecs
    
    If Not bRetRecs Then
        db.Execute "qPT_Generic", dbSeeChanges
    Else
        qDEF.Close
    End If
    
    Set qDEF = Nothing
    Set db = Nothing
    
Exit Sub

So create your SQL String, pass it in, then assuming it is returning records simply open it or use it as a record source.

 

[Zedster]

Yes - and no... Your statement above is correct, but that's not what you are doing with your VBA code.
You are creating a normal query def with this line;

Set qdf = db.CreateQueryDef("")

I think you may need to also specify

qdf.ReturnsRecords = True

to make access realise it's a pass-through query.
Alternatively, simply create and save a generic pass-through query, then alter the SQL and run it.

That is my bad for not showing all the code I was trying to cut down on space by stopping at the error. The full code is:

    Dim db As Database
    Dim qdf As QueryDef
    Dim rst As Recordset
    Dim stServer As String
    Dim stDatabase As String
    Dim strServer As String: strServer = "CAUK-SVR-19\CAUKAPPS"
    Dim strDatabase As String: strDatabase = "TimeSheet"
    Dim strConnection As String: strConnection = "ODBC;DRIVER=SQL Server;SERVER=" & strServer & ";DATABASE=" & strDatabase & ";Trusted_Connection=Yes"
    Dim strSQL As String
    Dim strOutput As String
    Dim strRecurse As String
    Debug.Print strConnection
    
    strSQL = "WITH subs AS ("
    strSQL = strSQL & "SELECT StaffNo,FN,LN,ReportsTo "
    strSQL = strSQL & "FROM tblStaff "
    strSQL = strSQL & "WHERE StaffNo = '47472' "
    strSQL = strSQL & "UNION ALL "
    strSQL = strSQL & "SELECT e.StaffNo,e.FN,e.LN,e.ReportsTo "
    strSQL = strSQL & "FROM tblStaff e INNER JOIN subs s "
    strSQL = strSQL & "ON e.ReportsTo = s.StaffNo) "
    strSQL = strSQL & "SELECT StaffNo FROM subs;"
    Debug.Print strSQL

    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("")
    qdf.SQL = strSQL
    qdf.Connect = strConnection
    qdf.ReturnsRecords = True
    Set rst = qdf.OpenRecordset
    
    strOutput = ""
    strRecurse = ""
    
    If rst.RecordCount > 0 Then
    
        With rst
            .MoveFirst
            Do While Not .EOF
                If Len(strOutput) > 1 Then
                    strOutput = strOutput & ", '" & !StaffNo & "'"
                Else
                    strOutput = "'" & !StaffNo & "'"
                End If
                .MoveNext
            Loop
        End With
        
        'get rid of any double single quotes
        strOutput = Replace(strOutput, "''", "'")
        If Len(strOutput) > 1 Then
            AllSubordinates = strOutput
        Else
            AllSubordinates = "''"
        End If
    End If
    
    rst.Close
    db.Close
    Set rst = Nothing
    Set qdf = Nothing
    Set db = Nothing

 

[Zedster]

SOLVED! I realised when I copied all the code I had my qdf.sql and qdf.connect the wrong way around. Thank you for all your help.

 

[Minty]

Glad you solved it - sometimes seeing it all helps!

 

[Zedster]

Glad you solved it - sometimes seeing it all helps!

Actually I think we were both right. I was actually creating passthrough queries in VBA, but because the two lines were in wrong order Access could not know that and was in fact trying to create an Access query with unrecogonisible SQL.