DELETE all duplicate rows in access table based condition
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
DELETE all duplicate rows in access table based condition
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
???
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
???
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: DELETE all duplicate rows in access table based condition
You'll have to decide which record to keep. Do you have criteria for that?
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
Re: DELETE all duplicate rows in access table based condition
No criteria, delete only entire row set and maintain entire rowset with a unique value 12345.HansV wrote:You'll have to decide which record to keep. Do you have criteria for that?
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: DELETE all duplicate rows in access table based condition
Does the table have a primary key field?
If so, what is its name?
If so, what is its name?
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
Re: DELETE all duplicate rows in access table based condition
No primary key field have the table...HansV wrote:Does the table have a primary key field?
If so, what is its name?
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?
Last edited by sal21 on 15 May 2010, 18:10, edited 1 time in total.
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: DELETE all duplicate rows in access table based condition
You'll have to loop through the records; this might be slow if the table has many records.
This assumes that TEST is a number field. If it is a text field, change the line
to
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
Code: Select all
If DCount("*", strTable, "TEST=" & rst!TEST) > 1 Then
Code: Select all
If DCount("*", strTable, "TEST=" & Chr(34) & rst!TEST & Chr(34)) > 1 Then
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
Re: DELETE all duplicate rows in access table based condition
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?
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?
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: DELETE all duplicate rows in access table based condition
The code I posted is supposed to be run from Access.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
Re: DELETE all duplicate rows in access table based condition
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..
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..
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: DELETE all duplicate rows in access table based condition
You can copy the code into a module in the Access database containing the table.
Make it look like this:
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.
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
You can now run the code by clicking anywhere in it and pressing F5.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
Re: DELETE all duplicate rows in access table based condition
Tks for your time, now work fine!HansV wrote:You can copy the code into a module in the Access database containing the table.
Make it look like this:
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).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
You can now run the code by clicking anywhere in it and pressing F5.
But is possible to call this code in module via VBA for excel or VB6 Classic?
Similar:
sub uno
call RemoveDuplicates
end sub
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: DELETE all duplicate rows in access table based condition
You'd have to modify the code to run Access through Automation.
It'd be simpler if the table had a primary key.
It'd be simpler if the table had a primary key.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
Re: DELETE all duplicate rows in access table based condition
ok for thath!HansV wrote:You'd have to modify the code to run Access through Automation.
It'd be simpler if the table had a primary key.
But is possible to call a query stored in access database via VB6 classic or via vba For Excel???
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: DELETE all duplicate rows in access table based condition
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.
Or if you want to execute an action query, use the Execute method of the connection object.
Best wishes,
Hans
Hans