DELETE all duplicate rows in access table based condition

[sal21]

Have a table in access database with many fields.
Field TEST contain possible duplicate values.
How to delete entire rows base duplicates value in TEST field?

Example:

before query
TEST
12345
12345
12345

after deleting query

TEST
12345

???

[HansV]

You'll have to decide which record to keep. Do you have criteria for that?

[sal21]

You'll have to decide which record to keep. Do you have criteria for that?

No criteria, delete only entire row set and maintain entire rowset with a unique value 12345.

[HansV]

Does the table have a primary key field?
If so, what is its name?

[sal21]

Does the table have a primary key field?
If so, what is its name?

No primary key field have the table...

But after a little thinking...
Not is possile to select all records based a distinc selection from field INDICE and recopy uniques records in a new table???
Delete the old table
Rename the new table with old name of first table
Wath you think about?

[HansV]

You'll have to loop through the records; this might be slow if the table has many records.

Code: Select all

Sub RemoveDuplicates()
  Dim cnn As ADODB.Connection
  Dim rst As New ADODB.Recordset
  Dim strTable As String
  ' Set the connection here
  Set cnn = ...
  ' Substitute the name of the table here
  strTable = "NAMEOFTABLE"
  rst.Open strTable, cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
  Do While Not rst.EOF
    If DCount("*", strTable, "TEST=" & rst!TEST) > 1 Then
      rst.Delete
    End If
    rst.MoveNext
  Loop
End Sub

This assumes that TEST is a number field. If it is a text field, change the line

Code: Select all

    If DCount("*", strTable, "TEST=" & rst!TEST) > 1 Then

to

Code: Select all

    If DCount("*", strTable, "TEST=" & Chr(34) & rst!TEST & Chr(34)) > 1 Then

[sal21]

Hans sorry me but have error.

My real name of table is TBuno
My real name of field where possible dupes is Campo1
Field Campo1 is formatted text

and

code go in error in:
If DCount("*", strTable, "TEST=" & Chr(34) & rst!TEST & Chr(34)) > 1 Then

but i need to dimesion Dcount?

[HansV]

The code I posted is supposed to be run from Access.

[sal21]

ahhhhhhhhhhhhhhhhh....
I never used macro code in access but i know a liitle vba and vb6, can you change for tath? or step by step for access..

[HansV]

You can copy the code into a module in the Access database containing the table.
Make it look like this:

Code: Select all

Sub RemoveDuplicates()
  Dim cnn As ADODB.Connection
  Dim rst As New ADODB.Recordset
  Dim strTable As String
  ' Set the connection here
  Set cnn = CurrentProject.Connection
  ' Substitute the name of the table here
  strTable = "TBUNO"
  rst.Open strTable, cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
  Do While Not rst.EOF
    If DCount("*", strTable, "Campo1=" & rst!Campo1) > 1 Then
      rst.Delete
    End If
    rst.MoveNext
  Loop
End Sub

Select Tools | References... in the Visual Basic Editor and make sure that there is a reference to the Microsoft ActiveX Data Objects ... Library (... is a number that depends on the version).
You can now run the code by clicking anywhere in it and pressing F5.

[sal21]

You can copy the code into a module in the Access database containing the table.
Make it look like this:

Code: Select all

Sub RemoveDuplicates()
  Dim cnn As ADODB.Connection
  Dim rst As New ADODB.Recordset
  Dim strTable As String
  ' Set the connection here
  Set cnn = CurrentProject.Connection
  ' Substitute the name of the table here
  strTable = "TBUNO"
  rst.Open strTable, cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
  Do While Not rst.EOF
    If DCount("*", strTable, "Campo1=" & rst!Campo1) > 1 Then
      rst.Delete
    End If
    rst.MoveNext
  Loop
End Sub

Select Tools | References... in the Visual Basic Editor and make sure that there is a reference to the Microsoft ActiveX Data Objects ... Library (... is a number that depends on the version).
You can now run the code by clicking anywhere in it and pressing F5.

Tks for your time, now work fine!
But is possible to call this code in module via VBA for excel or VB6 Classic?
Similar:
sub uno
call RemoveDuplicates
end sub

[HansV]

You'd have to modify the code to run Access through Automation.

It'd be simpler if the table had a primary key.

[sal21]

You'd have to modify the code to run Access through Automation.

It'd be simpler if the table had a primary key.

ok for thath!

But is possible to call a query stored in access database via VB6 classic or via vba For Excel???

[HansV]

Yes, you can open a recordset on a query the same way you can open a recordset on a table.
Or if you want to execute an action query, use the Execute method of the connection object.