Can't run query in Access via vba with a long definition

Question

I got a very long query definition that works when it run it in the Access GUI. I don't want to save it as a Query object that an end user can accidently run so I want to run it via VBA. When I tried to run it using DoCmd.RunSQL it throws an error:

"A RunSQL action requires an argument consisting of an SQL statement."

When I try it with mydatabase.Execute I get:

"The Microsoft Access engine cannot find the input table or query ''. Make sure it exists and that its name is spelled correctly."

I did a Debug.Print and copied the output to an Query in the Access GUI, after cleaning up the line brakes the Immediate window added the query ran fine. The query string is 5,293 characters long. Any help would be appreciated.

Dim dBase As Database
Set dBase = CurrentDb()
Dim stringSQL_3 As String
stringSQL_3 = _
"INSERT INTO Measurement_Period_History ( EMPLOYEE_NUMBER, PERIOD_START, PERIOD_END, DATE_REVIEWED, AVERAGE_HOURS, PERIOD_YEAR, PERIOD_TYPE_ID,  FIRST_PAY_WEEK, LAST_PAY_WEEK ) " & _
"SELECT FinalData.EMPLOYEE_NUMBER " & _
    ",FinalData.START_PERIOD " & _
    ",FinalData.END_PERIOD " & _
    ",Now() AS DATE_REVIEWED " & _
    ",IIF(FinalData.FLSA = 2, 40.00, SUM(FinalData.HOURS) / 52) AS AVERAGE_HOURS " & _
    ",Cint(FinalData.PERIOD_YEAR) AS PERIOD_YEAR " & _
    ",FinalData.PERIOD_TYPE_ID " & _
    ",FinalData.FIRST_PAY_WEEK " & _
    ",FinalData.LAST_PAY_WEEK "

stringSQL_3 = stringSQL_3 & _
"FROM ( " & _
    "SELECT MeasurementData.EMPLOYEE_NUMBER " & _
        ",MeasurementData.FLSA " & _
        ",MeasurementData.STARTING_HIRE_DATE " & _
        ",MeasurementData.VARIABLE_EMPLOYEE " & _
        ",MeasurementData.START_PERIOD " & _
        ",MeasurementData.END_PERIOD " & _
        ",MeasurementData.PERIOD_TYPE_ID " & _
        ",MeasurementData.PRIOR_PERIOD_START " & _
        ",MeasurementData.PERIOD_YEAR " & _
        ",MeasurementData.FIRST_PAY_WEEK " & _
        ",MeasurementData.LAST_PAY_WEEK " & _
        ",IIf(IsNull(Payroll_History.HOURS), 0, Payroll_History.HOURS) AS HOURS "

stringSQL_3 = stringSQL_3 & _
    "FROM Payroll_History " & _
    "RIGHT JOIN ( " & _
        "SELECT BaseData.EMPLOYEE_NUMBER " & _
            ",BaseData.FLSA " & _
            ",BaseData.STARTING_HIRE_DATE " & _
            ",BaseData.VARIABLE_EMPLOYEE " & _
            ",BaseData.START_PERIOD " & _
            ",BaseData.END_PERIOD " & _
            ",BaseData.PERIOD_TYPE_ID " & _
            ",BaseData.PRIOR_PERIOD_START " & _
            ",BaseData.PERIOD_YEAR " & _
            ",DateAdd('d', 3, DateAdd('ww', - 52, DateAdd('d', - 2, DateAdd('ww', DateDiff('ww', 0, DateAdd('ww', IIf(DatePart('w', " & _
            "BaseData.END_PERIOD) >= 5, 0, - 1), BaseData.END_PERIOD)), 0)))) AS FIRST_PAY_WEEK " & _
            ",DateAdd('d', - 2, DateAdd('ww', DateDiff('ww', 0, DateAdd('ww', IIf(DatePart('w', BaseData.END_PERIOD) >= 5, 0, - 1), " & _
            "BaseData.END_PERIOD)), 0)) AS LAST_PAY_WEEK "

