asked on

Parse Excel file and update data in access using VBA from access

Hi,
I was wondering if some one can help me modify my access file.

I have access database and form which transfers data from excel file to access. The simple transfer is working but I have the following problem:
The transfer works vin a very simple way it takes content of excel file and transfers it to access table. Now in many instances excel file has the same datasets as already in the database plus new datasets. I need to do the following

1. For new data I just need to add it to DB, this seems to be working
2. For datasets that are not new and already exist in db, i need to compare and see if there are any changes in fields value_4 and value_6. If it is different then i need to replace the values in those fields. If it is the same then I do not touch that dat row.

I have atatched sample files excel and db file.
The Value 9 field in both excel and access are calculated fields(value1 +value2+value3). So records have to be compared by those fileds since that represents uniquness .

The module basFileImport is where the code for updating data happens.

In excel file row 26 and 20 is different than in db. rows 33 and 39 are not in db at all.

Any help will be apreciated.

Thank you!
testing-auto-update.xlsx
data-transfer-sample.accdb

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

maximyshka

ASKER

I did it alittle bit differently. I created temp table where i first copy all records from excel using my procedure and then i run 2 queries. 1- append query with outter join sub query, 2- update query based on join. It works but i gues not very eficient. Your solution is better .

thanks!