vba 将数据从一个临时表复制到主表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19932568/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copying data from one temp table to a main table
提问by D347HxD
I have a temp table that form save pieces of data to that ends up creating a full record. I am trying to set it up so when you click a button it takes the data from the temp table, puts it into the main one, then deletes the old records in the temp table.
我有一个临时表，用于保存数据片段，最终创建完整记录。我正在尝试设置它，以便当您单击一个按钮时，它会从临时表中获取数据，将其放入主表中，然后删除临时表中的旧记录。
I was trying to use this code, but it seems there isn't enough room in the VBA editor for it (as all my column names seem to be too long and there are too many of them)
我试图使用此代码，但 VBA 编辑器中似乎没有足够的空间来容纳它（因为我所有的列名似乎都太长了，而且它们太多了）
strSQL = "Insert Into ProjectsT (CustomerName, ProjectName, ProjectDesc, DateOfPurchase, ProjectDueDate, EngineerDueDate, CutplanDueDate, HardwareDueDate, ProjectComplete, EngineerComplete, CutplanComplete, HardwareComplete, WorkOrder, MaterialSpecs, CutplanPattern, Milestones, HardwareSpecs, SupplierName, ProjFilePath, DrawFilePath, DetailFilePath, CostFilePath, ProjectStartDate, EngineerStartDate, CutplanStartDate, HardwareStartDate, PackageReleasedToShop, EstAssemblyHours, CuttingDueDAte, TrakwareNumber) Select CustomerName, ProjectName, ProjectDesc, DateOfPurchase, ProjectDueDate, EngineerDueDate, CutplanDueDate, HardwareDueDate, ProjectComplete, EngineerComplete, CutplanComplete, HardwareComplete, WorkOrder, MaterialSpecs, CutplanPattern, Milestones, HardwareSpecs, SupplierName, ProjFilePath, DrawFilePath, DetailFilePath, CostFilePath, ProjectStartDate, EngineerStartDate, CutplanStartDate, HardwareStartDate, PackageReleasedToShop, EstAssemblyHours, CuttingDueDAte, TrakwareNumber From ProjectsTempT Where ID=" & Me.txtID & ";"
How would I set it up so it can copy the data from the temp table to the main one if all columns are named the same, and have this many / long of names?
如果所有列的名称都相同，并且有这么多/长的名称，我将如何设置它以便它可以将数据从临时表复制到主表？
采纳答案by HansUp
"How would I set it up so it can copy the data from the temp table to the main one if all columns are named the same, and have this many / long of names?"
“如果所有列的名称都相同，并且有这么多/长的名称，我将如何设置它以便它可以将数据从临时表复制到主表？”
When your INSERTsupplies values for all the fields in the target table, you can omit the field list following the table name.
当您INSERT为目标表中的所有字段提供值时，您可以省略表名称后面的字段列表。
INSERT INTO ProjectsT
SELECT *
FROM ProjectsTempT;
I left off the WHEREclause to keep it simple.
WHERE为了简单起见，我省略了该条款。
But I don't understand why your first attempt (with the lists of field names) failed. (Was there an error message?) Build the SELECTquery with Access' query designer and add the ProjectsTempTfields to the lower part of the grid. Then change the query type to "append". And in the grid, choose the matching target table field for each of the fields from the source table. Assuming that approach gives you a working query, switch to SQL View to examine the statement text. Revise the VBA code to produce the same statement. Or make the query designer version a parameter query, save it, and call that named query from VBA.
但我不明白为什么您的第一次尝试（使用字段名称列表）失败。（是否有错误消息？）SELECT使用 Access 的查询设计器构建查询并将ProjectsTempT字段添加到网格的下部。然后将查询类型更改为“追加”。并在网格中，为源表中的每个字段选择匹配的目标表字段。假设该方法为您提供了一个有效的查询，请切换到 SQL 视图以检查语句文本。修改 VBA 代码以生成相同的语句。或者使查询设计器版本成为参数查询，保存它，然后从 VBA 调用该命名查询。
回答by Johnny Bones
Have you tried this?
你试过这个吗？
strSQL = "Insert Into ProjectsT (CustomerName, ProjectName, ProjectDesc, DateOfPurchase, ProjectDueDate, EngineerDueDate, CutplanDueDate, HardwareDueDate, ProjectComplete, EngineerComplete, CutplanComplete, HardwareComplete, WorkOrder, MaterialSpecs, CutplanPattern, Milestones, " & _
strSQL = strSQL & "HardwareSpecs, SupplierName, ProjFilePath, DrawFilePath, DetailFilePath, CostFilePath, ProjectStartDate, EngineerStartDate, CutplanStartDate, HardwareStartDate, PackageReleasedToShop, EstAssemblyHours, CuttingDueDAte, TrakwareNumber) " & _
strSQL = strSQL & "Select CustomerName, ProjectName, ProjectDesc, DateOfPurchase, ProjectDueDate, EngineerDueDate, CutplanDueDate, HardwareDueDate, ProjectComplete, EngineerComplete, CutplanComplete, HardwareComplete, WorkOrder, MaterialSpecs, CutplanPattern, Milestones, " & _
strSQL = strSQL & "HardwareSpecs, SupplierName, ProjFilePath, DrawFilePath, DetailFilePath, CostFilePath, ProjectStartDate, EngineerStartDate, CutplanStartDate, HardwareStartDate, PackageReleasedToShop, EstAssemblyHours, CuttingDueDAte, TrakwareNumber " & _
strSQL = strSQL & "From ProjectsTempT Where ID=" & Me.txtID & ";"
Sometimes it just can't handle the whole chunk at once, so you need to break it up. If that doesn't work, you can always create an Append query and run it through code by using DoCmd.OpenQuery
有时它只是无法一次处理整个块，因此您需要将其分解。如果这不起作用，您始终可以创建一个 Append 查询并使用 DoCmd.OpenQuery 通过代码运行它
