VBA Insert Query: "Query input must contain at least one table or query""

Question

I am importing data from an Excel spreadsheet into tblPOLinesTemp table, and then I have to apppend those to tblPOLines table. I am trying to execute an insert into SQL statement from VBA, but keep getting "Query input must contain at least one table or query" as error, and I cannot seem to figure out what is wrong as the SQL statement works when running it in Access! It is driving me crazy! Can you help?

My code (I have Dim'ed AppendPOlines as String):

AppendPOLines = "INSERT INTO tblPOlines ( PurchaseOrderID, PurchaseOrderLineID, [Material#], FirstDeliveryDate, Quantity, CreatedBy )" _
    & "SELECT tblPOLinesTemp.PurchaseOrderID, tblPOLinesTemp.PurchaseOrderLineID, tblPOLinesTemp.[Material#], tblPOLinesTemp.FirstDeliveryDate, tblPOLinesTemp.Quantity, [TempVars]![VarUserID] AS CreatedBy" _
    & "FROM tblPOLinesTemp LEFT JOIN tblPOlines ON (tblPOLinesTemp.PurchaseOrderLineID = tblPOlines.PurchaseOrderLineID) AND (tblPOLinesTemp.[PurchaseOrderID] = tblPOlines.[PurchaseOrderID])" _
    & "WHERE (((tblPOlines.PurchaseOrderLineID) Is Null) AND ((tblPOlines.PurchaseOrderID) Is Null));"

    CurrentDb.Execute AppendPOLines, dbFailOnError

Note that the Left Joins is to ensure that the Query does not insert rows with similar Purchase Order and Purchase Order Line ID - to avoid duplicate rows.

Thank you!

M. Rezaeyan M. Rezaeyan · Accepted Answer · 2016-10-18T18:13:03

After and before each ["] put a blank space :

AppendPOLines = " INSERT INTO tblPOlines( PurchaseOrderID, PurchaseOrderLineID, [Material#], FirstDeliveryDate, Quantity, CreatedBy ) " _
    & " SELECT tblPOLinesTemp.PurchaseOrderID, tblPOLinesTemp.PurchaseOrderLineID, tblPOLinesTemp.[Material#], tblPOLinesTemp.FirstDeliveryDate, tblPOLinesTemp.Quantity, [TempVars]![VarUserID] AS CreatedBy " _
    & " FROM tblPOLinesTemp LEFT JOIN tblPOlines ON (tblPOLinesTemp.PurchaseOrderLineID = tblPOlines.PurchaseOrderLineID) AND (tblPOLinesTemp.[PurchaseOrderID] = tblPOlines.[PurchaseOrderID]) " _
    & " WHERE (((tblPOlines.PurchaseOrderLineID) Is Null) AND ((tblPOlines.PurchaseOrderID) Is Null)); "

your query is executed like following:

INSERT INTO tblPOlines( PurchaseOrderID, PurchaseOrderLineID, [Material#], FirstDeliveryDate, Quantity, CreatedBy )SELECT tblPOLinesTemp.PurchaseOrderID, tblPOLinesTemp.PurchaseOrderLineID, tblPOLinesTemp.[Material#], tblPOLinesTemp.FirstDeliveryDate, tblPOLinesTemp.Quantity, [TempVars]![VarUserID] AS CreatedByFROM tblPOLinesTemp LEFT JOIN tblPOlines ON (tblPOLinesTemp.PurchaseOrderLineID = tblPOlines.PurchaseOrderLineID) AND (tblPOLinesTemp.[PurchaseOrderID] = tblPOlines.[PurchaseOrderID])WHERE (((tblPOlines.PurchaseOrderLineID) Is Null) AND ((tblPOlines.PurchaseOrderID) Is Null));

so CreatedByFrom is wrong and you get this error: "query input must contain at least one table or query"

VBA Insert Query: "Query input must contain at least one table or query""

1 Answers