By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,651 Members | 1,694 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,651 IT Pros & Developers. It's quick & easy.

How To Avoid Duplicate Records In Ms-access.

P: 81
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
Jul 23 '08 #1
Share this Question
Share on Google+
3 Replies


Expert Mod 2.5K+
P: 2,545
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
Jul 23 '08 #2

P: 81
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.
Jul 23 '08 #3

Expert Mod 2.5K+
P: 2,545
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
Jul 23 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.