Conrad Addo Conrad Addo - 1 month ago 8
SQL Question

Why does my Access SQL VBA code jump out of the Sub?

My code is exiting out of the sub on the line that says "CurrentDb.Execute strSQL_Insert_Data". Do you know why this is happening?

Local_Array = Array("dbo_Tape_Capture_Local_tbl", "dbo_Tape_Local_tbl", "dbo_Tape_Memo_Local_tbl")
Server_Array = Array("dbo_Tape_Capture", "dbo_Tape", "dbo_Tape_Memo")

For i = 0 To UBound(Local_Array)
strSQL_Insert_Data = "INSERT INTO [" & Local_Array(i) & "] " & _
"SELECT [" & Server_Array(i) & "].* " & _
"WHERE (LEFT([" & Server_Array(i) & "].header__situs_loan_id," & _
Len([Forms]![Login Page]![CBO_Job_Select_Login]) & ") = " & _
"[Forms]![Login Page]![CBO_Job_Select_Login]);"

CurrentDb.Execute strSQL_Insert_Data

CurrentDb.Close

Next i


--Added Printed out code--

INSERT INTO [dbo_Tape_Capture_Local_tbl] SELECT [dbo_Tape_Capture].*
WHERE (LEFT([dbo_Tape_Capture].header__situs_loan_id,14) = [Forms]![Login Page]![CBO_Job_Select_Login]);

Answer

There is missing FROM TableName in your query. Replace TableName with Actual Table Name

strSQL_Insert_Data = "INSERT INTO [" & Local_Array(i) & "] " & _
                     "SELECT [" & Server_Array(i) & "].* " & _
                     "FROM [" & Server_Array(i) & "] " & _
                     "WHERE (LEFT([" & Server_Array(i) & "].header__situs_loan_id," & _
                      Len([Forms]![Login Page]![CBO_Job_Select_Login]) & ") = " & _
                     "[Forms]![Login Page]![CBO_Job_Select_Login]);"