vba - 错误3134-语法错误-可以运行数年，但现在不会。找不到错误？

Question

这段代码写于2017年并一直有效-突然间它给了我一个语法错误...我似乎看不到这个问题。抱歉，我意识到它的很多代码...我们每月导入的一个冗长的发票文件-找到正确的季度来添加临时表。

    db.Execute "INSERT INTO" & varQuarter & " ([Ship Date], [Invoice Date], [Sell Price per Unit], 
   [Differential Amount]," _
 & " [Net Product Amount], [Amount Due], [Product Sell Amount Due], [Discount Amount Due], 
 [Miscellaneous Amount Due]," _
 & " [Storage Amount Due], [Overage Storage Amount Due], [Freight Amount Due], [Tax Amount Due], 
 [Memo Sell Amount]," _
 & " [Memo Tax Amount], [Billing Unit Quantity], [Billing Unit Count], [Billing Unit Code], [Shipped 
 Quantity]," _
& " [Invoiced Quantity], [Invoice Source System], [Invoice Number], [Invoice Bill Code], [Order 
Number], [Form Number]," _
& " [Form Name], [PO Number], [Requisition Number], [Billing Cost Center Number], [Using Cost Center 
Number]," _
& " [Warehouse Number], [Bill Of Lading Number], [Bill Of Lading Item Number], [Ship Mark For Text], 
[CCN Customer Name]," _
& " [CCN Customer Number], [Bill to Customer Name], [Bill to Customer Number], [Ship To Customer 
Number]," _
& " [Ship To Customer Name], [Ship To  Address Line1], [Ship To  Address Line2], [Ship To City], 
[Ship To State]," _
& " [Ship To Zip Code], [Plant Code], [Plant Name], [Product Code], [Product Name], [Postage Amount 
Due]," _
& " [Freight Carrier Code], [Category Title])" _
& " SELECT [Ship Date], [Invoice Date], [Sell Price per Unit], [Differential Amount], [Net Product 
Amount], [Amount Due]," _
& " [Product Sell Amount Due], [Discount Amount Due], [Miscellaneous Amount Due], [Storage Amount 
Due]," _
& " [Overage Storage Amount Due], [Freight Amount Due], [Tax Amount Due], [Memo Sell Amount], [Memo 
Tax Amount]," _
& " [Billing Unit Quantity], [Billing Unit Count], [Billing Unit Code], [Shipped Quantity], [Invoiced 
Quantity]," _
& " [Invoice Source System], [Invoice Number], [Invoice Bill Code], [Order Number], [Form Number], 
[Form Name], [PO Number]," _
& " [Requisition Number], [Billing Cost Center Number], [Using Cost Center Number], [Warehouse 
Number], [Bill Of Lading Number]," _
& " [Bill Of Lading Item Number], [Ship Mark For Text], [CCN Customer Name], [CCN Customer Number], 
[Bill to Customer Name]," _
& " [Bill to Customer Number], [Ship To Customer Number], [Ship To Customer Name], [Ship To  Address 
Line1]," _
& " [Ship To  Address Line2], [Ship To City], [Ship To State], [Ship To Zip Code], [Plant Code], 
[Plant Name], [Product Code]," _
 & " [Product Name], [Freight Carrier Code], [Postage Amount Due], [Category Title]" _
 & " FROM tblInvoicesTemp;"

Answer 1

只需避免需要长时间连接，换行和引号的VBA字符串查询。具体来说，您的问题似乎是INSERT INTO" & VarQuarter & "，如果空间不是问题(例如，错字后的StackOverflow)，则可能是VarQuarter的实际值。如果表名保留空格(如您的所有列)，特殊字符或前导数字，则必须使用方括号或反引号将其转义。
但是，通过保存Access存储的查询(最好是单个最终发票表)来完全避免这种长字符串构建。这样做，您不必担心换行或串联。此外，查询设计器不会将SQL与语法问题一起保存，并且Access引擎会为存储的查询缓存统计信息以实现最佳执行计划(因此，为什么它们比在VBA中动态运行的已解析SQL更好)。如果需要在WHERE子句中传递VBA值，则仍可以使用带有参数支持的已保存查询。
SQL (另存为Access存储的查询，或者每个四分之一表一个)

INSERT INTO [myFinalTable] ([Ship Date], [Invoice Date], [Sell Price per Unit], 
                            [Differential Amount], [Net Product Amount], [Amount Due], 
                            [Product Sell Amount Due], [Discount Amount Due], 
                            [Miscellaneous Amount Due], [Storage Amount Due], 
                            [Overage Storage Amount Due], [Freight Amount Due], 
                            [Tax Amount Due], [Memo Sell Amount], [Memo Tax Amount], 
                            [Billing Unit Quantity], [Billing Unit Count], [Billing Unit Code], 
                            [Shipped Quantity], [Invoiced Quantity], [Invoice Source System], 
                            [Invoice Number], [Invoice Bill Code], [Order Number], [Form Number],
                            [Form Name], [PO Number], [Requisition Number], [Billing Cost Center Number], 
                            [Using Cost Center Number], [Warehouse Number], [Bill Of Lading Number], 
                            [Bill Of Lading Item Number], [Ship Mark For Text], [CCN Customer Name],
                            [CCN Customer Number], [Bill to Customer Name], [Bill to Customer Number], 
                            [Ship To Customer Number], [Ship To Customer Name], [Ship To  Address Line1], 
                            [Ship To  Address Line2], [Ship To City], [Ship To State],
                            [Ship To Zip Code], [Plant Code], [Plant Name], [Product Code], [Product Name], 
                            [Postage Amount Due], [Freight Carrier Code], [Category Title])

SELECT [Ship Date], [Invoice Date], [Sell Price per Unit], [Differential Amount]
     , [Net Product Amount], [Amount Due], [Product Sell Amount Due], [Discount Amount Due]
     , [Miscellaneous Amount Due], [Storage Amount Due], [Overage Storage Amount Due]
     , [Freight Amount Due], [Tax Amount Due], [Memo Sell Amount], [Memo Tax Amount]
     , [Billing Unit Quantity], [Billing Unit Count], [Billing Unit Code], [Shipped Quantity]
     , [Invoiced Quantity], [Invoice Source System], [Invoice Number], [Invoice Bill Code]
     , [Order Number], [Form Number], [Form Name], [PO Number], [Requisition Number]
     , [Billing Cost Center Number], [Using Cost Center Number], [Warehouse Number] 
     , [Bill Of Lading Number], [Bill Of Lading Item Number], [Ship Mark For Text]
     , [CCN Customer Name], [CCN Customer Number], [Bill to Customer Name]
     , [Bill to Customer Number], [Ship To Customer Number], [Ship To Customer Name]
     , [Ship To  Address Line1], [Ship To  Address Line2], [Ship To City]
     , [Ship To State], [Ship To Zip Code], [Plant Code], [Plant Name], [Product Code]
     , [Product Name], [Freight Carrier Code], [Postage Amount Due], [Category Title]
 FROM tblInvoicesTemp;

VBA

DoCmd.OpenQuery "myAppendQuery"     ' NO NEED TO CLOSE ACTION QUERIES

现在，如果您使用许多四分之一表，则为每个表保存一个相应的查询，并在 OpenQuery调用中动态分配查询名称并传递变量值:

Dim VarQuarter
VarQuarter = ...

DoCmd.OpenQuery VarQuarter        ' NO NEED TO CLOSE ACTION QUERIES

为了避免用户更新/插入警告，请使用较早的 Database.Execute方法:

Dim VarQuarter
VarQuarter = ...

db.Execute VarQuarter