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:
The VBA code I am using to create it on the fly that fails with the error message is:
When I copy the output from the debug.print strSQL into SSMS it runs fine.
Any ideas?
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:
Code:
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:
Code:
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?