delete duplicate records

[br549]

I need to delete duplicate records in a table (the records are exact
duplicates in every field, I have no control over duplicates being there).

I found VBA code from:
http://www.databasejournal.com/features/msaccess/article.php/3077791
that claims to do this. I've never used VBA in Access (just Excel). When I
paste this code into a new module and press the run button, it asks for a
Macro Name. There are no "macros" in the database and it doesn't recognize
the name of the Sub when I type it in. Any suggestions about what I'm doing
wrong?

thanks

 

[Mika Oukka]

I have created a code that creates 3 action queries and 1 temporary table to
do the thing. Hope this helps

Regards,

Mika Oukka
Mika Oukka Consulting
www.onlinetuki.fi


Sub RemoveDublicates(TblWDublicates)
'******************************
'This code uses names:
TblTempWNoDublicates,1QRyWNoDublicates,2QryDeleteDublicates,3QryAppendUniqueRecords
'To avoid conflicts, make sure that you do not have tables/queries of
same name in your database
'Created by Mika Oukka
'Mika Oukka Consulting, www.onlinetuki.fi
'******************************
DoCmd.SetWarnings False
Dim dbs As Database, qdf As QueryDef, rst As Recordset, QryName
Set dbs = CurrentDb

QryName = "1QRyWNoDublicates"
Set qdf = dbs.CreateQueryDef(QryName)
qdf.SQL = "SELECT DISTINCT " & TblWDublicates & ".* INTO
TblTempWNoDublicates FROM " & TblWDublicates & ";"
DoCmd.OpenQuery QryName
DoCmd.DeleteObject acQuery, QryName

QryName = "2QryDeleteDublicates"
Set qdf = dbs.CreateQueryDef(QryName)
qdf.SQL = "DELETE " & TblWDublicates & ".* FROM " & TblWDublicates &
";"
DoCmd.OpenQuery QryName
DoCmd.DeleteObject acQuery, QryName

QryName = "3QryAppendUniqueRecords"
Set qdf = dbs.CreateQueryDef(QryName)
qdf.SQL = "INSERT INTO " & TblWDublicates & " SELECT
TblTempWNoDublicates.* FROM TblTempWNoDublicates;"
DoCmd.OpenQuery QryName
DoCmd.DeleteObject acQuery, QryName

DoCmd.DeleteObject acTable, "TblTempWNoDublicates"
DoCmd.SetWarnings True
End Sub

 

[Mika Oukka]

to call the code you of course create a following type of code:

Sub DoIt
RemoveDublicates "MyTableName"
End sub

....and allways create backup of the database before running code.

Mika Oukka