访问中重复的输出目标错误

Question

我正在使用以下查询。但是当我运行它时，它会产生错误

"重复输出目标'shipmentid'。

CurrentDb.Execute "INSERT INTO TmpContainersForPLReport " & _
                  "SELECT * FROM Containers INNER JOIN Shipments ON (Containers.ShipmentID = Shipments.ShipmentID)  WHERE Containers.PackinglistentryDate = #" & TxtEntryDate & "# and Containers.PONumber = '" & TxtPONumber & "'" & _
                  " OR Containers.PackinglistentryDate = #" & TxtEntryDate & "# and Containers.SalesOrderNumber = '" & txtSalesOrderNumber & "'"

Answer 1

您正在遇到薄错误，因为您正在使用两个表之间的连接中使用'select *'，并且两张表中都存在field shipmentID，因此您选择了两次该字段。

如果您转到此链接，您将看到此错误的描述：

https://msdn.microsoft.com/en-us/library/bb209781(v = Office.12(.aspx

Answer 2

从加入中，可以看到 shipmentid 居住在两个表中因此错误。因此，始终明确参考列，而不是用*缩写。即使如此明确地引用INSERT子句中的目标列。

作为另一个最佳实践，请考虑通过字符串串联进行参数化。您可以使用具有PARAMETERS子句的Dao QueryDef参数。请参见下面的示例。请替换 cols 。

Dim strSQL As String
Dim qdef As QueryDef
strSQL = "PARAMETERS TxtEntryDateParam Date, TxtPONumberParam Text(255), TxtSalesOrderNumberParam Text(255);" & _
         "INSERT INTO TmpContainersForPLReport (Col1, Col2, Col3) " & _
         "SELECT Col1, Col2, Col3 FROM Containers c " & _
         "INNER JOIN Shipments s ON (c.ShipmentID = s.ShipmentID) " & _
         "WHERE c.PackinglistentryDate = [TxtEntryDateParam] AND c.PONumber = [TxtPONumberParam]" & _
         "   OR c.PackinglistentryDate = [TxtEntryDateParam] AND c.SalesOrderNumber = [TxtSalesOrderNumberParam];"
Set qdef = CurrentDb.CreateQueryDef("", strSQL)
qdef!TxtEntryDateParam = TxtEntryDate
qdef!TxtPONumberParam = TxtPONumber
qdef!TxtSalesOrderNumberParam = txtSalesOrderNumber
qdef.Execute dbFailOnError
Set qdef = Nothing

实际上，您甚至可以将SQL作为保存的访问查询保存在上面，Jet/Ace Engine缓存以获得最佳优化，执行路径(即比VBA字符串查询更有效(：

Dim strSQL As String
Set qdef = CurrentDb.QueryDefs("myAppendQuery")
qdef!TxtEntryDateParam = TxtEntryDate
qdef!TxtPONumberParam = TxtPONumber
qdef!txtSalesOrderNumberParam = txtSalesOrderNumber
qdef.Execute dbFailOnError
Set qdef = Nothing