Exporting from Excel to Access - How to check if record already exists 1

[Chats]

With the help of this forum I am using ADO to send data from Excel 2000 to Access 2000 using some VB in Excel. Everything works fine.

I now have a slightly different need - before I send the data I need to check if the record already exists in Access - to avoid getting duplicates each time the data is sent.

Can anybody provide some guidance on how to best do this?

To give a bit more detail -

I have a database with the fields

ProjectNumber
ProjectName

I want to check to see if the ProjectNumber I am about to send from Excel already exists in the Access table, and only send the data if the record does not already exist.

Thanks

Ade

 

[SkipVought]

Hi,

you make these keys UNIQUE.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Chats]

Skip, thanks...

I did think of doing it that way - seeing the keys to UNIQUE and then starting my vb sub procedure in Excel with

On Error Resume Next

- this works fine, only sending the data if the key does not already exist.

But I do not like the idea of just skipping all errors during the export, I would prefer to send a query to the Access database to check whether the key is already there, before I start sending data down the line.

Is there a good way of doing this?

Ade

 

[SkipVought]

Use On Error Resume Next. Check the Err.Number. If the value is for dup keys, then write the record to a file of dup keys. Otherwise, write record to another file w/ err.number OR write them ALL to one file with err.number.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Chats]

Fantastic Skip... simple & very effective - thanks!