Printable View
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
Code:.Open "SELECT Negeri,Kompartment,Blok FROM Kompartmen"
Try refering the following link
http://www.sql-server-performance.co...icates_p1.aspx
Check also the
http://www.cryer.co.uk/brian/sql/sql...duplicates.htm
I using Microsoft Access as my database
Actually, I would like remove the duplicate records through coding in VB 6
Quote:
.Open "SELECT Negeri,Kompartment,Blok FROM Kompartmen"
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).
I got it, but I cannot browse the record inside the table. How I can see the duplicate tables in this DeleteDuplicatesCode_v2k.mdb
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?
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
Why I cannot delete all records contains duplicated record? I only able to delete half of them.
Code: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
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.
Code:rst.Open "Select distinct * into uniqueKompartmen from Kompartmen", conn, adOpenDynamic, adLockOptimistic
Code: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