Check if value has changed when moving data

问题:

I have built a macro already that moves values inside about 10 different tables from one database to another. It takes a unique identifier so say columns "nid" and checks to see if it already exists in the new database, if there is no match it moves the data if there is a match and it already exists it doesn't.

This macro is working fine however, I would like it to check if the value already exists and if it does to check each column for any changes and if there are changes to the value's to move the new value over. For example password changes on the original database updating on the new database.

Some of the tables have up to 50 columns so manually looping each one is going to be a very long macro, I was wondering if there was an easier way to do this? If not how would I go about looping them?

Here is my macro for one table:

Public Function update1()

    'Open source database
    Dim dSource As Database
    Set dSource = CurrentDb

    'Open dest database
    Dim dDest As Database
    Set dDest = DAO.OpenDatabase("C:\Users\simon\Documents\SellerDeck 2013\Sites\dest\ActinicCatalog.mdb")

    'Open source recordset
    Dim rSource As Recordset
    Set rSource = dSource.OpenRecordset("Address", dbOpenForwardOnly)

    'Open dest recordset
    Dim rDest As Recordset
    Set rDest = dDest.OpenRecordset("Address", dbOpenDynaset)

    'Loop through source recordset
    While Not rSource.EOF

        'Look for record in dest recordset
        rDest.FindFirst "nCustomerID = " & rSource.Fields("nCustomerID") & ""

        'If not found, copy record
        If rDest.NoMatch Then
            rDest.AddNew
            rDest.Fields("nCustomerID") = rSource.Fields("nCustomerID")
            rDest.Fields("sName") = rSource.Fields("sName")
            rDest.Fields("sLine2") = rSource.Fields("sLine2")
            rDest.Fields("sLine4") = rSource.Fields("sLine4")
            rDest.Fields("nCountryID") = rSource.Fields("nCountryID")
            rDest.Fields("bValidInvoiceAddress") = rSource.Fields("bValidInvoiceAddress")
            rDest.Fields("bValidDeliveryAddress") = rSource.Fields("bValidDeliveryAddress")
            rDest.Fields("nStateID") = rSource.Fields("nStateID")
            rDest.Fields("bExemptTax1") = rSource.Fields("bExemptTax1")
            rDest.Fields("sExemptTax1Number") = rSource.Fields("sExemptTax1Number")
            rDest.Fields("bExemptTax2") = rSource.Fields("bExemptTax2")
            rDest.Fields("sExemptTax2Number") = rSource.Fields("sExemptTax2Number")
            rDest.Fields("bPurge") = rSource.Fields("bPurge")
            rDest.Fields("bChanged") = rSource.Fields("bChanged")
            rDest.Fields("nID") = rSource.Fields("nID")
            rDest.Fields("nTax1ID") = rSource.Fields("nTax1ID")
            rDest.Fields("nTax2ID") = rSource.Fields("nTax2ID")
            rDest.Fields("nResidential") = rSource.Fields("nResidential")
            rDest.Fields("sCompanyName") = rSource.Fields("sCompanyName")
            rDest.Fields("sLine1") = rSource.Fields("sLine1")
            rDest.Fields("sLine3") = rSource.Fields("sLine3")
            rDest.Fields("sPostalCode") = rSource.Fields("sPostalCode")
            rDest.Fields("sEmailAddress") = rSource.Fields("sEmailAddress")
            rDest.Fields("sFaxNumber") = rSource.Fields("sFaxNumber")
            rDest.Fields("sFirstName") = rSource.Fields("sFirstName")
            rDest.Fields("sFullName") = rSource.Fields("sFullName")
            rDest.Fields("sLastName") = rSource.Fields("sLastName")
            rDest.Fields("sMobileNumber") = rSource.Fields("sMobileNumber")
            rDest.Fields("sSalutation") = rSource.Fields("sSalutation")
            rDest.Fields("sTelephoneNumber") = rSource.Fields("sTelephoneNumber")
            rDest.Fields("sTitle") = rSource.Fields("sTitle")
            rDest.Update
        End If

        'Next source record
        rSource.MoveNext
    Wend

    'Close dest recordset
    rDest.Close
    Set rDest = Nothing

    'Close source recordset
    rSource.Close
    Set rSource = Nothing

    'Close dest database
    dDest.Close
    Set dDest = Nothing

    'Close source database
    dSource.Close
    Set dSource = Nothing

    End Function

回答1:

You can use the field collection of the recordset for both comparison and copy:

Option Compare Database
Option Explicit

Public Function update1()
    'Temp field
    Dim fField As Field
    Dim bCopy As Boolean

    'Open source database
    Dim dSource As Database
    Set dSource = CurrentDb

    'Open dest database
    Dim dDest As Database
    Set dDest = DAO.OpenDatabase("C:\Users\simon\Documents\SellerDeck 2013\Sites\dest\ActinicCatalog.mdb")

    'Open source recordset
    Dim rSource As Recordset
    Set rSource = dSource.OpenRecordset("Address", dbOpenForwardOnly)

    'Open dest recordset
    Dim rDest As Recordset
    Set rDest = dDest.OpenRecordset("Address", dbOpenDynaset)

    'Loop through source recordset
    While Not rSource.EOF

        'Reset copy flag
        bCopy = False

        'Look for record in dest recordset
        rDest.FindFirst "nCustomerID = " & rSource.Fields("nCustomerID") & ""

        If rDest.NoMatch Then

           'If not found, copy record
            rDest.AddNew
            bCopy = True
        Else

            'If found, check for differences
            For Each fField In rSource.Fields
                If rDest.Fields(fField.Name) <> rSource.Fields(fField.Name) Then
                    rDest.Edit
                    bCopy = True
                    Exit For
                End If
            Next fField
            Set fField = Nothing
        End If

        'If copy flag is set, copy record - ignore errors
        If bCopy Then
            For Each fField In rSource.Fields
                If Not (fField.Attributes And dbAutoIncrField) Then
                    On Error Resume Next
                    rDest.Fields(fField.Name) = rSource.Fields(fField.Name)
                    On Error Goto 0
                End If
            Next fField
            Set fField = Nothing
            rDest.Update
        End If

        'Next source record
        rSource.MoveNext
    Wend

    'Close dest recordset
    rDest.Close
    Set rDest = Nothing

    'Close source recordset
    rSource.Close
    Set rSource = Nothing

    'Close dest database
    dDest.Close
    Set dDest = Nothing

    'Close source database
    dSource.Close
    Set dSource = Nothing

    End Function

回答2:

As I understand, a simple model of your problem is:

given the tables

SELECT * FROM CustA
-------------------
|Id|Nme       |
| 2|A. Only   |
| 6|A. B. Same|
|12|A. B. New |
-------------------

and

SELECT * FROM CustB
---------------------
|Id|Nme       |
| 3|B. Only   |
| 6|A. B. Same|
|12|A. B. Old |
---------------------

my claim is: A customer is either in A (2) or B (3) alone or in both, then the data is either same (6) or different (12); there are no other possibilities.

If you copy (2) to B and update (12) you'll get:

SELECT * FROM CustC
----------------------
|Id|Nme       |
| 2|A. Only   |
| 6|A. B. Same|
|12|A. B. New |
| 3|B. Only   |
----------------------

This can be done without any fancy/error prone/table specific loopings by simple SQL statements:

(1) copy CustA to CustC

SELECT * Into CustC FROM CustA 

(2) get those B only records

INSERT Into CustC SELECT B.* 
FROM CustB B LEFT JOIN CustA A On A.Id = B.Id 
WHERE A.Id Is Null