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.
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