[RESOLVED] Access 2003 - compaire problem

Greetings,

I am writing on an App (VB2005) which imports records from an Excel Sheet into an Access 2003 DB.

We are talking around 15000 records.
The records are containing persons details like an addressbook
This Excel Sheet is monthly updated and has to be imported into the DB

My problem now is 95% of the monthly Excel data are identical to the existing data in the DB.

There could be the possibility a person has moved from street A to street B but the rest of the record stays the same.

The only idea i have in the moment is

Code:

---


loop through all Excel records

get one record

compair if Firstname, Surname and/or Birthname and Birthday are allready existing in the DB (these are the only fields which should be relaiable to check)

    If yes - compair the whole Excel record with the DB record and check if there are identical

      If yes forget this record (because it exist allready)
      If no park the Excel record in an temporary table for manualy check later
    If no enter Excel record into DB

---

Because of the amount of data and looping trough each record it takes now a lot of time to do the job.

Is there a faster/ better methode?

Thanks in advance

Here is what you can do:

1. try to find record by excuting sql similar to this:

Code:

---

select * from Employees
where
    Firstname = 'Mary'
and (Surname = 'Jonson' or Birthname = 'Swanson')
and Birthday = #08/16/1990#

---

2. if dataset contains at least one record always UPDATE it with all remaining values (don't compare them).

Code:

---

Update Employees
set
    f1 = abc
    f2 = 123
where
    Firstname = 'Mary'
and (Surname = 'Jonson' or Birthname = 'Swanson')
and Birthday = #08/16/1990#

---

3 if record doesn't exist insert new.

Code:

---

insert into Employees
    (Firstname, Surname, Birthname, Birthday, etc...)
values
    (1, 2, 3, 4...)

---

NOTE: all sql statements are psuedo sql so modify each as necessary

Btw, I would define an index on that table to include all 4 fields: Firstname, Surname, Birthname, Birthday - it will speed up the search part at least.

Thanks for your help :-)