Re: Re: How do I avoid inserting duplicate numbers into an Access table?

[Anonymous Monk]

Hi there, thank you for your advice. I really learned from what you wrote. But I still have some prolem. When I was trying to set the Access table to refuse duplicate values, the program simply stopped and gave error message when there were duplicated values. So I want to first read what are already in the table into an array, lets say array A1, and compare the values I intent to insert into the table with the values in A1. If a value has already been in A1, I will discard it and go to the next value. If the next value is not in A1, I will put it into an array A2. After this process ends, I then will insert the values in A2 into the Access table. My problem is that the values in A2 contains some values in A1. I do not know why.

[bmann]

Here is the line that is exiting your script:

die qq(SQL fail "$rc":), $db->Error(),qq(n) if $rc;

Just handle the error differently. Check $rc or $db->Error(), and continue with the next insert if the error points to a duplicate value.

# untested
if ($rc == WHATEVER_ERROR_IS_GENERATED_BY_DUPLICATE) {
    # you can also test ($db->Error() =~ /some value/)
    next;
} else {
    die qq(SQL fail "$rc":), $db->Error(),qq(n);
}

Your script will still die if the insert fails for a different reason.

HTH...

[Anonymous Monk]

Access has a query wizard to find duplicate values in a table.
First, add an autoincrement column to the table like others suggested.
Second, run the Find Duplicates query wizard to generate a query that finds all the duplicate rows.

Use that  Find Duplicates query as the basis to generate and fine tune a  Delete query that gets rid of the duplicate rows by eliminating the rows having an autoincrement value higher the lowest one found for each duplicate.

Then delete the autoincrement column from the table and then re-add it again. This should work as long as you haven't used the column or table in a Relationship.

Now you should have consecutively numbered rows with clean nonduplicated data in a table that generates it's unique row autocount ID automatically.