[RESOLVED] Verify identical records in one database ???
Hi everybody !
I want to verify in one database if there is identical records and keep only one of them...
Here below it's what I tried (Sorry some words are in french):
VB Code:
'Instanciation des variables
Set cn = New ADODB.Connection
Set cn1 = New ADODB.Connection
Set rs = New ADODB.Recordset
Set rs1 = New ADODB.Recordset
'Connection Database
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & ImpressionMDB
cn.Open
cn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & ImpressionMDB
cn1.Open
'Ouverture des recordset
rs.Open "Items", cn, adOpenKeyset, adLockPessimistic, adCmdTable
rs1.Open "Items", cn1, adOpenKeyset, adLockPessimistic, adCmdTable
'Vérifier si item identique
rs.MoveFirst
Do Until rs.EOF
rs1.MoveFirst
Do Until rs1.EOF
[U]If rs.Fields("Description").Value = rs1.Fields("Description").Value Then[/U]
rs.Delete
End If
rs1.MoveNext
Loop
rs.MoveNext
Loop
'Fermeture de la connection
cn.Close
cn1.Close
rs.Close
rs1.Close
Set cn = Nothing
Set cn1 = Nothing
Set rs = Nothing
Set rs = Nothing
I got an error on the underlined line:
Runtime Error -2147217885 (80040e23) : A Given HROW Referred to a Hard-Deleted or Soft-Deleted Row
If you have other suggestion, let me know !
Thanks in advance !
Re: Verify identical records in one database ???
Your logic is wrong. You open th same table in both recordsets and then, for each record in rs you check if there's a record with the same values in rs1. There will be but it'll be the same record rather than a duplicate. Your actually quite lucky it didn't run becuase that would have deleted all your data.
You need to take a slightly different approach. I would recommend using a:-
Code:
SELECT *
FROM Items
INTO tempTable
GROUP BY field1,field2,field3...etc
To create a new table. You will need to replace the Field1,field2 etc with a list of all the fields in your table. Because you've used a group by with all the fields in it this will create a new table with no duplicate records. You can then delete your original Items table and replace it with the contents of the new tempTable.
Re: Verify identical records in one database ???
Quote:
Originally Posted by FunkyDexter
Your logic is wrong. You open th same table in both recordsets and then, for each record in rs you check if there's a record with the same values in rs1. There will be but it'll be the same record rather than a duplicate. Your actually quite lucky it didn't run becuase that would have deleted all your data.
Oooopppsss ! I want to try something instead to ask someone to make it for me...
Quote:
Originally Posted by FunkyDexter
You need to take a slightly different approach. I would recommend using a:-
Code:
SELECT *
FROM Items
INTO tempTable
GROUP BY field1,field2,field3...etc
To create a new table. You will need to replace the Field1,field2 etc with a list of all the fields in your table. Because you've used a group by with all the fields in it this will create a new table with no duplicate records. You can then delete your original Items table and replace it with the contents of the new tempTable.
I'll try that ! Thanks !
