Remove duplicate records in Access with VBA

[JoeS01]

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.

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

 

[SydneyGeek]

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

 

[jackd]

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"

I don't really have keys.

You are not using any of the fundamentals of RDBMS. You are really working against some of these, and making things harder for yourself than necessary.
Read this -
http://forums.aspfree.com/attachment.php?attachmentid=4712

Good luck.