How To Avoid Duplicate Records In Ms-access.

[smugcool]

HI,

I am having a database in access. Where user imports various data which is in excel format. I want them to stop importing any duplicate record. If any duplicate record is been trying to import by user, access should generate an error files saying record already exists. Kindly suggest how to go ahead.

Thanx in advance
Anup

[Stewart Ross]

Hi Anup. Access will automatically refuse to import or accept duplicates as long as the relationship between tables is defined and referential integrity set between those tables. Duplicate records would violate referential integrity. Access's own error messages to users about such violations are low-level and not user-friendly, so developers would normally replace these with their own.

If you are looking to block duplicates on a table that does not relate to any other you can do so by defining the field or fields which make up the primary key for that table, as there can be no duplicates of primary keys.

It all comes down to ensuring tables are correctly defined, with appropriate primary keys (not just autonumber values if possible).

-Stewart

[smugcool]

Hi Anup. Access will automatically refuse to import or accept duplicates as long as the relationship between tables is defined and referential integrity set between those tables. Duplicate records would violate referential integrity. Access's own error messages to users about such violations are low-level and not user-friendly, so developers would normally replace these with their own.

If you are looking to block duplicates on a table that does not relate to any other you can do so by defining the field or fields which make up the primary key for that table, as there can be no duplicates of primary keys.

It all comes down to ensuring tables are correctly defined, with appropriate primary keys (not just autonumber values if possible).

-Stewart

Thanx Stewart for your prompt reply.

My problem is that I want to avoid those duplicates which is having same value for all fields. consider below example:-
Suppose Field 1 contains A ,Field2 contains B and Field3 contains C...... etc.

If I import Field1 and field2 with similar values access should accept it and should not throw any errors.

But when I will upload all the fields i.e. Field1, field2 and field3..... with same values, then access should deny accepting it. My table does have any relationship with any other table.

Since I cant make primary keys to any field as I am accepting duplicates where some of the fields are matching in the table. But i want all the fields with similar value matched access should avoid it.

[Stewart Ross]

Hi Anup. If it is the case that to define a record as a duplicate you must look at all fields in the record - then in the table design you should set those fields together as the (compound) primary key for that table.

Designing relational tables is about identifying what makes a record unique; if that requires that all fields are part of the primary key, so be it. There is an excellent article on Database Normalisation and Table Design in our howto section.

Otherwise, you have few options. The only other that occurs to me is to use form data entry and run VBA code from the Before Update event of the form to check for duplicates - getting very messy indeed.

-Stewart