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
– nicomp, Commented Nov 23, 2017 at 12:01
Thanks for the link Vityata, but I have no idea how to add the SQL request into my VBA code. — FL.Alx
– FL.Alx, Commented Nov 23, 2017 at 12:03
@nicomp - you mean I should try to add some sql request that does not work for me? — FL.Alx
– FL.Alx, Commented Nov 23, 2017 at 12:07
@FL.Alx - in the part Set rs = cn.Execute("SELECT COUNT (Base) FROM AccessBase") write the SQL from the duplicated subject. — Vityata
– Vityata, Commented Nov 23, 2017 at 12:07

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

1

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

answered Nov 23, 2017 at 14:50

Ali Azam

2,1131 gold badge18 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

FL.Alx Over a year ago

thanks for helping me, but I've get the Run-Time Error '-2147217887 (80040e21)' Multiple-Step Operation Generated Errors

Andre Over a year ago

Note that this would delete all duplicates - I guess the goal is to keep one instance of each duplicate.

FL.Alx Over a year ago

@Andre - thanks, I guessed the same, because SELECT Base FROM AccessBase GROUP BY base HAVING count(*) > 1) = string value and count number.

Collectives™ on Stack Overflow

Fastest way for duplicates remove in Access DB

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related