problems with non-editable view (ADP/Access2003)

[Gerald Aichholzer]

Hi NG,

I have a strange problem with a (non) editable view.
There's a table Etat which looks like:

CREATE TABLE [dbo].[Etat] (
[fiEtat] [int] NOT NULL ,
[Year] [smallint] NOT NULL ,
[Cost] [decimal](12, 2) NOT NULL ,
[TS] [timestamp] NULL
) ON [PRIMARY]

Primary key is composed from fiEtat and Year.

Then I have an editable view 'viewEtatCost' with the
following SQL:

SELECT TOP 100 PERCENT fiEtat, Jahr, Betrag
FROM dbo.EtatBetrag
ORDER BY Jahr

And a non-editable view 'viewCustomerEtatCostForm':

SELECT fiEtat, Jahr, Betrag
FROM dbo.viewEtatBetrag


Can anybody tell me why the second view is read-only?

thanx in advance,
Gerald

 

[Sylvain Lafontaine]

Try adding the « TOP 100 PERCENT » to your second view, it may help. There
is a reason behind this but I don't remember it.

Also, mixing composed primary keys and ADP is probably not the best idea of
the century.

S. L.

 

[Sylvain Lafontaine]

There is also a relation between the TOP 100 PERCENT and the use of a ORDER
BY clause in a view but I don't remember it either.

S. L.

Try adding the « TOP 100 PERCENT » to your second view, it may help.
There is a reason behind this but I don't remember it.

Also, mixing composed primary keys and ADP is probably not the best idea
of the century.

S. L.

Hi NG,

I have a strange problem with a (non) editable view.
There's a table Etat which looks like:

CREATE TABLE [dbo].[Etat] (
[fiEtat] [int] NOT NULL ,
[Year] [smallint] NOT NULL ,
[Cost] [decimal](12, 2) NOT NULL ,
[TS] [timestamp] NULL
) ON [PRIMARY]

Primary key is composed from fiEtat and Year.

Then I have an editable view 'viewEtatCost' with the
following SQL:

SELECT TOP 100 PERCENT fiEtat, Jahr, Betrag
FROM dbo.EtatBetrag
ORDER BY Jahr

And a non-editable view 'viewCustomerEtatCostForm':

SELECT fiEtat, Jahr, Betrag
FROM dbo.viewEtatBetrag


Can anybody tell me why the second view is read-only?

thanx in advance,
Gerald

 

[Gerald Aichholzer]

There is also a relation between the TOP 100 PERCENT and the use of a ORDER
BY clause in a view but I don't remember it either.

The relation is that you must use TOP 100 PERCENT if
your query contains ORDER BY.

Gerald

 

[Gerald Aichholzer]

Try adding the « TOP 100 PERCENT » to your second view, it may help. There
is a reason behind this but I don't remember it.

Hi Sylvain,

it didn't work, but I have solved the problem: I just don't
use nested views in this case

thanx,
Gerald