Updatable view problem...

[AlexT]

Folks,

Guess it's a « classic » topic around here... Have a problem with
updatable queries in ADP (not sure if it's an Access or SQL server
question, hence the cross post).

To start with I was under the impression that the conditions for an
updatable query where

The select list can't include a DISTINCT or TOP clause.
The select list can't include an aggregate function.
The select list can't include a calculated value.
The SELECT statement can't include a GROUP BY or HAVING clause.
The view can't include the UNION operator.

I have the following query that works fine (although it contains
calculated values...)

SELECT dbo.MyTable.*, 1 AS ldRecCounting, CASE WHEN
ISNULL(ldMyStuff, 0) <> 0 THEN 1 ELSE 0 END AS ldMatchedCounting
FROM dbo.MyTable

I now would like to join on a second table

SELECT dbo.MyTable.*, 1 AS ldRecCounting, CASE WHEN
ISNULL(ldMyStuff, 0) <> 0 THEN 1 ELSE 0 END AS ldMatchedCounting,
dbo.qActivePOL.POKUNN AS ldNEWField
FROM dbo.MyTable INNER JOIN
dbo.qActivePOL ON dbo.MyTable.ldNoPolIdx =
dbo.qActivePOL.Idx

This new view works fine but is not anymore updatable (note that I do
no want to update ldNEWField, I just need it for display purposes).

Is there a way around this ?

Thanks & regards

-alexT

 

[chakatheapeman]

Have you?

Have you filled in the unique table property for the from, under the data tab on the propery sheet?

 

[--CELKO--]

Try an INSTEAD OF trigger

 

[Steve Jorgensen]

It is supposed to be possible to edit through a join of a multi-table view
just by saying which table you want to edit (I can't recall the property you
set for that just at the moment). That actually seemed to work fin in Access
2000.

In Access 2002, they changed the behavior so it's actually supposed to be
possible to edit all tables regardless, but the effect is usually the other
way around - you can't edit any tables in a multi-table view anymore.

The problem is so bad, and so hard to reliably work around that I've taken to
using things like combo boxes as an alternative to joins in ADPs, so I'm never
trying to edit using a query that joins tables. To get multiple fields from
one "joined" table, you can have a combo box with zero-width columns for the
extra values, and use computed fields to display the values from those
columns.

 

[AlexT]

Try an INSTEAD OF trigger

Care to elaborate in the above context ?

Thanks

alex

 

[Steve Jorgensen]

Try an INSTEAD OF trigger

That makes it worse in an ADP because you have to use WITH VIEW METADATA
option. When you do that, if you don't include all PK fields, ADO says that
there are no key fields, and the ADP won't let you try to edit anything. If
you do include all PK fields, ADO reports them all as part of the PK of the
"row", and the APD becomes mightily confused.

 

[AlexT]

Well well

I guess I will go the "manual" lookup way...

What's the best practice (i.e. what function to use ?)

--alexT

 

[Vadim Rapp]

A> SELECT dbo.MyTable.*, 1 AS ldRecCounting, CASE WHEN
A> ISNULL(ldMyStuff, 0) <> 0 THEN 1 ELSE 0 END AS ldMatchedCounting,
A> dbo.qActivePOL.POKUNN AS ldNEWField
A> FROM dbo.MyTable INNER JOIN
A> dbo.qActivePOL ON dbo.MyTable.ldNoPolIdx =
A> dbo.qActivePOL.Idx

A> This new view works fine but is not anymore updatable (note that I do
A> no want to update ldNEWField, I just need it for display purposes).

is POKUNN the primary key of qActivePOL ?

if not, include the primary key. In the form, specify the primary table.

If it still does not work, please generate and post CREATE TABLE's for the
involved tables.


Vadim Rapp

 

[AlexT]

FWIW qActivePol is a query...

Will try your suggestions

--alexT