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

All Courses

From novice to tech pro — start learning today.