Conrad Addo Conrad Addo - 1 year ago 55
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 Source

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]);"