compare tables and delete oldest record from either table (1 Viewer)

lucky245

Registered User.
Local time
Today, 15:44
Joined
Sep 19, 2009
Messages
16
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

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

  • Example.accdb
    440 KB · Views: 540
Last edited by a moderator:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 15:44
Joined
Jul 9, 2003
Messages
16,245
someone tells me an sql will be faster

As a general rule SQL Statements are normally faster, if not always! I suspect you already know this. As to what the SQL Statement should look like, I have no idea! I normally just prat around with it/them until it does what I want. I guess you will need several queries, one calling the next and so on.

The query Builder supplied by Microsoft Access has some useful features, it can build you an "unmatched query" and a couple of other sorts of queries which you might want to look into. Otherwise seeing as I have just bumped this thread up the list a bit, you might get some help from someone who knows what they are doing!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:44
Joined
Oct 29, 2018
Messages
21,360
Hi. This could be a toss up, but some testing would definitely let us know for sure. Using VBA, you can complete all the tasks one step at a time, and by the time you get to the last record, then you're all done. Using SQL, you may have to perform the checks one step at a time, but applying each step to the entire table, instead of one record at a time (like VBA could). If you get the "rules" correctly identified, I might still go with multiple SQL queries executing in succession to do the whole job. But, doing it using VBA is not entirely out of the question too.
 

Mark_

Longboard on the internet
Local time
Today, 08:44
Joined
Sep 12, 2017
Messages
2,111
For me, First step would be a query that matches your source table to your destination table by ID. This would have your date/times. You can them work out how to return ONLY those records where your source had a previous date/time to your destination. This becomes your first delete query.

Second step would be to do a match between your destination and source, returning ONLY those where there is a match. Use the same logic for date/time filter to set this up to be a delete query for your destination table.

Third is the append query you'd already talked about.

For myself, I'd actually have the first query not delete, but instead force a user to review the data to make sure your actually using the latest and greatest. The user can make judgement calls about dates and such that are horribly difficult to get right in programming.

I'd also be setting a "Deleted" flag in your destination table rather than actually deleting the records.
 

lucky245

Registered User.
Local time
Today, 15:44
Joined
Sep 19, 2009
Messages
16
This seems to solve the problem. Slight change in approach as any records in input table will always be the most up to date so I only need to remove the records from the destination table with the same spnumber. Can anyone see any potential issues or cleaner code. "I will add error code after"

Code:
Sub DelDupRec(strTableName As String)
  
    Dim db As Database
    Dim rs As Recordset 'destination table
    Dim rs2 As Recordset 'match query
    Dim strSQL As String 'sql for match
    Dim i As Long
    Dim j As Long
    Dim lngdst As Long
 
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strTableName) 'Destination
    strSQL = "SELECT input.spnumber FROM [input] INNER JOIN dest ON input.spnumber = dest.spnumber GROUP BY input.spnumber;"
    Set rs2 = db.OpenRecordset(strSQL) 'match query
    lngdst = rs.RecordCount + 1 ' destination counter
    rs.MoveFirst
    rs2.MoveFirst
    
    For i = 1 To rs2.RecordCount
        rs.MoveFirst
        For j = 1 To lngdst
            If rs2.Fields(0) = rs.Fields(1) Then
                rs.Delete
                lngdst = lngdst - 1
            End If
            rs.MoveNext
        Next j
       rs2.MoveNext
    Next i
End Sub
 
Last edited by a moderator:

AccessBlaster

Registered User.
Local time
Today, 08:44
Joined
May 22, 2010
Messages
5,830
Do you have an index set on any field? If not try setting an index on "spnumber", if that's the field you are comparing. Just a thought.
 

Users who are viewing this thread

Top Bottom