Fastest way for duplicates remove in Access DB

Question

I manage the Access MDB files from Excel VBA mode, and want to find the fastest way to delete duplicates records (Memo type, maximum strings length is about 400 symbols) in file with millions of rows.

Sub AccessDB()

Dim db_file As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

    db_file = "c:\Files\"
    db_file = db_file & "accdb.mdb"

    Set cn = New ADODB.Connection

    cn.ConnectionString = _
        "Provider=Microsoft.Ace.OLEDB.12.0;" & _
        "Data Source=" & db_file & ";" & _
        "Persist Security Info=False"
    cn.Open

   'duplicates delete----------------------------------
    Set rs = cn.Execute("SELECT Base, count(*)" & _
    "FROM AccessBase GROUP BY Base HAVING count(*) > 1")

    cn.Execute ("set rowcount 1;" &_
    "delete from AccessBase where Base = rs.Fields(0)")
   '-----------------------------------------------------

    cn.Close

End Sub

There is only one column ("Base") in one table ("AccessBase"). I've tried to delete the duplicates strings in duplicates delete block, but there are some mistakes I guess.

Why did you post code that has no logic to remove duplicates? — nicomp
Thanks for the link Vityata, but I have no idea how to add the SQL request into my VBA code. — FL.Alx
@nicomp - you mean I should try to add some sql request that does not work for me? — FL.Alx
@FL.Alx - in the part Set rs = cn.Execute("SELECT COUNT (Base) FROM AccessBase") write the SQL from the duplicated subject. — Vityata

Ali Azam Ali Azam · Accepted Answer · 2017-11-23T14:50:18

Use a single query in duplicates delete block instead of two query at a time

cn.Execute ("Delete from AccessBase where Base IN (SELECT Base FROM AccessBase GROUP BY base HAVING count(*) > 1)")

Fastest way for duplicates remove in Access DB

1 Answers