deleting duplicate records in vba

msjeb007

Registered User.
Local time
Today, 15:26
Joined
Mar 9, 2018
Messages
13
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?
 
It would be easier to delete dup if you have autonumber field
 
Agree with arnel or use unique index on whatever is being duplicated.

Can you show us an example of a duplicate?
 
Attached copy
 

Attachments

  • Capture.jpg
    Capture.jpg
    91.3 KB · Views: 152
This came from another system i supposed, cant tell if this us in purpose.
 
OK, I guess the next step is to see your table design(s) and the query SQL. And any related info.
 
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
 
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.
 
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).
 

Users who are viewing this thread

Back
Top Bottom