[RESOLVED] Delete Duplicate records

[matrik02]

I have three field name Negeri,Kompartment,Blok

In this three field name, there are two record which contains same records. How I can delete the record in the database if it same records have already exist in the records. I am not using primary key for this. tables. I used Microsoft access 2003 as my database

.Open "SELECT Negeri,Kompartment,Blok FROM Kompartmen"

[cheenu_vasan]

Try refering the following link

http://www.sql-server-performance.co...icates_p1.aspx

[cheenu_vasan]

Check also the

http://www.cryer.co.uk/brian/sql/sql...duplicates.htm

[matrik02]

I using Microsoft Access as my database

[cheenu_vasan]

For MS Access,

http://support.microsoft.com/kb/209183

http://www.databasejournal.com/featu...le.php/3077791

[matrik02]

Actually, I would like remove the duplicate records through coding in VB 6

.Open "SELECT Negeri,Kompartment,Blok FROM Kompartmen"

[si_the_geek]

Two of the links Cheenu has posted will work for you with a little effort.. one of them is SQL based, and the other has VB code which uses a recordset (but unfortunately a DAO one, so will need to be converted if you use ADO).

[matrik02]

I got it, but I cannot browse the record inside the table. How I can see the duplicate tables in this DeleteDuplicatesCode_v2k.mdb

[matrik02]

i try to convert this code to ADO.. When I test it, it delete all records in the tables when I change this one row to other name.. What the error of this code?

Private Sub cmdDeleteDuplicates_Click()
On Error Resume Next

  Dim db As DAO.Database, rst As DAO.Recordset
  Dim strDupName As String, strSaveName As String
  
  Set db = CurrentDb()
  Set rst = db.openrecordset("qryDuplicates")
  
  Stop
  
  If rst.BOF And rst.EOF Then
    MsgBox "No records to process"
  Else
    rst.MoveFirst
    Do Until rst.EOF
      strDupName = rst.Fields(0) & rst.Fields(1) & rst.Fields(2)
      If strDupName = strSaveName Then
        rst.Delete
      Else
        strSaveName = rst.Fields(0) & rst.Fields(1) & rst.Fields(2)
      End If
      rst.MoveNext
    Loop
    
    Set rst = Nothing
    Set db = Nothing
    
    Call RestoreData
  End If

End Sub

[matrik02]

Why I cannot delete all records contains duplicated record? I only able to delete half of them.

Private Sub Command1_Click()
On Error Resume Next
Set db = New ADODB.Connection
db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\DBMS.mdb;Persist Security Info=False"
Set rst = New ADODB.Recordset
rst.Open "Select * from A", db, adOpenDynamic, adLockOptimistic

  Dim strDupName As String, strSaveName As String
  If rst.BOF And rst.EOF Then
  MsgBox "No records to process"
  Else
  MsgBox "DUPLICATE FOUND DELETE"
    rst.MoveFirst
    Do Until rst.EOF
      strDupName = rst.Fields(0) & rst.Fields(1) & rst.Fields(2)
      If strDupName = strSaveName Then
        rst.Delete
      Else
        strSaveName = rst.Fields(0) & rst.Fields(1) & rst.Fields(2)
      End If
      rst.MoveNext
    Loop
    
    Set rst = Nothing
    Set db = Nothing
    

  End If

End Sub

[matrik02]

I would like to share you all how I remove all the duplicate record in the tables. i used to copy from original tables to the temporary tables. After that, I rename the tables back to the original. Could somebody check this code because I have run this code, and no error found, If you find the error let me know.

rst.Open "Select distinct * into uniqueKompartmen from Kompartmen", conn, adOpenDynamic, adLockOptimistic

Public rst   As ADODB.Recordset
Public Sub duplicate()
Dim catalog As New ADOX.catalog
Dim i As Integer
Set rst = New ADODB.Recordset
On Error GoTo tableexist

'copy duplicate value from table A to table D
rst.Open "Select distinct * into uniqueKompartmen from Kompartmen", conn, adOpenDynamic, adLockOptimistic
        catalog.ActiveConnection = conn

        For i = 0 To catalog.Tables.Count() - 1
            If catalog.Tables(i).Name = "uniqueKompartmen" Then 'If table D found then
                conn.Execute "DROP TABLE Kompartmen" 'Drop tables original

                catalog.Tables(i).Name = "Kompartmen" 'rename to A
                Exit For
            End If
        Next
        
  If (rst.State And adStateOpen) = adStateOpen Then
    rst.Close
    
  End If
    Set rst = Nothing
    Set catalog = Nothing
        
Exit Sub
tableexist:
If Err.Number = -2147217900 Then 'If table uniqueKompartmen already Exist
catalog.ActiveConnection = conn

        For i = 0 To catalog.Tables.Count() - 1
            If catalog.Tables(i).Name = "uniqueKompartmen" Then 'If table D found then
                conn.Execute "DROP TABLE uniqueKompartmen" 'Drop tables original
                Exit For
            End If
        Next

      'copy duplicate value from table A to table D
       rst.Open "Select distinct * into uniqueKompartmen from Kompartmen", conn, adOpenDynamic, adLockOptimistic

        catalog.ActiveConnection = conn

        For i = 0 To catalog.Tables.Count() - 1
            If catalog.Tables(i).Name = "uniqueKompartmen" Then 'If table D found then
                conn.Execute "DROP TABLE Kompartmen" 'Drop tables original"

                catalog.Tables(i).Name = "Kompartmen" 'rename to A"
                Exit For
            End If
        Next

     If (rst.State And adStateOpen) = adStateOpen Then
      rst.Close
    
     End If
     Set rst = Nothing
     Set catalog = Nothing

Else
  MsgBox "An error has occurred.   It is " & Err.Number & " " & Err.Description
End If
End Sub