stringSQL_3 = stringSQL_3 & _
        "FROM ( " & _
            "SELECT ElgEmployees.EMPLOYEE_NUMBER " & _
                ",ElgEmployees.FLSA " & _
                ",ElgEmployees.STARTING_HIRE_DATE " & _
                ",ElgEmployees.VARIABLE_EMPLOYEE " & _
                ",IIF(ElgEmployees.VARIABLE_EMPLOYEE = No, DateSerial(" & DatePickerYear & " - 1, 4, 1), " & _
                "IIF(PeriodInfo.PRIOR_PERIOD_START IS NOT NULL, DateSerial(" & DatePickerYear & " - 1, DatePart('m', " & _
                "PeriodInfo.PRIOR_PERIOD_START), 1), DateSerial(" & DatePickerYear & " - 1, DatePart('m', ElgEmployees.STARTING_HIRE_DATE), 1))) AS START_PERIOD " & _
                ",IIF(ElgEmployees.VARIABLE_EMPLOYEE = No, DateAdd('d', - 1, DateSerial(" & DatePickerYear & ", 4, 1)), DateAdd('d', - 1, " & _
                "IIF(PeriodInfo.PRIOR_PERIOD_START IS NOT NULL, DateSerial(" & DatePickerYear & ", DatePart('m', PeriodInfo.PRIOR_PERIOD_START), 1), " & _
                "DateSerial(" & DatePickerYear & ", DatePart('m', ElgEmployees.STARTING_HIRE_DATE), 1)))) AS END_PERIOD " & _
                "," & DatePickerYear & " AS PERIOD_YEAR " & _
                ",PeriodInfo.PERIOD_TYPE_ID " & _
                ",PeriodInfo.PRIOR_PERIOD_START "

stringSQL_3 = stringSQL_3 & _
            "FROM ( " & _
                "SELECT Employee.EMPLOYEE_NUMBER " & _
                    ",Employee.FLSA " & _
                    ",Employee.VARIABLE_EMPLOYEE " & _
                    ",IIf(Day(Employee.CURRENT_HIRE_DATE) = 1, Employee.CURRENT_HIRE_DATE, DateSerial(DatePart('yyyy', DateAdd('m', 1, " & _
                    "Employee.CURRENT_HIRE_DATE)), DatePart('m', DateAdd('m', 1, Employee.CURRENT_HIRE_DATE)), 1)) AS STARTING_HIRE_DATE "

stringSQL_3 = stringSQL_3 & _
                "FROM Employee " & _
                "WHERE ( " & _
                        "((Employee.EMPLOYEE_NUMBER)" & EmployeeNumberText & ") " & _
                        "AND ((IIf(Day(Employee.CURRENT_HIRE_DATE) = 1, Employee.CURRENT_HIRE_DATE, DateSerial(DatePart('yyyy', DateAdd('m', 1, " & _
                        "Employee.CURRENT_HIRE_DATE)), DatePart('m', DateAdd('m', 1, Employee.CURRENT_HIRE_DATE)), 1))) < DateAdd('y', - 1, DATE ())) " & _
                        "AND ((Employee.CURRENT_TERMINATION_DATE) IS NULL) " & _
                        ") " & _
                ") AS ElgEmployees " & _
            "INNER JOIN ( " & _
                "SELECT Employee.EMPLOYEE_NUMBER " & _
                    ",IIF(Measurement_Period_History.PERIOD_YEAR <> " & DatePickerYear & " - 1, NULL, Measurement_Period_History.PERIOD_YEAR) AS PRIOR_PERIOD_YEAR " & _
                    ",IIF(Measurement_Period_History.PERIOD_YEAR <> " & DatePickerYear & " - 1, IIF(Employee.VARIABLE_EMPLOYEE = No, 2, 0), " & _
                    "IIF(Employee.VARIABLE_EMPLOYEE = No, 2, IIF(Measurement_Period_History.PERIOD_END < Employee.CURRENT_HIRE_DATE, 0, " & _
                    "IIF(Measurement_Period_History.AVERAGE_HOURS >= 30, 1, 0)))) AS PERIOD_TYPE_ID " & _
                    ",IIF(Measurement_Period_History.PERIOD_YEAR <> " & DatePickerYear & " - 1, NULL, IIF(Measurement_Period_History.PERIOD_END < " & _
                    "Employee.CURRENT_HIRE_DATE, NULL, Measurement_Period_History.PERIOD_END)) AS PRIOR_PERIOD_END " & _
                    ",IIF(Measurement_Period_History.PERIOD_YEAR <> " & DatePickerYear & " - 1, NULL, IIF(Measurement_Period_History.PERIOD_END < " & _
                    "Employee.CURRENT_HIRE_DATE, NULL, Measurement_Period_History.PERIOD_START)) AS PRIOR_PERIOD_START "

