CODEWITHSUNDEEP
sqlvbams-access
FL.Alx
FL.Alx

Reputation: 71

Fastest way for duplicates remove in Access DB

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.

Upvotes: 0

Views: 3129

Answers (1)

Ali Azam
Ali Azam

Reputation: 2115

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)")

Upvotes: 1

Related Questions