How realizing 'full outer join' from ADP in MDB

[Guest]

My current job is to convert a ADP into a MDB.
Because of storing the queries of ADP in SQL-Server, the conversion of
queries, witch contains 'full outer join' between to tables, to MDB seems not
work.
How can I convert this (in SQL-Server allowed) construct to Access (MDB)?
Access do not know the construct 'full outer join'.

Please help.
Thanks
Erhard

 

[Gary Walter]

My current job is to convert a ADP into a MDB.
Because of storing the queries of ADP in SQL-Server, the conversion of
queries, witch contains 'full outer join' between to tables, to MDB seems
not
work.
How can I convert this (in SQL-Server allowed) construct to Access (MDB)?
Access do not know the construct 'full outer join'.

Hi Erhard,

I would just connect to the view of
the stored query. That way, all work
will be done on server side.

Otherwise (if all data were stored in mdb),
the typical solution is to use a "UNION" query

SELECT *
FROM
A LEFT JOIN B
ON A.ID = B.ID
UNION
SELECT *
FROM
A RIGHT JOIN B
ON A.ID = B.ID

which can be very slow
because UNION has to
weed out records from
second query which already
exist in first query

good luck,

gary

 

[Guest]

Thanks Gary, but this answer cann't satisfy me ;-(( otherwise I have to do it.
So long ......

Erhard

 

[Gary Walter]

I'm sorry Erhard but I just don't understand
what you mean?

Is your data still stored on SQL Server?

-- if you cannot link to a View,
you can use previous sql in
a passthrough query usually

Or is your data now stored in an mdb file?

-- then you will have to use UNION
to create full outer join

-- or create a temp table with correct
fields indexed for no duplicates.
Then append with left join query.
Then append with right join query.
The indexes will not allow dups to be added
to temp table.
This actually can be faster in some situations
than the UNION query.

If the above does not "satisfy,"
please tell where data is now stored,
post the sql from ADP
and MDB sql you have tried that does not work.

thanks,

gary