Copying data from one temp table to a main table

Question

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)

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?

Solution 1

"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 INTO ProjectsT
SELECT *
FROM ProjectsTempT;

I left off the WHERE clause to keep it simple.

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.

Solution 2

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