How import records in a temp table to a final table

Posted on 2013-10-16
Medium Priority
Last Modified: 2013-10-25
I have records in a temp table.  The name of the temp table is tmpTable.  I also have a table named Orders.

I need VBA code if possible that will delete all existing records in the table named Orders and import the records in tmpTable into the then empty table named Inventory.

But the catch is this... the field names in tmpTable do not match the field names in Orders.  Somehow, and hopefully all through the magic of VBA coding I can tell the process what fields map to what fields.  I understand that there will have to be some hard coding of field names but that's ok.

Can someone help?

Question by:SteveL13
  • 2
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39578223
Use the query designer to make queries to delete, append etc all the data changes you need - you'll be able to handle the field mappings there.

Then use VBA to run them in the right order...

To stop the messages about how many records are to be deleted etc

commence the code with DoCmd.SetWarnings Off and add some form of error hanbdler /exit sub routine and rest the warnings to ON as you exit.

LVL 86
ID: 39579327
To do this all in VBA:

Currentdb.Execute "DELETE * FROM Inventory"
Currentdb.Execute "INSERT INTO Inventory(Col1, Col2, Col3) (SELECT Col1, Col4, Col6 FROM tmpTable"

The Field Types must match, or more specifically the data in the fields in tmpTable must be valid for the DataType of the "receiving" field in Inventory (i.e you couldn't stuff a String value into a Double field in Inventory).

Author Comment

ID: 39582826
I'm getting a syntax error.  Here is the code I have in place...

    CurrentDb.Execute "DELETE * FROM Inventory"
    CurrentDb.Execute "INSERT INTO Inventory(Col1, Col44, Col9, Col6, Col8, Col5, Col13, Col20, Col31, Col14, Col16) (SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col9, Col10, Col13, Col17, Col19 FROM tmpTable"

Note that I assume the "numbers" won't be in order because, for example, column 2 in tmpTable needs to insert into column 44 in Inventory table.  Am I doing this right?

LVL 86

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 39587386
Yes, that is correct. In the INSERT INTO portion you list the "receiving" columns in the order you wish, and then you match up the columns in the SELECT portion to put data in those receiving columns. So in your example above, Inventory.Col4 would receive data from tmpTable.Col2, and Inventory.Col9 would receive data from tmpTable.Col3.

Obviously you need to use your actual Column names for this. For example, Access won't translate "Col1" into "the first column in the table".

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question