append data

[slimjen]

All; using 2010; I have a database in which I import 4 excel spreadsheets into temp tables. I want to append the data in temp tables to existing tables.
I want user to be able to do the importing and appending from a form with a command button. I put code similar to this in a function along with code to import the excel spreadsheet (Transferspreadsheet...):

Dim srtQry1 As String, srtQry2 As String
strQry1 = "INSERT INTO Order_Line (OrderNo, ProductID, Qty, Price) " & _
              "SELECT OrderNo, ProductID, Qty, Price " & _
              "FROM Import_Order_Lines;"
strQry2 = "INSERT INTO Order (OrderNo, OrderDate, CurstomerName, CustomerDeliveryAddress, CustomerSuburb, CustomerState, CustomerPostCode) " & _
              "SELECT OrderNo, OrderDate, CurstomerName, CustomerDeliveryAddress, CustomerSuburb, CustomerState, CustomerPostCode " & _
              "FROM Import_Order_Lines;"
DoCmd.RunSQL strQry1
DoCmd.RunSQL strQry2

Like i said; I have 4 tables. Is there a more proficient use of VBA that I can use or am I on the right track. How would you do this?


Thanks

[June7]

Only if you want to import directly into the final tables.

[slimjen]

So is this the best way to get the data into the final table?
Thanks

[June7]

By direct import to final table? Often. 'Best' approach depends on structures of the files. However, use of 'temp' tables is usually unnecessary step.

[slimjen]

thanks. Temp tables are to ensure data imported correctly from excel spreadsheet. Often more than not; data type does not hold when using Transferspreadsheet. Users get import errors if they forget to make sure the format is correct on the spreadsheet i.e. ID number is text not number.

[June7]

Even setting format on Excel is no guarantee of error-free import. Now working with another poster having that issue.