Compare data between two recordsets - only update fields where different (1 Viewer)

[kfschaefer]

I need to compare two tables, data and determine differences and where different - update the first table with the new data. I am looking for the best approach - a simple update query will not sufficient due to the fact the second record set is not an updatable query, hence the need to use the recordset method.

The issue is with field name and field value, what is the proper syntax for the See:
Problem is with object not found, even though the hover over the rs1(f.value) does display a value. Not sure where I am going wrong:

rs(F.value) = rs1(F.value)

'------------------------------------------------------------------------------------------------

Private Sub cmdValidateGeneralInfo_Click()

On Error GoTo Err_cmdValidateGeneralInfo_Click

Dim stDocName As String
Dim F As DAO.Field
Dim nName As DAO.Fields
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset

Set curDB = CurrentDb()
    If Me.DateModified = Date Then
        'Adds new employees to the TT_GeneralInfo table in the FTEI_PhoneBook.mdb - which is used thru out the AP databases.
        DoCmd.OpenQuery "qryEmpData_TT_General"

    strSQL = "SELECT Name, LastName, FirstName, MidName, PrfName, BEMS, NT_UserId," & _
                " StableEmail, WrkPhNo, WrkPhNo2, PagerPh, Org, MS, Bldg, Cub, AltBldg, AltCub, RevDt, EmplClass" & _
                " FROM TT_GeneralInfo"
    Set rs = curDB.OpenRecordset(strSQL)
    
    strSQL1 = "SELECT [LastName] & ', ' & Left([FirstName],1) & '.' & IIf(IsNull([Midname]),'',Left([MidName],1) & '. ') & IIf(IsNull([PrfName]),'','(' & [PrfName] & ')') AS Name," & _
                " LastName, FirstName, MidName, PrfName," & _
                " BEMS, NT_UserId, StableEmail, WrkPhNo, WrkPhNo2, PagerPh, tblOrgListing_lkup.Org, MailStop as MS, Bldg_Primary as Bldg," & _
                " Col_Cub_Primary as Cub, Bldg_Alt as AltBldg, Col_Cub_Alt as AltCub, Date() AS RevDt, tblEmplClass_lkup.EmplClass" & _
            " FROM (tblEmployee LEFT JOIN tblEmplClass_lkup ON tblEmployee.ClassCtr = tblEmplClass_lkup.ClassCtr) LEFT JOIN tblOrgListing_lkup ON tblEmployee.Mgr_OrgNo = tblOrgListing_lkup.OrgCtr" & _
            " WHERE (((tblOrgListing_lkup.UpdateGeneralInfo)=-1) AND ((tblEmployee.Active)=-1))"
    Set rs1 = curDB.OpenRecordset(strSQL1)

         While Not rs1.EOF
            For Each F In rs1.Fields
                If F.value <> rs(F.Name) Then
                  rs.Edit
                  'Set nName = rs1(F.value)
                  rs(F.value) = rs1(F.value)
                  rs.Update
                End If
            Next F
            rs.Close
            rs1.MoveNext
        Wend
        rs1.Close

    End If
        
    stDocName = "QS_EMD_tblEmployee vs TT_GeneralInfo"
    DoCmd.OpenQuery stDocName, acNormal, acReadOnly

Exit_cmdValidateGeneralInfo_Click:
    Exit Sub

Err_cmdValidateGeneralInfo_Click:
    MsgBox Err.Description
    Resume Exit_cmdValidateGeneralInfo_Click

   On Error GoTo 0
   Exit Sub

cmdValidateGeneralInfo_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdValidateGeneralInfo_Click of VBA Document Form_frmEmpMain"
    
End Sub

Thanks,

Karen

 

[mdlueck]

The issue is with field name and field value, what is the proper syntax for the See:
Problem is with object not found, even though the hover over the rs1(f.value) does display a value. Not sure where I am going wrong:

rs(F.value) = rs1(F.value)

I am more familiar with this syntax:

MyRS.Fields("ThisField")

I would also suggest adding a "dirfyflg" variable and only issue the update if a field actually update for the record. So update based on the record, not the individual field.

Air Code...

...snip...
Dim strThisField  as String
Dim datahaschangedflg As Boolean
...snip...

         While Not rs1.EOF
            datahaschangedflg = False
            For Each F In rs1.Fields
                strThisField = F.Name
                If F.value <> rs(strThisField) Then
                  If datahaschangedflg  = False Then
                    datahaschangedflg  = True
                    rs.Edit
                  End If
                  rs(strThisField) = rs1(strThisField)
                End If
            Next F
            If datahaschangedflg = True Then
                rs.Update
               datahaschagned = False
            End If
            rs.Close
            rs1.MoveNext
        Wend
        rs1.Close

