Syncronising Changing External Data 1

[leobaby]

This is a fairly lengthy explanation of my problem, so skip down to the ****'s to see the heart of the problem.

I have created a database that tracks rentals to customers. The only problem is that it is important to get the customer information from another piece of software (our billing software written in clarion). Changes to customer information are made by various people in that software. I have created batch files to export this data to a text file which is importable into access. I need suggestions as to which path to take next. I have done a fair amount of coding in this database and am stuck here. Here are some of the stickies.

1. In the billing software, the 'key' sometimes changes. In the billing software, the key looks like this "Doe,Jane,4331231234A". Yup, its ln,fn,ssn(isurance id). If the name was originally mispelled or if the insurance changes, the key changes and the billing software automatically 're-links' all of its tables. The customer table is the only table in the billing software I take data from.

2. In my database, the customers table key is in a one to many relationship with the deliveries table. Meaning... I cannot simply delete a record and import the correct one. But I do have referential integrity on, so if I manually change the customers key, then my database is happy with that.

3. The billing software is clarion, so odbc is pretty much out of the question. (right?). I cannot find a way to link my customers table to the table of the clarion db. In addition, I am fairly confident that the export proggie I am using to get the data out will be part of the distribution for the life of the program.

4. I do not mind throwing an awful lot of code at this problem.

5. It does not have to be efficient. So the path I am considering taking looks like this. I get the data into my database as a seperate table. I now have tblCustomers and tblCustomersCompare. This part has been completed.

****
I would like to compare each record of table1 to each record of table2 and update the records in table1 to reflect the 'changes' found in table2. I have thought about the compare algorithm I will implement and the criteria for determining wether a record is a new record or a changed record. The tables have the same structure.

A. How do I work with two tables and navigate them? Do I work with recordsets, recordsetclones or combinations?

B. There are over 130 fields in these tables. Can I enumerate the fields for comparison?

Thank You Very Much In Advance!
Matthew (leobaby321@yahoo.com)

 

[RickSpr]

You need to use recordsets. You could use the RecordsetClone property, but what that does is create a recordset object anyway, and it's only available in a form. I don't see any reason you need a form for this--you just want to do it automatically, right?

Since you're not worried about efficiency, you can just open a recordset on each table, copy the fields over, and save the record.

Ok, so you have a main loop that processes the imported table one row at a time. You do your compare logic to find the matching record, if any, in the existing table. Then all you have to worry about is copying the fields--all 130+ of them. It's good that your tables have the same structure, because that makes it easy.

Here's the code, assuming tblCustomersCompare is your imported table:

    Dim db As Database
    dim rst1 As Recordset, rst2 As Recordset

    Set db = CurrentDb()
    Set rst1 = db.OpenRecordset("tblCustomersCompare")
    Set rst2 = db.OpenRecordset("tblCustomers")
    Do While Not rst1.EOF
        ' Insert code here to look up record in rst2
        If Not rst2.NoMatch Then
            ' Copy all the fields
            rst2.Edit
            For i = 0 to rst2.Fields.Count - 1
                rst2.Fields(i) = rst1.Fields(i)
            Next i
            rst2.Update
        End If
        rst1.MoveNext
    Loop
    rst1.Close
    rst2.Close
    Set rst1 = Nothing
    Set rst2 = Nothing
    Set db = Nothing

Rick Sprague