Manipulate and Import from Excel using Access

Ok, everytime I get the solution to 1 problem I manage to create another problem!  I'm going to post everything I'm trying to do and issues.

I have an Microsoft Access database, originally it was created in 2000 but now I'm using office 2010.  I did not convert my database - I was just able to open it in the new version and edit the datbase.

I prompt the user for the excel file that they want to import - the version is Miicrosoft Excel 2010 -  the data in the files is stored as formulas.  I need to update a table in Access with data from the excel spreadsheet - the values only.  If the record already exists in the table, I need to delete the data in the table and then append the new data from excel into the table.  If the record does not exist in the table, I will append it.   I have 2 key fields Quote No and Item No

The problems I have ran into so far are:

Importing from the excel spreadsheet as is (with formulas).  I have a work around by copying the data into a new worksheet (using access) and pasting the values only.  Then importing the new sheet (which doesn't work because of the error below)

Different versions of Microsoft Excel and Microsoft Access - Microsoft Excel is a later version than Microsoft Access - which I don't understand because I'm editing my database in 2010.

I hope this makes sense!  I'm at my wits end!
Marilync1266Asked:
Who is Participating?

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

x
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.

hitsdoshi1Commented:
My approach to this would be.....

1) I would link the excel spreadsheet to Access (Not import) - Lets call it Table x
2) You would like excel data in already existing access table - Lets call it Table a
3) Create a form and vba code to go each record on table x

For each record on Table x
    Check in Table a
    If Exist then do nothing (loop - read next record)
    if doesn't exist then append that record in Table a
End For

Hope this helps! Good Luck!
0

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
Marilync1266Author Commented:
the answer was not complete
0
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.