query over two databases

[Jopossum]

Hi,

i have some sql servers with two nearly identical databases. one for production use, one as a reference for replication purposes. to find differences i use querys like the following:

SELECT
*
FROM
M2KReference.dbo.ADRESSEN
INNER JOIN Makler2000.dbo.ADRESSEN
ON M2KReference.dbo.ADRESSEN.DcGUID = Makler2000.dbo.ADRESSEN.DcGUID AND
M2KReference.dbo.ADRESSEN.DcChangeDate <> Makler2000.dbo.ADRESSEN.DcChangeDate

in small branch offices we had to implement access databases for our software due to the lack of servers. i want to use the same program for replication, so i tried to port the mssql query to an access jet query but without success. the following query produces FROM-Clause errors.
SELECT
*
FROM
ADRESSEN AS t1 IN 'm2kreference.mdb'
INNER JOIN ADRESSEN AS t2 IN 'm2k.mdb'
ON t1.DcGUID = t2.DcGUID AND
t1.DcChangeDate <> t2.DcChangeDate

Maybe someone could tell me what im doing wrong or can point me to a different approach. i prefer not to copy the whole database into the other to keep disk space and execution time as low as possible.


thx in advance,
Sascha Jopen

 

[Len Boorman]

Not sure that alias's for tables in From clause allowed in Access,

Could be completely wrong though

Len B

 

[Jopossum]

Hmm, aliases are allowed, but you may reference only one external database in a FROM Clause.
But you can workarround this problem by using nestet SELECTs.

I found the following solution:

SELECT
*
FROM
ADRESSEN AS t1
INNER JOIN
(SELECT * FROM ADRESSEN IN m2kreference.mdb') AS t2
ON t1.DcGUID = t2.DcGUID AND t1.DcChangeDate <> t2.DcChangeDate


CU,
Sascha Jopen

 

[Len Boorman]

Learn something new every day