Duplicate records

  • Thread starter Thread starter Glenda
  • Start date Start date
G

Glenda

What causes and fixes are there for the creation of exact
duplicate records, especially when the primary key is set
to no duplicates?
 
What causes and fixes are there for the creation of exact
duplicate records, especially when the primary key is set
to no duplicates?
Click to expand...

All primary keys are, by definition, "no duplicates". Are you getting
duplicate records even though there is a Primary Key set!? This
certainly should NOT be happening. Of course if you have an Autonumber
field each record will (should... there are some bugs that cause
duplicate autonumbers) be unique by virtue of the autonumber field;
unless you have a unique index on some other field or combination of
fields, you could still get "data duplicates" which differ only in the
value of the autonumber.

Please post some more details: table structure, identity of the PK,
how you're adding the records, etc.
 
I was mistaken on the primary key. This table does not
have a primary key. It is an AutoNumber field. However,
it does not create the duplicate until a query runs moving
the records into an Archived table where the corresponding
field is not an Autonumber (It is just a number field).

The records are added to this table one of two ways: 1. a
form within this database or 2. a form from a separate
database that has links to the Main Database.

Is this enough info?
 
I was mistaken on the primary key. This table does not
have a primary key. It is an AutoNumber field. However,
it does not create the duplicate until a query runs moving
the records into an Archived table where the corresponding
field is not an Autonumber (It is just a number field).

The records are added to this table one of two ways: 1. a
form within this database or 2. a form from a separate
database that has links to the Main Database.

Is this enough info?
Click to expand...

Which table is getting the duplicates? If it's the table with the
autonumber, are you getting exact duplicates (i.e. the autonumber
value being reused), or data duplicates (the records differ only in
the autonumber)? You say that you have "a form from a separate
database that has links to the Main database" - please explain; is the
database where the table is getting dups the "main database", and is
this Form bound to that table?

There is nothing in what you describe which would *prevent* duplicates
from being added; Access will not take any action to prevent
duplicates unless you set up something to do so (a Primary Key, which
every table should have; and/or a unique Index; and/or VBA code on the
Forms to check for duplicates before they are added).
 
I am getting exact duplicates (with the autonumber value
being reused); however, I never see them in the main table
where the autonumber is generated. They appear
sporadically in the Archives table where the field is just
a number field. The table that is getting the dups is
the "main database" and the form is bound to the table.

I believe the person who originally set up the database
chose not to make the autonumber field a primary key
because then the records would not be able to be archived
into the Archive table using an append query.
 
I am getting exact duplicates (with the autonumber value
being reused); however, I never see them in the main table
where the autonumber is generated. They appear
sporadically in the Archives table where the field is just
a number field. The table that is getting the dups is
the "main database" and the form is bound to the table.
Click to expand...

Do you have the latest JET service pack? Check the version number for
msjet40.dll. The duplicate autonumbers IS A BUG.
I believe the person who originally set up the database
chose not to make the autonumber field a primary key
because then the records would not be able to be archived
into the Archive table using an append query.
Click to expand...

This person was mistaken, then. There is NO reason why having a
primary key in one table should impede archiving data into another
table. The Archive table might need to have *a different* primary key,
but it should still have a Primary Key, otherwise you would never be
able to identify which record is which!
 
Back
Top