[RESOLVED] Verify identical records in one database ???

[DubweiserTM]

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):

'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 !

[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.

You need to take a slightly different approach. I would recommend using a:-

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.

[DubweiserTM]

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...

You need to take a slightly different approach. I would recommend using a:-

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 !