Workspaces, pointers, etc.

[user451]

My problem is this: I have been programming in xBase dialects and
clipper for 15 years and keep putting off getting up to speed on OO
stuff. Anyway, my other, real problem is this:

I need to compare two tables in Access, and write any differences to a
third table. I'm so used to just looping through with code (with 3
workspaces and 3 pointers), that I'm at a loss how to get started. If
someone can point me in the right direction, I'm sure I can write the
logic for determining how the tables are different. A quick idea of
what I want:

Table 1 & 2:
Fields: State_ID (char 2)
Tag_num (char 5)
Origin (char 20)
Num_widgets (INT)
Num_pies (INT)
Num_birds (INT)
Num_shoes (INT)
Table 3 would list changes from 1 to 2, over 1 period of time:
For example at 1pm, Table 1 data was input, and a record of:
(NY 1234A Baltimore 24 2 0 5) exists.
At 3pm, Table 2 data is input and a records exists:
(NY 1234A Baltimore 24 2 1 5)

desired output is Table 2 data copied to Table 3.
However, if all the data remains the same, no entry to be made in
table 3. Also, if a State_ID, Tag_num exists in table 2 but not Table
1, then the record in Table two would be copied to Table 3. In other
words, I only want to see the changes in table 3. I can do the code, I
just need a head start of record navigation and the workspace concept
using Access. Thanks in advance for your help. And the reason that I
just don't sit down for a few days and work it out is that it is very
time-sensitive.

 

[Allen Browne]

You could:
- OpenRecordset() on the first table.
- OpenRecordset() on the 2nd table.
- OpenRecordset() on the output table.
- Do While Not rst1.EOF ... rst1.MoveNext ... Loop

However, it is usually better to use a SQL statement in Access rather than
walk the recordset. Is there some way to identify which record in Table1
matches the the record in Table2, e.g. they share the same primary key
value? If so, you can create a query that joins the 2 tables, and use a
WHERE clause so only the records that are different are included. You can
then write this to a 3rd table as an Append query. If there are records in
either table that do not exist in the other, they would also be written with
queries (like that generated by the Unmatched Query wizard). So the whole
job is done by just executing 3 queries.