...snip...

 

[kfschaefer]

Thanks for the input, however, there seems to be an issue with

If F.value <> rs(strThisField) Then
If datahaschangedflg = False Then

if F.value = "Jim Jones" and strThisField= "Name" then it errors. seems that the strthisfield is returning the name of the field and not the value of the field.

whereas F.value is returning the value of the field in question. Any Ideas?

K

 

[mdlueck]

there seems to be an issue with

If F.value <> rs(strThisField) Then

Make that along this syntax...

MyRS.Fields("ThisField")
so add .Fields and see if that fixes it...

 

[kfschaefer]

Still looking for assistance with the issue. Still getting invalid object not found, even with the adding the ,fields.

K

 

[vbaInet]

Is there any particular reason you don't want to do it in an UPDATE query?

 

[boblarson]

I'm wondering why you can't use an update query either. But I did want to point out an error in your use of the recordsets.

You have no sort order applied in either of the queries that make up the recordsets. That is an error. Access essentially does not store data in any particular order and what you pull one time will not necessarily match at another time.

 

[JANR]

Problem is with object not found, even though the hover over the rs1(f.value) does display a value. Not sure where I am going wrong:

rs(F.value) = rs1(F.value)

The error message gives it away don't it? You say that rs1(F.Value) has a value, but did you hower over rs(F.Value)?? I beleive that says Nothing. You close the rs record set after the first run of your outer loop of rs1.

...snip
 While Not rs1.EOF
            For Each F In rs1.Fields
                If F.value <> rs(F.Name) Then
                  rs.Edit
                  'Set nName = rs1(F.value)
                  rs(F.value) = rs1(F.value)
                  rs.Update
                End If
            Next F
            rs.Close  [COLOR=red]<----- remove this code line
[/COLOR]            rs1.MoveNext
        Wend
        rs1.Close
..snip

So you code runs for the first record of rs1 but not the rest of the records.

JR

 

[tehNellie]

Is there any particular reason you don't want to do it in an UPDATE query?

Thirded.

"I want to compare two sets of Data and update the records where there are differences" Would seem a pretty good description of an Update Query.

 

[kfschaefer]

I was not having any success with an Update query. I need to replace only that data where it is different, since the BEMS has a counterpart in both tables how do i get it look at each field.

Here is my latest attempt at code, as you can see by the commented out code I have tried various approaches without success. Feel free to revise the code with either using recordsets to update or a Update query.

Private Sub cmdValidateGeneralInfo_Click()
On Error GoTo Err_cmdValidateGeneralInfo_Click
Dim stDocName As String
Dim F As DAO.Field
'Dim nName As DAO.Fields
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
'Dim qdf As DAO.QueryDef
Dim strThisField  As String
Dim datahaschangedflg As Boolean
Set curDB = CurrentDb()
curDB.Execute ("Delete * from tbl_temp")
 
    If Me.DateModified = Date Then
        'Adds new employees to the TT_GeneralInfo table in the FTEI_PhoneBook.mdb - which is used thru out the AP databases.
        DoCmd.OpenQuery "qryEmpData_TT_General"
    strSQL = "SELECT Name, LastName, FirstName, MidName, PrfName, BEMS, NT_UserId," & _
                " StableEmail, WrkPhNo, WrkPhNo2, PagerPh, Org, MS, Bldg, Cub, AltBldg, AltCub, RevDt, EmplClass" & _
                " FROM TT_GeneralInfo"
    Set rs = curDB.OpenRecordset(strSQL)
 
    'strSQL1 = "INSERT INTO tbl_Temp ( Name, LastName, FirstName, MidName, PrfName, BEMS, NT_UserId, StableEmail, WrkPhNo, WrkPhNo2, PagerPh, Org, MS, Bldg, Cub, AltBldg, AltCub, RevDt, EmplClass )" & _
    strSQL1 = "SELECT [LastName] & ', ' & Left([FirstName],1) & '.' & IIf(IsNull([Midname]),'',Left([MidName],1) & '. ') & IIf(IsNull([PrfName]),'','(' & [PrfName] & ')') AS Name," & _
                " LastName, FirstName, MidName, PrfName," & _
                " BEMS, NT_UserId, StableEmail, WrkPhNo, WrkPhNo2, PagerPh, tblOrgListing_lkup.Org, MailStop as MS, Bldg_Primary as Bldg," & _
                " Col_Cub_Primary as Cub, Bldg_Alt as AltBldg, Col_Cub_Alt as AltCub, Date() AS RevDt, tblEmplClass_lkup.EmplClass" & _
            " FROM (tblEmployee LEFT JOIN tblEmplClass_lkup ON tblEmployee.ClassCtr = tblEmplClass_lkup.ClassCtr) LEFT JOIN tblOrgListing_lkup ON tblEmployee.Mgr_OrgNo = tblOrgListing_lkup.OrgCtr" & _
            " WHERE (((tblOrgListing_lkup.UpdateGeneralInfo)=-1) AND ((tblEmployee.Active)=-1))"
    Set rs1 = curDB.OpenRecordset(strSQL1)
