compare 2 recordsets - update differences

kfschaefer · #1 (**permalink**) 02-10-12, 18:22

Need help with the best approach to how to update identical tables (structure)
with the differences in data. here is what I have so far see '>>>>>>>

Code:

Private Sub cmdValidateGeneralInfo_Click()

On Error GoTo Err_cmdValidateGeneralInfo_Click

Dim stDocName As String
Dim F As DAO.Fields
Dim nName As DAO.Fields
   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.
       curDB.Execute "qryEmpData_TT_General", dbFailOnError
 

   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"
   
   strSQL1 = "SELECT [LastName] & ', ' & Left([FirstName],1) & '.' & IIf
(IsNull([Midname]),'',Left([MidName],1) & '. ') & IIf(IsNull([PrfName]),'','
(' & [PrfName] & ')') AS Name," & _
               " LastName, FirstName, MidName, PrfName," & _
               " CStr([BEMS]) AS BEMSID, 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 tblOrgListing_lkup RIGHT JOIN ((tblEmployee LEFT JOIN
qryUnitChief ON" & _
                   " UnitChief = qryUnitChief.UnitChiefID) LEFT JOIN
tblEmplClass_lkup ON" & _
                   " ClassCtr = tblEmplClass_lkup.ClassCtr) ON
tblOrgListing_lkup.OrgCtr = Mgr_OrgNo" & _
               "WHERE ((tblOrgListing_lkup.UpdateGeneralInfo)=-1) AND (
(Active)=-1))"

   Set gRS = curDB.OpenRecordset(strSQL)
   Set gRS1 = curDB.OpenRecordset(strSQL1)
'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
        While Not gRS1.EOF
           For Each F In gRS1.Fields
               If F.value <> gRS(F.Name) Then
                 gRS.Edit
                 'Set nName = gRS1(F.value)
                 gRS(F.value) = gRS1(F.value)
                 gRS.Update
               End If
           Next F
           gRS.Close
           gRS1.MoveNext
       Wend
       gRS1.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

Sinndho · #2 (**permalink**) 02-11-12, 13:05

In your code, there are references to queries we don't know anything about ("qryEmpData_TT_General", "QS_EMD_tblEmployee vs TT_GeneralInfo"). Moreover, you don't provide any information on the tables involved in the process (name, columns name and data type). The only way we have to retrieve the information is by analyzing 2 complex dynamic SQL strings using several VBA functions (Left, IIF, IsNull, etc.).

You don't provide any information about the form ("frmEmpMain")from which the "cmdValidateGeneralInfo_Click" procedure is supposed to run.

Why did you chose the Recordset method, which probably is the most complex and inefficient method, instead of using SQL? Is there a special reason I did not notice when examining the code you posted?

kfschaefer · #3 (**permalink**) 02-13-12, 11:31

The reason behind my choice for record set is due to the fact that one of the queries is not updatabale.. Note the values of the form and the value of the first query - does not affect the portion of the code I am attempting to fix.

To restate my problem. 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. Feel free to make any suggestions that would allow for a more streamline process.

The issue is with field name and field value, what is the proper syntax for the See:

nName = gRS1.Fields.Name
gRS(F.value) = nName.value

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

Thanks,

Karen

Sinndho · #4 (**permalink**) 02-13-12, 12:41

A Recordset object has a Fields collection.
- To refer to a Field object in the Fields collection of a Recordset (Rs) by its ordinal number, use the following syntax

Code:

Rs.Fields(0)

Each Field object has a Name property.
- To refer to a Field object in the Fields collection of a Recordset (Rs) by its Name property, use the following syntax:

Code:

Rs.Fields("name")
' Or:
Rs.Fields!name

However, because the Fields collection is the default collection of a Recordset object, you may also use:

Code:

Rs.(0)
' Or:
Rs("name")
' Or
Rs!name

See also: Fields Collection *[Access 2007 Developer Reference]

kfschaefer · #5 (**permalink**) 02-15-12, 13:18

still need assistances, I tried the various suggestion w/o success. What is the best approach to compare values between two recordsets and update where needed?

K

Internet Services

Web Development

Digital Marketing

Consumer Tech