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

What is the string contained in string_3 when you get that error from dBase.Execute string_3? — HansUp
Not for nothing, but I am pretty sure that this is one of the reasons that saved queries exist. There are better ways to secure your Access objects than stringifying them. — Robert Harvey
Robert is right. You make life difficult for yourself. You should rethink this concept. — Gustav
I have to pass this on to a "Power User" who will continue to modify it after I am done. I know they are not likely to fiddle with the VBA code but they will be adding reports and new forms most likely using the wizards and such. I know this is bad, I don't look forward to maintaining this 3 years from now as it is so if you have any suggestions or could point me to somewhere that could help me clean this up I would appreciate it. — Mrphin

Ryan Wildry Ryan Wildry · Accepted Answer · 2015-06-26T23:36:08

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?

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

1 Answers