'curDB.Execute (strSQL1)
 
'strSQL = "Update TT_GeneralInfo AS T1 INNER JOIN  tbl_temp AS T2 ON T1.BEMS = T2.BEMSID" & _
'                " SET " & _
'                " IIF(T1.Name <> T2.Name)T2.name, T1.Name)" & _
'                " T1.LastName =T2.LastName, " & _
'                " t1.FirstName=T2.FirstName, " & _
'                " T1.MidName=T2.MidName, " & _
'                " T1.PrfName=T2.PrfName, " & _
'                " T1.NT_UserId=T2.NT_UserId, " & _
'                " t1.StableEmail=T2.StableEmail, " & _
'                " t1.WrkPhNo=T2.WrkPhNo, " & _
'                " T1.WrkPhNo2=T2.WrkPhNo2, " & _
'                " T1.PagerPh=T2.PagerPh, " & _
'                " T1.Org=T2.Org, " & _
'                " T1.MS=T2.MS, " & _
'                " T1.Bldg=T2.Bldg, " & _
'                " T1.Cub=T2.Cub, " & _
'                " T1.AltBldg=T2.AltBldg, " & _
'                " T1.AltCub=T2.AltCub, " & _
'                " T1.RevDt=T2.RevDt, " & _
'                " T1.EmplClass=T2.EmplClass"
'     Debug.Print strSQL
'curDB.Execute (strSQL)
         While Not rs1.EOF
            datahaschangedflg = False
            For Each F In rs1.Fields
                strThisField = F.Name
                If F.value <> rs.Fields(strThisField).value Then
                  If datahaschangedflg = False Then
                    datahaschangedflg = True
                    rs.Edit
                  End If
                  rs(strThisField) = rs1.Fields(strThisField).value
                End If
            Next F
            If datahaschangedflg = True Then
                rs.Update
               datahaschangedflg = False
            End If
            rs.Close
            rs1.MoveNext
        Wend
        rs1.Close
 
'         While Not rs1.EOF
'            For Each F In rs1.Fields
'                If F.value <> rs(F.Name) Then
'                  rs.Edit
'                  'Set nName = rs1(F.value)
'                  rs(F.value) = rs1(F.value)
'                  rs.Update
'                End If
'            Next F
'            rs.Close
'            rs1.MoveNext
'        Wend
'        rs1.Close
'
    End If
'
'    stDocName = "QS_EMD_tblEmployee vs TT_GeneralInfo"
'    DoCmd.OpenQuery stDocName, acNormal, acReadOnly
Exit_cmdValidateGeneralInfo_Click:
    Exit Sub
Err_cmdValidateGeneralInfo_Click:
    MsgBox Err.Description
    Resume Exit_cmdValidateGeneralInfo_Click
   On Error GoTo 0
   Exit Sub
cmdValidateGeneralInfo_Click_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdValidateGeneralInfo_Click of VBA Document Form_frmEmpMain"
 
End Sub

 

[tehNellie]

Right now, your code suggests that the second query can effectively be considered your master data. i.e. if any of the fields are different then the value in the second query overwrites the value in tt_generalinfo.

On that basis, why bother to check if anything is different? Just update the entire record in tt_generalinfo with the data from the second query.

Does tt_generalinfo have any related records? i.e. is there any reason why you couldn't just drop the entire contents of tt_generalinfo and repopulate it from your second query? Then you know the data is both up to date and you have all the contents from the second query in tt_generalinfo.

 

[kfschaefer]

Thanks for your quick response.

However, you got it backward - tblEmployee is the good/revised data and primary data, I need to update TT_GeneralInfo with any changes in TblEmployee.

