Comparing, Archiving, Updating. 1

[GoatieEddie]

Hi guys,

I have been struggling with this and now giving in to the masters!

I have a table in a database containing customer details that imported from our central system. Occassionally (and if I get this to work properly it could end up daily), I re-import this to ensure it is up to date. However, to ensure I don't lose customers that may have left, I have been importing with the entire table into the existing table with the customer number as a primary key so that old ones are kept, existing ones are kept unchanged and new ones are added.

Great. But sometimes the data in one of the fields (lets say contactname) changes even though the customer remains otherwise the same. What I want to do, is move the existing field in the table to an archive table (with the new and old contactname and new field with date of change) and then update the existing table with the new value.

And I have kind of lost my way! I started looking at recordset but sank quickly!

Any help would be greatly appreciated.

Many thanks as always,

GE

 

[CautionMP]

See if this gets you moving in the right direction. You will need to change the [tt]SELECT * ... FROM ...;[/tt] for all three recordsets to match your real data. Also it has been a while since I've done this so you may need to check the usage of this line:
[tt]rstDestination.Find "[KeyFIeld]=" & rstSource.Fields("KeyField")[/tt]

Public Sub UpdateFields()
Dim conCurrent As ADODB.Connection
Dim rstSource As ADODB.Recordset
Dim rstDestination As ADODB.Recordset
Dim rstUpdates As ADODB.Recordset
Dim fldSource As Field

Set conCurrent = CurrentProject.Connection

'The source recordset
rstSource.Open "SELECT * FROM CentralSystem;", conCurrent, adOpenForwardOnly
'The local destination recordset
rstDestination.Open "SELECT * FROM LocalDestination;", conCurrent, adOpenDynamic, adLockOptimistic
'This is the container recordset to hold the updates
rstUpdates.Open "SELECT * FROM LocalUpdtes;", conCurrent, adOpenDynamic, adLockOptimistic

'Cycle through the records in the source table
Do While Not rstSource.EOF
  'Locate the record in the destination table that has the same key & _
  as the source record
  [b]rstDestination.Find "[KeyFIeld]=" & rstSource.Fields("KeyField")[/b]
  'Cycle through each field in the source recordset
  For Each fldSource In rstSource.Fields
    'Compare the value in the source recordset field with the value in & _
    the destination recordset field
    If fldSource.Value <> rstDestination.Fields(fldSource.Name) Then
      'The field values did not match, create a record in the update table...
      With rstUpdates
        .AddNew
        .Fields(fldSource.Name) = rstDestination.Fields(fldSource.Name).Value
        'add you date/time updated field here
        .Update
      End With
      '...then update the value in the destination table
      rstDestination.Fields(fldSource.Name).Value = fldSource.Value
    End If
  Next fldSource
  'Move to the next record in the source and start the process all over & _
  again
  rstSource.MoveNext
Loop
'Done, clean up
rstUpdates.Close
rstDestination.Close
rstSource.Close
conCurrent.Close
Set rstUpdates = Nothing
Set rstDestination = Nothing
Set rstSource = Nothing
Set conCurrent = Nothing
End Sub

Hope this gets you moving in the right direction.
CMP

Instant programmer, just add coffee.

 

[GoatieEddie]

Many thanks! I had to use .FindFirst but otherwise it was all very helpful!!