Compare two tables in different databases

[Ecohouse]

I'm using Access 2003 and will have to separate databases with the
exact same tables and table structures.

I need to be able to compare the table from the second database
against the same table in the first database. I need to know if there
is a similar name in the table in the first db and then find out if
all the rest of the fields are the same. If they aren't I need to
update the table in db1 with the values from db2. If there isn't a
record with a similar name then I need to create a new record in db1.

I was just wondering what the best approach to do this would be? Any
help would be appreciated.

[Rich P]

Greetings,

For Updating:

UPDATE tbl2 INNER JOIN tbl1 ON tbl2.ID = tbl1.ID and tbl2.Name =
tbl1.Name SET tbl2.fld1 = tbl1.fld1, tbl2.fld2 = tbl1.fld2,
tbl2.fld3=tbl1.fld3

And for missing rows do this:

Insert Into tbl1 t1
Select * From tbl2 t2 Where Not Exists (select * From tbl2 t3 where
t3.ID = t1.ID and t3.Name = t1.Name)

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Ecohouse]

Thanks for the help on this issue. The problem is that some of these
tables have 50 or more fields. I was wondering if there is a way to
create recordsets and step through them and compare values?

I may be off on this one. I'm not sure how to approach it.

On Jan 3, 3:02*pm, Rich P <[e-mail]:

Greetings,

For Updating:

UPDATE tbl2 INNER JOIN tbl1 ON tbl2.ID = tbl1.ID and tbl2.Name =
tbl1.Name SET tbl2.fld1 = tbl1.fld1, tbl2.fld2 = tbl1.fld2,
tbl2.fld3=tbl1.fld3

And for missing rows do this:

Insert Into tbl1 t1
Select * From tbl2 t2 Where Not Exists (select * From tbl2 t3 where
t3.ID = t1.ID and t3.Name = t1.Name)

Rich

*** Sent via Developersdexhttp://www.developersdex.com***