comparing data

[Susie]

We have a database that is downloaded daily with new
registration information. We keep the "old" table daily,
and replace it with a "new" table with the new
registration info. We compare the "old" to the "new" for
any new registrations. What we would like to do is
compare all the records and fields within each record to
see if there have been any changes to specific data. For
instance, people are constantly changing their arrival and
departure dates. There are approx 100 fields in each
record and we can have as many as 500 registrations, so
you can see that it is very tedious to try to find any
changes to a record. Is there a way to compare the two
tables and extract specific fields within a record that
have changed? Thank you in advance for any ideas or
suggestions!

Susie

 

[Pieter Wijnen]

quite simple & quite fun ;-) - As I don't have to provide the error checking
& user feedback


adding new entries I leave to you ...

Sub UpdateChanges(ByVal otherDb as String,Optional Byval TabName AS
String="MYTABLE",Optional ByVal KeyField As String="ID")
' i might forget some declarations as I'm typing this directly in the mail -
good exercise & more fun
' also assume dumb keys (ie autonumber) - not difficult to take into account
(check .type property of the field)
Dim RDb As DAO.Database
Dim LDb AS DAO.Database
Dim RRs AS DAO.RecordSet
Dim LRs AS DAO.Recordset
Dim RFld AS DAO.Field
Dim LFld AS DAO.Field

Set LdB=Access.CurrentDB
Set RdB=Access.OpenDatabase(OtherDb)

Set LRs = LDb.OpenRecordset("SELECT * FROM " & TabName ,
DbOpenDynaset,DbSeeChanges) ' allow for linked table ..
While Not LRs.EOF
Set RRs = RDb.OpenRecordset("SELECT * FROM " & TabName WHERE " & KeyField
&"=" & Lrs.Fields(KeyField),DbopenSnapshot)
If Not RRs.EOF Then ' no match - no do..
Lrs.Edit
For Each Lfld in LRs.Fields
Set RFld = RRs.Fields(LFld.Name)
If Nz(LFld.Value,"") <> Nz(RFld.Value,"") Then
Lfld.Value = Nz(RFld.Value,"NULL")
end if
next
End if
LRs.Update ' U can make this more sophisticated .. I would have made a
temporary query & executed it instead but that's me
RRs.Close : Set RRs = nothing
LRs.Movenext
wend
LRs.Close : Set Lrs = nothing
End sub

HTH

--
Pieter Wijnen

When all else fail try:
http://www.mvps.org/access
http://www.granite.ab.ca
http://allenbrowne.com/