CODEWITHSUNDEEP
ms-accessvbams-access-2003
Ben Hamilton
Ben Hamilton

Reputation: 975

Access VBA - detect duplicated records

I have a VBA procedure (behind a form) which does the following ;-

After the import of the data from XML, I want to be able to capture scenarios where the same file is trying to be imported twice (i.e. where the temp table contains IDs which also exist in the backup table), tell the user via msgbox, and then quit the Sub.

I would have thought it'd be possible to do it by error code handling when trying to append, capturing the error about duplicate keys.

I have an append query to append to the backup, and if I run this query via the Query itself, I get warnings etc. about there being duplicates, with option to continue to run the query or not.

However, I am doing this in VBA via OpenQuery (and have turned warnings off), and so nothing happens? I can't seem to capture an error code.

I tried writing a select query to identify any duplicate IDs, and then somehow detect if that query returned any rows, but also can't get this to work as cannot find a way to detect if the query returns any rows or not.

Upvotes: 2

Views: 4028

Answers (1)

HansUp
HansUp

Reputation: 97101

"I am doing this in VBA via OpenQuery (and have turned warnings off), and so nothing happens? I can't seem to capture an error code."

Turning SetWarnings off suppresses information in general, so that is why your expected error message doesn't appear. Many experienced Access developers advise turning SetWarnings off is a bad idea. See this SO question for another example of how frustrating troubleshooting can be with SetWarnings off.

Since you have an "append query", execute it with the DAO.Database .Execute method instead of using OpenQuery.

Dim db As DAO.Database
Dim strInsert As String
strInsert = "INSERT INTO backup_table(ID, fld1, fld2)" & vbCrLf & _
    "SELECT ID, fld1, fld2 FROM temp_table"
Set db = CurrentDb
db.Execute strInsert, dbFailOnError

With SetWarnings on (True) that approach will trigger an error message due to key violations when the backup table already includes ID values you're trying to add.

Separately, if you want a count of the duplicates, this query should give you that.

SELECT Count(*) AS count_of_duplicates
FROM
    temp_table AS t
    INNER JOIN backup_table AS b
    ON t.ID = b.ID

If you want to insert non-duplicate temp_table rows into backup_table, try a query like this ...

INSERT INTO backup_table(ID, fld1, fld2)
SELECT t.ID, t.fld1, t.fld2
FROM
    temp_table AS t
    LEFT JOIN backup_table AS b
    ON t.ID = b.ID
WHERE b.ID Is Null

Upvotes: 1

Related Questions