Import and Display Changed Data (1 Viewer)

[Rat1sully]

Just a quick one I suspect.

I'm currentyl using transfer spreadsheet to import 2 tables, I've just recieved a new data set and my boss now wants to know what's changed where as before he was happy to just overwrite the old data.

I'm thinking I'll need to import the data into a new table run some sort of query to compare the data and display any changes, then oblitorate the old data and change the name of the new table to replace it.

I was just wondering if there was a function or anything that might do this or if the above method was the best/simpleist way.

Thanks in advance

 

[Rat1sully]

ok so I went ahead and coded something anyway but why on earth won't it work i feel like i'm missing something fundementally obvious here which doesn't help.

I keep getting the item not found in collection error but it works perfectly fine if i type the field values in

Option Compare Database
Option Explicit
 
Function CompareTables(tbl1, tbl2, tblout, field1, field2, field3)
Dim db As DAO.Database
Dim tblA, tblB As Recordset
Dim strChange, strSQL As String
Set db = CurrentDb()
Set tblA = db.OpenRecordset(tbl1)
Set tblB = db.OpenRecordset(tbl2)
 
tblA.MoveFirst
While (Not (tblA.EOF))
    tblB.MoveFirst
    strChange = ""
 
    Do While tblA![field1] <> tblB![field1] Or tblA![field2] <> tblB![field2]
        tblB.MoveNext
        If tblB.EOF = True Then
        Exit Do
        End If
    Loop
 
    If tblA![field1] <> tblB![field1] Or tblA![field2] <> tblB![field2] Then
        strChange = tblA![field1] & " " & tblA![field2] & " Has been removed"
    Else
        If tblA![field3] <> tblB![field3] Then
            strChange = "On " & field1 & " " & tblA![field1] & " Quantity fitted of " & tblA![field2] & " has changed from " & tblA![field3] & " to " & tblB![field3]
        End If
    End If
 
    If strChange <> "" Then
        strSQL = "INSERT INTO tblout![Changes] Values '" & strChange & "';"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
    End If
    tblA.MoveNext
Wend
End Function

 

[Rat1sully]

Ok so I've finished being a bit dim and now the code runs fine (final code below if anyone wants it though not commented for the now)

So back to my original question, is there a more efficient way of doing this as this is some what processor intensive due to the number of itterations across 20k records

Option Compare Database
Option Explicit

Function CompareTables(tbl1, tbl2, tblout, field1, field2, field3)
Dim db As DAO.Database
Dim tblA, tblB As Recordset
Dim strChange, strSQL As String
Set db = CurrentDb()
Set tblA = db.OpenRecordset(tbl1)
Set tblB = db.OpenRecordset(tbl2)
tblA.MoveFirst
While Not tblA.EOF
    tblB.MoveFirst
    strChange = ""
    
    Do While tblA.Fields(field1) <> tblB.Fields(field1) Or tblA.Fields(field2) <> tblB.Fields(field2)
        tblB.MoveNext
        If tblB.EOF = True Then
        Exit Do
        End If
    Loop
    
    If tblB.EOF = True Then
        strChange = tblA.Fields(field1) & "'s equipment fit of " & tblA.Fields(field2) & " has been removed"
    Else
        If tblA.Fields(field3) <> tblB.Fields(field3) Then
            strChange = "On " & field1 & " " & tblA.Fields(field1) & " Quantity fitted of " & tblA.Fields(field2) & " has changed from " & tblA.Fields(field3) & " to " & tblB.Fields(field3)
        End If
    End If
    
    If strChange <> "" Then
        strSQL = "INSERT INTO " & tblout & " (Changes) VALUES (" & Chr(34) & strChange & Chr(34) & ");"
        Debug.Print strChange
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
    End If
    'Debug.Print strChange
    tblA.MoveNext
Wend
End Function