There is a Primary key value in TT_GeneralInfo that is used in multiple databases - so I can not just replace all the data (ie. refresh the table), hence I need to compare each value to change only those values that are different.

K

 

[kfschaefer]

Ok, I got most of the recordset update working except for the field type issue, ie. Dates.

         While Not rs1.EOF
            For Each F In rs1.Fields
                If F.value <> rs(F.Name) Then
                    rs.Edit
                        If IsDate(F.value) Then
                            rs(F.value) = CDate(F.value)
                        Else
                            rs(F.value) = F.value
                        End If
                    rs.Update
                End If
            Next F
            rs1.MoveNext
        Wend
        rs1.Close
    End If
'

How Do I check for dates and pass that value correctly?

K

 

[kfschaefer]

Here is my final solution. Thanks for your input.

Private Sub cmdValidateGeneralInfo_Click()
On Error GoTo Err_cmdValidateGeneralInfo_Click
Dim F As DAO.Field
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Set curDB = CurrentDb()
    If Me.DateModified = Date Then
        'Adds new employees to the TT_GeneralInfo table in the FTEI_PhoneBook.mdb - which is used thru out the AP databases.
     '   DoCmd.OpenQuery "qryEmpData_TT_General"
    strSQL = "SELECT Name, LastName, FirstName, MidName, PrfName, BEMS, NT_UserId," & _
                " StableEmail, WrkPhNo, WrkPhNo2, PagerPh, Org, MS, Bldg, Cub, AltBldg, AltCub, RevDt, EmplClass" & _
                " FROM TT_GeneralInfo" & _
                " ORDER BY BEMS"
    Set rs = curDB.OpenRecordset(strSQL)
    
    strSQL1 = "SELECT [LastName] & ', ' & Left([FirstName],1) & '.' & IIf(IsNull([Midname]),'',Left([MidName],1) & '. ') & IIf(IsNull([PrfName]),'','(' & [PrfName] & ')') AS Name," & _
                " LastName, FirstName, MidName, PrfName," & _
                " BEMS, NT_UserId, StableEmail, WrkPhNo, WrkPhNo2, PagerPh, tblOrgListing_lkup.Org, MailStop as MS, Bldg_Primary as Bldg," & _
                " Col_Cub_Primary as Cub, Bldg_Alt as AltBldg, Col_Cub_Alt as AltCub, Date() AS RevDt, tblEmplClass_lkup.EmplClass" & _
            " FROM (tblEmployee LEFT JOIN tblEmplClass_lkup ON tblEmployee.ClassCtr = tblEmplClass_lkup.ClassCtr) LEFT JOIN tblOrgListing_lkup ON tblEmployee.Mgr_OrgNo = tblOrgListing_lkup.OrgCtr" & _
            " WHERE (((tblOrgListing_lkup.UpdateGeneralInfo)=-1) AND ((tblEmployee.Active)=-1))" & _
            " ORDER BY BEMS"
    Set rs1 = curDB.OpenRecordset(strSQL1)
        Do Until rs.EOF
          For Each F In rs.Fields
            If rs.Fields(F.Name) <> rs1.Fields(F.Name) Then
              rs.Edit
              rs.Fields(F.Name) = rs1.Fields(F.Name)
              rs.Update
            End If
          Next F
          rs.MoveNext
        Loop
    End If
'
'    stDocName = "QS_EMD_tblEmployee vs TT_GeneralInfo"
'    DoCmd.OpenQuery stDocName, acNormal, acReadOnly
Exit_cmdValidateGeneralInfo_Click:
    Exit Sub
Err_cmdValidateGeneralInfo_Click:
    MsgBox Err.Description
    Resume Exit_cmdValidateGeneralInfo_Click
   On Error GoTo 0
   Exit Sub
cmdValidateGeneralInfo_Click_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdValidateGeneralInfo_Click of VBA Document Form_frmEmpMain"
    
End Sub

 

[mdlueck]

my final solution

rrr??? ALWAYS one more tweak!

 

[kfschaefer]

Turns out I was mistake the code runs, however, the current code returns the same employee info for all the employees, replacing every record with the same - what am I missing?

K

 

[vbaInet]

You don't break out of your loop when you set the boolean flag.

So, going back to our previous questions, is there a reason why you're not doing this in an UPDATE query?

 

[tehNellie]

Perhaps it boils down to having spent so long writing a piece of code to achieve this that abandoning it in favour of spending 5 minutes creating an update query is unthinkable?

I've re-invented the wheel a few times for sure, admitting that's what you're doing and abandoning it though is quite hard.