Hello I am trying to create a VBA script which removes duplicates from a single table. It searches for duplicates based on two fields (IDtable1 and IDtable2) puts them in a string and then if the string is a duplicate to the one preceeding then it deletes that line.
That part is ok, but there seems to be an overflow problem. The table is 'overflowing' on the ID (autonumber) of the table which should be straightforward enough. Debugging it highlights the line id1 = rstJunction("ID").Value. I've set id1 as an Integer which I thought would match up nicely, but notice the ID numbers are quite high (in excess of 79,000) and as a result there is an overflow error.
I then tried setting id1 as either Long, Single or Double and the error that I get is the following:
Run-time error '-2147217913 (80040e07)': Data type mismatch in criteria expression. The debugging highlight goes over the line cn1.Execute SQLd
Perhaps something with my SQL command?
Here is the code, minus the open recordset such-and-such:
Dim strDuplicate1 As String
Dim strDuplicate2 As String
Dim id1 As Long
Dim SQLd As String
strDuplicate1 = ""
strDuplicate2 = ""
id1 = 0
If rstJunction.BOF And rstJunction.EOF Then
MsgBox "No matches found"
Else
rstJunction.MoveFirst
rstJunction.MoveNext
id1 = rstJunction("ID").Value
Do Until rstJunction.EOF
strDuplicate1 = rstJunction("IDtable1").Value & rstJunction("IDtable2").Value
If strDuplicate1 = strDuplicate2 Then
SQLd = "DELETE FROM tblJunction WHERE ID = ('" & id1 & "')"
cn1.Execute (SQLd)
Else
strDuplicate2 = rstJunction("IDtable1").Value & rstJunction("IDtable2").Value
End If
id1 = 0
rstJunction.MoveNext
Loop
End If
That part is ok, but there seems to be an overflow problem. The table is 'overflowing' on the ID (autonumber) of the table which should be straightforward enough. Debugging it highlights the line id1 = rstJunction("ID").Value. I've set id1 as an Integer which I thought would match up nicely, but notice the ID numbers are quite high (in excess of 79,000) and as a result there is an overflow error.
I then tried setting id1 as either Long, Single or Double and the error that I get is the following:
Run-time error '-2147217913 (80040e07)': Data type mismatch in criteria expression. The debugging highlight goes over the line cn1.Execute SQLd
Perhaps something with my SQL command?
Here is the code, minus the open recordset such-and-such:
Dim strDuplicate1 As String
Dim strDuplicate2 As String
Dim id1 As Long
Dim SQLd As String
strDuplicate1 = ""
strDuplicate2 = ""
id1 = 0
If rstJunction.BOF And rstJunction.EOF Then
MsgBox "No matches found"
Else
rstJunction.MoveFirst
rstJunction.MoveNext
id1 = rstJunction("ID").Value
Do Until rstJunction.EOF
strDuplicate1 = rstJunction("IDtable1").Value & rstJunction("IDtable2").Value
If strDuplicate1 = strDuplicate2 Then
SQLd = "DELETE FROM tblJunction WHERE ID = ('" & id1 & "')"
cn1.Execute (SQLd)
Else
strDuplicate2 = rstJunction("IDtable1").Value & rstJunction("IDtable2").Value
End If
id1 = 0
rstJunction.MoveNext
Loop
End If
