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?
我如何设置它以便它可以将数据从临时表复制到主表,如果所有列都被命名为相同,并且具有这么多/很长的名称?
2 个解决方案
#1
2
"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 INSERT supplies 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 WHERE clause 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 SELECT query with Access' query designer and add the ProjectsTempT fields 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.
但我不明白为什么你的第一次尝试(带有字段名称列表)失败了。 (是否有错误消息?)使用Access的查询设计器构建SELECT查询,并将ProjectsTempT字段添加到网格的下半部分。然后将查询类型更改为“追加”。在网格中,为源表中的每个字段选择匹配的目标表字段。假设该方法为您提供了有效的查询,请切换到SQL View以检查语句文本。修改VBA代码以生成相同的语句。或者使查询设计器版本成为参数查询,保存它,并从VBA调用该命名查询。
#2
1
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
有时它只是无法一次处理整个块,所以你需要分解它。如果这不起作用,您始终可以使用DoCmd.OpenQuery创建Append查询并通过代码运行它
#1
2
"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 INSERT supplies 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 WHERE clause 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 SELECT query with Access' query designer and add the ProjectsTempT fields 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.
但我不明白为什么你的第一次尝试(带有字段名称列表)失败了。 (是否有错误消息?)使用Access的查询设计器构建SELECT查询,并将ProjectsTempT字段添加到网格的下半部分。然后将查询类型更改为“追加”。在网格中,为源表中的每个字段选择匹配的目标表字段。假设该方法为您提供了有效的查询,请切换到SQL View以检查语句文本。修改VBA代码以生成相同的语句。或者使查询设计器版本成为参数查询,保存它,并从VBA调用该命名查询。
#2
1
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
有时它只是无法一次处理整个块,所以你需要分解它。如果这不起作用,您始终可以使用DoCmd.OpenQuery创建Append查询并通过代码运行它