Remove duplicate records in Access with VBA

JoeS01

Well-known Member
Joined
Jun 25, 2005
Messages
832
I am trying to delete the duplicate records in an Access database.
In the code below, all the records, including duplicates, are inserted in the new tblmyTemp table.

Can anyone please advise where I am going wrong, as I thought DISTINCTROW was intended to remove duplicates.

Any suggestions on how I can get a table without duplicate records?

All help and suggestions very gratefully received.

Code:
Sub Del_Dupes()

' delete duplicate records in a database table

Dim db As DAO.Database, rst As DAO.Recordset
Dim sSQL$

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblTransformer")

sSQL = "SELECT DISTINCTROW INTO tblMyTemp FROM tblTransformer"

DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True

MsgBox "A table WITHOUT DUPLICATES has been created as tblMyTemp", _
  vbOKOnly, "Created"

Set rst = Nothing
Set db = Nothing

End Sub
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
GOT IT!!

After lots of reading and fiddling around, discovered that DISTINCTROW will not work with a single table. So I used DISTINCT *

YESSSSS!!!!!

Correct code shown below :)

Code:
Sub Del_Dupes()

' delete duplicate records in a database table

Dim db As DAO.Database, rst As DAO.Recordset
Dim sSQL$

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblTransformer")

sSQL = "SELECT DISTINCT *"
sSQL = sSQL + "INTO tblMyTemp "
sSQL = sSQL + "FROM tblTransformer"

Stop
DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True

MsgBox "A table WITHOUT DUPLICATES has been created as tblMyTemp", _
  vbOKOnly, "Created"

Set rst = Nothing
Set db = Nothing

End Sub
 
Upvote 0
Is that a valid way to eliminate duplicates???

If I have a table with 40 columns, will that piece of code work?

Thanks
 
Upvote 0
What is the best way to delete duplicates?

I have code in VBA that bugs for large tables.

I have 1.3 million lines and about 50 columns, huge table.

Any idea?
 
Upvote 0
I've been using this code so far:

Sub DeleteDuplicateRecords()
' Deletes exact duplicates from the specified table.
' No user confirmation is required. Use with caution.
StrTableName = InputBox("Enter tablename")


Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strSQL As String
Dim varBookmark As Variant

Set tdf = DBEngine(0)(0).TableDefs(StrTableName)
strSQL = "SELECT * FROM " & StrTableName & " ORDER BY "
' Build a sort string to make sure duplicate records are
' adjacent. Can't sort on OLE or Memo fields,though.
For Each fld In tdf.Fields
If (fld.Type <> dbMemo) And _
(fld.Type <> dbLongBinary) Then
strSQL = strSQL & "[" & fld.Name & "], "
End If
Next fld
' Remove the extra comma and space from the SQL
strSQL = Left(strSQL, Len(strSQL) - 2)
Set tdf = Nothing

Set rst = CurrentDb.OpenRecordset(strSQL)
Set rst2 = rst.Clone
rst.MoveNext
Do Until rst.EOF
varBookmark = rst.Bookmark
For Each fld In rst.Fields
If fld.Value <> rst2.Fields(fld.Name).Value Then
GoTo NextRecord
End If
Next fld
rst.Delete
GoTo SkipBookmark
NextRecord:
rst2.Bookmark = varBookmark
SkipBookmark:
rst.MoveNext
Loop
End Sub
 
Upvote 0
all 50 fields are required to uniquely identify a record? Aren't then any keys in this table?
 
Upvote 0
Maybe not all 50 are needed but I would still want to understand why I can't use the code above.

It seems like it's a size problem... I can a warning message saying "either the size is greater than 2GB or"
 
Upvote 0
I probably would not want to use a recordset loop with such a large table. But it is hard to suggest alternatives when it takes 50 fields to identify a record. Having memo fields adds to the difficulty. The code you are doing is not a typically way to delete dupes, in any case.
 
Upvote 0
Cheap and nasty method;

Build a query. Add every field to the grid EXCEPT the memos -- they shouldn't be used for searching / grouping.
Turn the query into a Totals query by pressing the big Sigma button.
Hold your breath, then run the query. With large datasets and no indexing it could take a while.

Beforehand, try running a Compact & Repair to reduce the szie of the database. It could help with the speed and make you less likely to hit the 2GB limit.

Denis
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top