How do I find duplicates in a database that I want to update.

PBMax used Ask the Experts™
Right now I don't find if the data is already there so I get an error because one of the fields is a key field.

            Set rs = db.OpenRecordset("PROGRAMTABLE", dbOpenDynaset)
            rs.FindFirst "ID='" & ProgID& "'"
            If rs.NoMatch Then
                rs("ID") = ProgID
                rs("title") = ProgramTitle
                rs("desc") = ProgramDescription
            End If

This method is SUPER SLOW.  I'm not sure what makes it slow.  It seems to work.

If I don't use the dbOpenDynaset option.
And just do an update (the no match and find first statements commented out) then the database update is super quick.  The problem is if the data already exists because of a previous update then I get an error.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Use the following method. Never use Recordsets to Insert, Update or delete from database. they are actually very slow and resource intensive in doing that.

strSQL="Select ColumnName from PROGRAMTABLE Where ID='" & ProgID& "'"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

if rs.EOF=True then
strSQL="Insert into  PROGRAMTABLE (ID, title, desc) Values('" & ProgID & "', '" & ProgramTitle & "', '" & ProgramDescription & ")"          

db.Execute strSQL
msgbox "The data is already existing in the database.
end if


How can I do an update instead of the msgbox?

This method almost works.  I have to strip out single quotes out of the title and descriptions now.  Is there a way I can keep them in?  I tried to replace them with Chr(36) but it still complains.
Simply fire the Update statement like thisL
strSQL="Update PROGRAMTABLE Set ID='" & ProgID & "', title ='" & ProgramTitle & "', desc='" & ProgramDescription & "' where put some criteria here"
db.Execute strSQL

The above two statements are just templates of SQL queries that you need to fire. As I am not sure about the data types in your table it would be difficult to comment on the single quotes aspect.


It works great.  I just substituted the ' character with a ` character.  That's not too annoying.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial