Removing duplicate rows from .mdb Database from excel vba

Question

My code so far: (-getting an error on the alt statement...)

Sub Test()
    DeleteDuplicates_Database "Mytable", ThisWorkbook.Path & "\DATA.mdb"
End Sub

Sub DeleteDuplicates_Database(sData_Sht_Table As String, sPathToDatabase As String)
    ' delete duplicate records in a database table
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sSQL$

    Application.ScreenUpdating = False

    'Open connection to database
    Set db = OpenDatabase(sPathToDatabase)
    Set rs = db.OpenRecordset(sData_Sht_Table)

    sSQL = "ALTER IGNORE TABLE " & sData_Sht_Table & " ADD UNIQUE (EventID)"

    db.Execute sSQL

    db.Close
    Set rs = Nothing
    Set db = Nothing
    Application.ScreenUpdating = True

End Sub

just a question, why not use a `query` inside the DB ? this is exactly what it's used for — Shai Rado, Sep 03 '16 at 16:03
Because this is part of a macro I'm running on excel. This way I can switch between databases and still run the same macro on excel. Is this possible to achieve? I've got things working, it's just that bit that is erroring failing... I've ran other queries and it went smoothly so far. — TCN, Sep 03 '16 at 16:06
@dbmitch I was attempting this based on the most voted answer on this question http://stackoverflow.com/questions/2630440/how-to-delete-duplicates-on-a-mysql-table — TCN, Sep 03 '16 at 16:15
I've also seen things like this: SELECT DISTINCT * INTO TempTable FROM OriginalTable (But I wasn't able to get that to work either, it just copied all rows) — TCN, Sep 03 '16 at 16:17
@dbmitch I'm not familiar with SQL (nor MySQL), I didn't know there was a difference. Can you help me out? — TCN, Sep 03 '16 at 16:20
This begs the question of why you don't have a primary key defined in the target table. — Comintern, Sep 03 '16 at 16:38
I'll look into that. One of my columns does have a unique identifier. By defining a primary key will the duplicate row entries get merged? — TCN, Sep 03 '16 at 16:48
I see.... Got it! Your comment is not an answer to my question, but helped me understand the whole thing. Thank you. — TCN, Sep 03 '16 at 17:02
It's not SQL vs MySQL - It's MySQL vs MS-Access. Both have their own versions of SQL. To get help here you'll have to supply a lot more information about your table fields and what duplicates you'd want to remove. To start with you should run the Query Wizard in ms-access to show you what your duplicate rows look like - and maybe that would help you decide which rows would be deleted — dbmitch, Sep 03 '16 at 17:07

PaichengWu · Answer 1 · 2016-09-03T16:30:50.910

1

I am not farmilier with DAO, but I will do this with ADO.

strSQL = "SELECT DISTINCT * INTO new_table FROM " & sData_Sht_Table & " ;"
AdoConnection.Execute strSQL

Note: not tested.

edited Sep 03 '16 at 16:30

answered Sep 03 '16 at 16:25

PaichengWu

2,649
1
14
28

But I still have duplicates, the data has simply been copied from one table to another. – TCN Sep 03 '16 at 16:46
+1 your answer. It works provided I correct the variable names. (It has always worked, but I had no duplicates. I thought this query was only copying my entire table) – TCN Sep 03 '16 at 17:08

score 1 · Accepted Answer · answered Sep 03 '16 at 16:30

1

I've also seen things like this:

SELECT DISTINCT * INTO TempTable FROM OriginalTable

(But I wasn't able to get that to work either, it just copied all rows)

Then you have no dupes. It's that simple.

answered Sep 03 '16 at 16:30

Gustav

53,498
7
29
55

But I still have duplicates, the data has simply been copied from one table to another. – TCN Sep 03 '16 at 16:40
The reason why all the data was being copied is that I had no dupçlicate rows... the primary key was set up but I thought it was just a standard row counter that didn't matter for anything else. – TCN Sep 03 '16 at 17:05
Indeed I had no duplicates. :) – TCN Sep 03 '16 at 17:05

Removing duplicate rows from .mdb Database from excel vba

2 Answers2