Compare two tables in different databases

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.

Re: Compare two tables in different databases

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 ***

Re: Compare two tables in different databases

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=A0pm, Rich P wrote:
> Greetings,
>
> For Updating:
>
> UPDATE tbl2 INNER JOIN tbl1 ON tbl2.ID =3D tbl1.ID and tbl2.Name =3D
> tbl1.Name SET tbl2.fld1 =3D tbl1.fld1, tbl2.fld2 =3D tbl1.fld2,
> tbl2.fld3=3Dtbl1.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 =3D t1.ID and t3.Name =3D t1.Name)
>
> Rich
>
> *** Sent via Developersdexhttp://www.developersdex.com***