ellisito
asked on
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 :-)
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 :-)
ASKER
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.
Hi,
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.
Bill
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.
Bill
ASKER
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 :-)
Database6.accdb
Database6.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This was great. I clearly see how this works. I even learned how to modify it for other things.
Thank you so much!
Thank you so much!
Glad to be of help! Bill
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.
Regards,
Bill