stringSQL_3 = stringSQL_3 & _
                "FROM Measurement_Period_History " & _
                "RIGHT JOIN Employee ON Measurement_Period_History.EMPLOYEE_NUMBER = Employee.EMPLOYEE_NUMBER " & _
                "WHERE ((Employee.CURRENT_TERMINATION_DATE) Is Null) AND (Employee.EMPLOYEE_NUMBER" & EmployeeNumberText & ") " & _
                "GROUP BY Employee.EMPLOYEE_NUMBER " & _
                    ",IIF(Measurement_Period_History.PERIOD_YEAR <> " & DatePickerYear & " - 1, NULL, Measurement_Period_History.PERIOD_YEAR) " & _
                    ",IIF(Measurement_Period_History.PERIOD_YEAR <> " & DatePickerYear & " - 1, IIF(Employee.VARIABLE_EMPLOYEE = No, 2, 0), " & _
                    "IIF(Employee.VARIABLE_EMPLOYEE = No, 2, IIF(Measurement_Period_History.PERIOD_END < Employee.CURRENT_HIRE_DATE, 0, " & _
                    "IIF(Measurement_Period_History.AVERAGE_HOURS >= 30, 1, 0)))) " & _
                    ",IIF(Measurement_Period_History.PERIOD_YEAR <> " & DatePickerYear & " - 1, NULL, IIF(Measurement_Period_History.PERIOD_END < " & _
                    "Employee.CURRENT_HIRE_DATE, NULL, Measurement_Period_History.PERIOD_END)) " & _
                    ",IIF(Measurement_Period_History.PERIOD_YEAR <> " & DatePickerYear & " - 1, NULL, IIF(Measurement_Period_History.PERIOD_END < " & _
                    "Employee.CURRENT_HIRE_DATE, NULL, Measurement_Period_History.PERIOD_START)) " & _
                ") AS PeriodInfo ON ElgEmployees.EMPLOYEE_NUMBER = PeriodInfo.EMPLOYEE_NUMBER " & _
            ") AS BaseData " & _
        ") AS MeasurementData ON Payroll_History.EMPLOYEE_NUMBER = MeasurementData.EMPLOYEE_NUMBER " & _
    "WHERE ((Payroll_History.PAY_DATE) Is Null) " & _
        "OR ( " & _
            "(Payroll_History.PAY_DATE) >= MeasurementData.FIRST_PAY_WEEK " & _
            "AND (Payroll_History.PAY_DATE) <= MeasurementData.LAST_PAY_WEEK " & _
            ") " & _
    ") AS FinalData "

stringSQL_3 = stringSQL_3 & _
"WHERE LAST_PAY_WEEK < Now() " & _
"GROUP BY EMPLOYEE_NUMBER " & _
    ",FLSA " & _
    ",STARTING_HIRE_DATE " & _
    ",VARIABLE_EMPLOYEE " & _
    ",START_PERIOD " & _
    ",END_PERIOD " & _
    ",PERIOD_TYPE_ID " & _
    ",PRIOR_PERIOD_START " & _
    ",PERIOD_YEAR " & _
    ",FIRST_PAY_WEEK " & _
    ",LAST_PAY_WEEK "


    Debug.Print stringSQL_3
    Debug.Print Len(stringSQL_3)
    dBase.Execute string_3

Answer 1

Isn't your SQL in stringSQL_3? What's in the other string (string_3)? That might the issue, you are trying to execute the wrong string?