Import & replace data from Excel to Access

Hey Guys,

I have data in Access & Excel with identical headers and a unique account number on each. Is there a way I can have access read that excel table and for what ever accounts it finds have it import to access and update those accounts and if the account does not exist have it create a new record with the new account number. I'm new at this and I have no coding experience.

Ex: In Access, the record for Account 255 has fields "Name" "Amount" "Qty", if the excel sheet says that account 255 has a QTY of 3 and Access shows Qty 2 I want Access to import the new Qty and update account  255.

Hope someone can save me with an easy script I can modify :-)
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Bill RossProgrammerCommented:
Hi ellisito,

You can do this without coding using 4 queries.  You must first create a table in MS Access that is identical to your existing table but will have the Excel import records.  Make sure both tables have the exact structure and have a primary key.

Then here are the steps:
Query 1 - Clear the temp table
Query 2 - Append the linked Excel file to the temp table
Query 3 - Update the primary table with the data from the temp table
Query 4 - Add new records to the primary table from the temp table.

After you've debugged your work then put the 4 queries into a macro.  

Link the Excel file and run the macro.


ellisitoAuthor Commented:
I tried creating a sample of what you just explained. Problem is that the account # that identifies the record doest append because no duplicates can be created. I'm looking for a solution that compares the records and updates access. I don't mind importing the excel sheet to access so it can compare/update from one table to another. Creating 4 queries + running a macro seems to confuse me a bit.
Bill RossProgrammerCommented:

Post your database and xls file and I'll show you.  It's really not too bad once you get the hang of it.  If you want to use code it will be much more complex for you.

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

ellisitoAuthor Commented:
So here is a sample database. I have the original table and a table with the imported excel changes. My goal is to get the changes appearing in the excel table updated on the original table. Enlighten me :-)

Bill RossProgrammerCommented:

Problem is in table design.  The account number is unique but you have allowed access to create a unique ID for you when creating the tables.  I changed the tables, created queries 1-4 and the macro.  I put some sample data into the table "Linked Excel Workbook".  This is the name of the linked workbook .

First - review the data in all 3 tables.  Note that there are differences in the data in the simulated Linked Excel Workbook.

Run the queries in order 1,2,3,4.  Look at the 3 table's data after each query.  The macro just runs the queries in order.

Next steps to make it work with your Excel file.
1.  Delete the table "Linked Excel Workbook".  (Note: This will break Query2)
2.  Link your Excel workbook and name it exactly "Linked Excel Workbook"
3.  Run Query 1 and Query 2
4.  Verify that the table "Imported Excel File" is populated correctly.
5.  Run queries 3-4.

In production:
1. Link the excel file as "Linked Excel Workbook"
2. Run teh macro "TrueUpAccountTableMacro"

That's it.

Hope it helps.


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
ellisitoAuthor Commented:
This was great. I clearly see how this works. I even learned how to modify it for other things.

Thank you so much!
Bill RossProgrammerCommented:
Glad to be of help!  Bill
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.