markcrobinson
Registered User.
- Local time
- Today, 06:42
- Joined
- Nov 28, 2017
- Messages
- 14
I realize that there have been several threads on this. I've looked through them can can't find a simple way to delete duplicates.
Running a DELETE Query on a Find Duplicates query throws an error not solved by setting the Unique record to Yes.
I found this online and I like the logic, I've plugged in my info, although I don't understand why the HAVING COUNT(*)>1 throws an error.
Must admit, I don't understand some of the code right after the "With rs".
Of course, if there's an easier way, I'll all "ears".
----------------------------------------------------------------------------------
Function Dedupe()
Dim sSql As String
Dim sOrder_number As String
Dim sItem_id As String
Dim sItem_name As String
Dim sItem_sku As String
Dim sItem_meta As String
Dim lRows As Long
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
sSql = "select Order_number, Item_id, Item_Name, Item_Sku, Item_Meta " _
& "from 2017WooOrdersIn " _
& "group by Order_number, Item_id, Item_Name, Item_Sku, Item_Meta " _
& "having count(*) > 1"
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = CurrentProject.AccessConnection
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Source = sSql
.Open
While Not .EOF
sOrder_number = .Fields(1).Value
sItem_id = .Fields(38).Value
sItem_name = .Fields(39).Value
sItem_sku = .Fields(42).Value
sItem_meta = .Fields(48).Value
sSql = "delete * " _
& "from 2017WooOrdersIn " _
& "where Order_Number = '" & sOrder_number & "' " _
& "and Item_Id = '" & sItem_id & "' " _
& "and Item_name = '" & sItem_name & "' " _
& "and Item_sku = '" & sItem_sku & "' " _
& "and Item_meta = '" & sItem_meta & "' " _
& "from 2017WooOrdersIn " _
& "where Order_Number = '" & sOrder_number & "' " _
& "and Item_Id = '" & sItem_id & "' " _
& "and Item_name = '" & sItem_name & "' " _
& "and Item_sku = '" & sItem_sku & "' " _
& "and Item_meta = '" & sItem_meta & "' "
con.Execute sSql, lRows
.MoveNext
Wend
End With
End Function
Running a DELETE Query on a Find Duplicates query throws an error not solved by setting the Unique record to Yes.
I found this online and I like the logic, I've plugged in my info, although I don't understand why the HAVING COUNT(*)>1 throws an error.
Must admit, I don't understand some of the code right after the "With rs".
Of course, if there's an easier way, I'll all "ears".
----------------------------------------------------------------------------------
Function Dedupe()
Dim sSql As String
Dim sOrder_number As String
Dim sItem_id As String
Dim sItem_name As String
Dim sItem_sku As String
Dim sItem_meta As String
Dim lRows As Long
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
sSql = "select Order_number, Item_id, Item_Name, Item_Sku, Item_Meta " _
& "from 2017WooOrdersIn " _
& "group by Order_number, Item_id, Item_Name, Item_Sku, Item_Meta " _
& "having count(*) > 1"
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = CurrentProject.AccessConnection
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Source = sSql
.Open
While Not .EOF
sOrder_number = .Fields(1).Value
sItem_id = .Fields(38).Value
sItem_name = .Fields(39).Value
sItem_sku = .Fields(42).Value
sItem_meta = .Fields(48).Value
sSql = "delete * " _
& "from 2017WooOrdersIn " _
& "where Order_Number = '" & sOrder_number & "' " _
& "and Item_Id = '" & sItem_id & "' " _
& "and Item_name = '" & sItem_name & "' " _
& "and Item_sku = '" & sItem_sku & "' " _
& "and Item_meta = '" & sItem_meta & "' " _
& "from 2017WooOrdersIn " _
& "where Order_Number = '" & sOrder_number & "' " _
& "and Item_Id = '" & sItem_id & "' " _
& "and Item_name = '" & sItem_name & "' " _
& "and Item_sku = '" & sItem_sku & "' " _
& "and Item_meta = '" & sItem_meta & "' "
con.Execute sSql, lRows
.MoveNext
Wend
End With
End Function