updateable recordsets in ADP with SQL 2k

[ROB]

I have a form based on a stored proc. which selects
records from two tables and a view all joined together.
The view is a union between two tables. My form is
updateable when I only base the view on one table, not
having the UNION clause in the view. Is there some
restriction against using a UNION in a view, and then
basing an updateable recordset on the results of the view
joined to other tables?

Thanks!

VADIM PLEASE HELP!!!

 

[ROB]

The table being updated isn't in the UNION. It is only
linked to the view, which is a UNION between two tables.

Still need a miracle??

 

[Van T. Dinh]

You find it is the same in Access: as soon as one component of the join(s)
in not updateable, the whole Query / View is not updateable. In this case,
because the Union Query is not updateable, the stored proc. won't be
updateable.

There may be some way to construct your stored proc using WHERE clause
rather than JOIN (to the UNION View) to make the resultant View updateable.

 

[Vadim Rapp]

R> The table being updated isn't in the UNION. It is
R> only linked to the view, which is a UNION between
R> two tables.

R> Still need a miracle??

most likely yes. But you can send me those queries, including the scripts to
create and populate the tables and the view, so I could reproduce the
situation. Put on some web space and send a link (preferred), or remove .nospam
from my email address.

regards

 

[Rob]

Thanks for all of the wonderful advice...but I decided on
a design change, and now all is well. Learning something
new every day, keeping it fun! Thanks again!