Insert into ... On duplicate key update ... (1 Viewer)

[selahlynch]

I am importing large datasets into my database from text files.

I would like to add records in a manner so that if they are new, a record is inserted, but if they duplicate of one of my unique indexes, they update the existing record.

In MySQL I run the following query for each record and it works great.

INSERT INTO tablename ..... ON DUPLICATE KEY UPDATE ....

In Access I've been using DAO.RecordSet functions.

            rs.FindFirst (matchcriteria)
            If rs.NoMatch Then
                rs.AddNew
            Else
                rs.Edit
            End If

But this seems to be too slow.

Does anybody have any ideas about how I could improve this process in Access?

 

[Galaxiom]

Do it in two queries.
Firstly run an update query with an inner join between the imported table and the destination on the key.
Then an append query using an outer join on the key (Show all records from the import) where destination.keyfieldname Is Null

 

[selahlynch]

Ahh, good idea, thanks.

Also, using DAO.RecordSet.Seek works a lot faster than DAO.RecordSet.FindFirst

It seems that rs.FindFirst was not using my table indexes at all... strange.