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.

Possible duplicate of [How can I remove duplicate rows?](https://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows) — Vityata, Nov 23 '17 at 12:00
Why did you post code that has no logic to remove duplicates? — nicomp, Nov 23 '17 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, Nov 23 '17 at 12:03
@nicomp - you mean I should try to add some sql request that does not work for me? — FL.Alx, Nov 23 '17 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, Nov 23 '17 at 12:07
@Vityata - I've not found the way that can help me to delete duplicates rows on "How can I remove duplicate rows?" page. There is no example how to do it with only one column in one table I guess. — FL.Alx, Nov 23 '17 at 12:18
Your select command will return total number of rows. If you require to see duplicates, you must use "group by [base] and having COUNT (Base) > 1 — Krish, Nov 23 '17 at 12:23
@krishKM - thanks for helping me, I will try it now and will update the code here. — FL.Alx, Nov 23 '17 at 12:26
https://stackoverflow.com/a/18934/5448626 -> see this answer and read the Microsoft link it is pointing to. — Vityata, Nov 23 '17 at 12:27
@Vityata I've inserted the code from Microsoft page, but VBA marked the code as incorrect. — FL.Alx, Nov 23 '17 at 13:14

score 1 · Answer 1 · answered Nov 23 '17 at 14:50

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 '17 at 14:50

Ali Azam

2,047
1
16
25

thanks for helping me, but I've get the `Run-Time Error '-2147217887 (80040e21)' Multiple-Step Operation Generated Errors` – FL.Alx Nov 23 '17 at 14:59
Note that this would delete **all** duplicates - I guess the goal is to keep one instance of each duplicate. – Andre Nov 23 '17 at 15:35
@Andre - thanks, I guessed the same, because `SELECT Base FROM AccessBase GROUP BY base HAVING count(*) > 1)` = string value and count number. – FL.Alx Nov 23 '17 at 15:41

Fastest way for duplicates remove in Access DB

1 Answers1