I have a find duplicate query that found duplicate records in my Parts table. I wrote VBA code to run the query qryDuplicateParts when I opened my form PartsListing. Is there a way in VBA to delete the duplicate records? Could I have created VBA code to find and delete the duplicate records?
deleting duplicate records in vba
- Thread starter msjeb007
- Start date
arnelgp
..forever waiting... waiting for jellybean!
- Local time
- , 02:43
- Joined
- May 7, 2009
- Messages
- 20,276
I think this is trabs history.
If you really want to get rid of the dups here us a simple one.
Copy the table (ctrl-c)(ctrl-v).
Select structure only abd nane the new table.
Bring the new tabke in design view.
Create index with no dup on all field
Save.
Apoend the old table to new one.
Verify. Uf all ok dekete the old and rename the new
If you really want to get rid of the dups here us a simple one.
Copy the table (ctrl-c)(ctrl-v).
Select structure only abd nane the new table.
Bring the new tabke in design view.
Create index with no dup on all field
Save.
Apoend the old table to new one.
Verify. Uf all ok dekete the old and rename the new
Cronk
Registered User.
- Local time
- , 04:43
- Joined
- Jul 4, 2013
- Messages
- 2,799
msjeb007
As to your initial question "Is there a way in VBA to delete the duplicate records?", the answer is yes.
You do need a unique index in the table to identify each specific record as already advised by others. When I am importing data with duplicates, I use a query to identify duplicates, then open a recordset of that query which has one occurence of each duplicate with grouping on all fields that determine that it is a duplicate, loop through that recordset and open another recordset containing the set of records that are the set of each duplicate, and loop through that, deleting all but the first occurrence.
When all duplicates are removed, add a unique composite index based on all of the relevant fields so duplicates cannot subsequently cannot be added to the table in the future.
As to your initial question "Is there a way in VBA to delete the duplicate records?", the answer is yes.
You do need a unique index in the table to identify each specific record as already advised by others. When I am importing data with duplicates, I use a query to identify duplicates, then open a recordset of that query which has one occurence of each duplicate with grouping on all fields that determine that it is a duplicate, loop through that recordset and open another recordset containing the set of records that are the set of each duplicate, and loop through that, deleting all but the first occurrence.
When all duplicates are removed, add a unique composite index based on all of the relevant fields so duplicates cannot subsequently cannot be added to the table in the future.
- Local time
- Today, 14:43
- Joined
- Jan 23, 2006
- Messages
- 15,549
If you have a table structure with appropriate unique composite indexes as Cronk identified, and you take your existing data and attempt to add it to this new indexed table, duplicates will be rejected (Error 3022) by the database software automatically.
This just won't add the duplicates.
However, Cronk's solution gives you some backup. And he adds the composite index after he has resolved the duplicate issue in the data with query(s).
This just won't add the duplicates.
However, Cronk's solution gives you some backup. And he adds the composite index after he has resolved the duplicate issue in the data with query(s).
