I need to compare a small source table (upto 100 records) with a much larger destination table (20000).
If the ID is the same I need to delete any records (there may be more than one) that are older in the destination table. It doesn't matter if the other fields differ as the newer records will have correct information in them. If the timestamp is identical then I want to delete from source as this table will be appended to the destination table afterwards.
I am going to attempt writing some vba with loops to compare but just in case someone tells me an sql will be faster
:banghead:
Thanks
Current progress but deleting from same table not either
If the ID is the same I need to delete any records (there may be more than one) that are older in the destination table. It doesn't matter if the other fields differ as the newer records will have correct information in them. If the timestamp is identical then I want to delete from source as this table will be appended to the destination table afterwards.
I am going to attempt writing some vba with loops to compare but just in case someone tells me an sql will be faster
:banghead:
Thanks
Current progress but deleting from same table not either
Code:
Option Compare Database
Sub test()
DelDupRec "tableBDes"
End Sub
Code:
Sub DelDupRec(strTableName As String)
Dim db As Database
Dim rst As Recordset
Dim rst2 As Recordset
Dim tdf As TableDef
Dim strSQL As String
Dim varBookmark As Variant
Set db = CurrentDb
Set tdf = db.TableDefs(strTableName)
strSQL = "SELECT * FROM " & strTableName & " ORDER BY spnumber"
strSQL = Left(strSQL, Len(strSQL))
Set tdf = Nothing
Set rst = db.OpenRecordset(strSQL)
Set rst2 = rst.Clone
rst.MoveNext
Do Until rst.EOF
varBookmark = rst.Bookmark
If rst.Fields(1).Value <> rst2.Fields(1).Value Then
GoTo NextRecord
Else
If rst.Fields(2).value > rst2.Fields(2).value Then
GoTo NextRecord
End If
End If
rst.Delete
GoTo SkipBookmark
NextRecord:
rst2.Bookmark = varBookmark
SkipBookmark:
rst.MoveNext
Loop
End Sub
Attachments
Last edited by a moderator: