How import records in a temp table to a final table

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?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kelvin SparksCommented:
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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).
SteveL13Author Commented:
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?

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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".

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.