MS Access duplicates remove from Excel VBA

[Fixed]

How to delete the duplicates rows in a single MS Access column?

I'v got the code for Excel's VBA:

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

I guess "set rowcount" is not supported by MS Access.
I've asked this before at Stackoverflow, but there is still no solution.

 

[VHS01]

If I understand correctly, you trying to use Excel to delete duplicates in the Access database.
The question that jumps to mind is why not just run a remove duplicates query in Access?
Trying to understand why use Excel to do this??

How to delete the duplicates rows in a single MS Access column?

I'v got the code for Excel's VBA:

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

I guess "set rowcount" is not supported by MS Access.
I've asked this before at Stackoverflow, but there is still no solution.

 

[Fixed]

Yes, you understand correctly, I'm trying to use Excel for this.
Excel is the program I use most frequently.
The data that contains in my MS Access file is transferred from CSV file via VBA Excel.
I'm trying to find the fastest way to delete the duplicate rows in the file with about 50 millions of rows.
I've tried the 5 methods in VBA Excel including methods with Collection and Dictionary objects that seem too slow for me.
And now I'm trying to use some SQL bases for this task.
The next one I guess is SQL Lite.
AFAIK there is no difference between the queries in Access and the queries in VBA Excel through ADODB.Connection.
Am I right?
If you know the query that works in Access directly, show it please.

If I understand correctly, you trying to use Excel to delete duplicates in the Access database.
The question that jumps to mind is why not just run a remove duplicates query in Access?
Trying to understand why use Excel to do this??

 

[VHS01]

Let's start here.
In Access, under the Create tab, select Query Wizard and you will have the choice to make a Find Duplicates query.
Once you have that working, it will show you all the duplicates.
Try that and let us know. At that point you may wan to take it to the Access forum as well.
Regards