Comparing Recordsets, add new records

[The Nine Inch Ninja]

Morning All.

I have a DB of Delayed Transfers of Care (I work for the NHS)

I need to compare the new information to be entered into the table (DTOC Import Table) with information already in the main table (DTOC Data Table) before inserting it. This is to ensure that no duplicates are entered.

The code I have knocked up so far takes ages to run (and could very well be incorrect!)

    Dim DB As Database
    Dim RS As Recordset     'this is the existing records in the database
    Dim RS2 As Recordset    'this is the records in the DTOC Import Temp Table
    Dim RecCount As Integer
    
    Set DB = CurrentDb()
    Set RS = DB.OpenRecordset("DTOC Data Table", dbOpenDynaset)
    Set RS2 = DB.OpenRecordset("IP001-2: Converted to Date", dbOpenDynaset)
    
    RecCount = 0
    
    RS.MoveLast
    RS.MoveFirst
    
    RS2.MoveLast
    RS2.MoveFirst
    
        
    rsYear = RS![Year]
    rsWeekEndDate = RS![Week Ending Date]
    rsProvShaCode = RS![Provider SHA Code]
    rsProvShaName = RS![Provider SHA Name]
    rsProvOrgCode = RS![Provider Org Code]
    rsProvOrgName = RS![Provider Org Name]
    rsLAC = RS![Local Authority Code]
    rsLAN = RS![Local Authority Name]
    rsAccNonAcc = RS![Acute or Non Acute]
    rsRFD = RS![Reason For Delay]
    rsNHSA = RS![NHS A SUM]
    rsNHSB = RS![NHS B SUM]
    rsSCA = RS![Social Care A SUM]
    rsSCB = RS![Social Care B SUM]
    rsBothA = RS![Both A SUM]
    rsBothB = RS![Both B SUM]
    
    rs2Year = RS2![Year]
    rs2WeekEndDate = RS2![Week Ending]
    rs2ProvShaCode = RS2![Provider SHA Code]
    rs2ProvShaName = RS2![Provider SHA Name]
    rs2ProvOrgCode = RS2![Provider Org Code]
    rs2ProvOrgName = RS2![Provider Org Name]
    rs2LAC = RS2![Local Authority Code]
    rs2LAN = RS2![Local Authority Name]
    rs2AccNonAcc = RS2![Acute or Non Acute]
    rs2RFD = RS2![Reason For Delay]
    rs2NHSA = RS2![NHS A SUM]
    rs2NHSB = RS2![NHS B SUM]
    rs2SCA = RS2![Social Care A SUM]
    rs2SCB = RS2![Social Care B SUM]
    rs2BothA = RS2![Both A SUM]
    rs2BothB = RS2![Both B SUM]
        
        
    Do Until RS.EOF
    
        Do Until RS2.EOF
        
            
                If rsWeekEndDate <> rs2WeekEndDate Then
                
                    If rsProvShaCode <> rs2ProvShaCode Then
                    
                        If rsProvOrgCode <> rs2ProvOrgCode Then
                        
                            If rsLAC <> rs2LAC Then
                            
                                If rsAccNonAcc <> rs2AccNonAcc Then
                                
                                    If rsRFD <> rs2RFD Then
                                    
                                        RS.AddNew
                                        RS![Year] = rs2Year
                                        RS![Week Ending Date] = rs2WeekEndDate
                                        RS![Provider SHA Code] = rs2ProvShaCode
                                        RS![Provider SHA Name] = rs2ProvShaName
                                        RS![Provider Org Code] = rs2ProvOrgCode
                                        RS![Provider Org Name] = rs2ProvOrgName
                                        RS![Local Authority Code] = rs2LAC
                                        RS![Local Authority Name] = rs2LAN
                                        RS![Acute or Non Acute] = rs2AccNonAcc
                                        RS![Reason For Delay] = rs2RFD
                                        RS![NHS A SUM] = rs2NHSA
                                        RS![NHS B SUM] = rs2NHSB
                                        RS![Social Care A SUM] = rs2SCA
                                        RS![Social Care B SUM] = rs2SCB
                                        RS![Both A SUM] = rs2BothA
                                        RS![Both B SUM] = rs2BothB
                                        RS.Update
                                        RS2.MoveNext
                                        RecCount = RecCount + 1
                                        
                                    Else
                                                                                    
                                    End If
                                
                                Else
                                                    
                                End If
                                
                            Else
                            
                            End If
                            
                        Else
                                      
                        End If
                    
                    Else
                               
                    End If
                    
                Else
                   
                   RS2.MoveNext
                   
                End If
            
        Loop
        
        RS.MoveNext
        
    Loop
    
Debug.Print "Records Added: " & RecCount
MsgBox "Records Added: " & RecCount, vbInformation

There are 6 fields that have to match for the record to be identical (unimportant) these are:

Week End
Provider SHA Code
Provider Org Code
Local Authority Code
Acute or Non Acute
Reason for Delay.

The method above should (hopefully) loop through the existing data(comparing against the data to import) and entering any records that do not match.

any ideas on where i've gone wrong? (probably all of it!)

Any help is greatly appreciated!!

Cheers,

Ben

 

[MarkK]

With stuff like this think SQL, which is blistering fast.
You can create a query that joins your two tables on all the fields that would constitute a match. Whatever records are returned are your duplicates, so handle those. Delete them or flag them for further checking. Once you've isolated those everything that's left can simply be dumped into the main table using an append query. This will complete in seconds.

 

[The Nine Inch Ninja]

Brilliant, cheers.

Tried it before spitting out all that code, but it was really slow. Turns out I just have a really slow computer (at work) and too much data. Broke it down into more managable chunks so the PC can keep up!