Read-only view

[brainy]

I created a view on a DB2 table (Select * from TableName) to allow me to open it via ODBC in Access. I cannot access the table directly from Access because there are too many indexes and over 256 fields.

Even with GRANT ALL, the view remains read-only.

Ideas on why a view would be read-only? Is it because I cannot include all fields (I believe that Access can recognize only 256)?

 

[ddiamond]

Does TableName have a primary key? I know access requires a primary key in order for a view to be updateable.

 

[gregsimpson]

Could it be that although you've given ultimte permissions and authorities on the view, your user is only read only on the base table and that's overriding it?

Greg

 

[sathyarams]

If you have 'write' privilege on the view, the privileges on the base table will not override that of the view, AFAIK .

What does
select readonly from syscat.views where viewname='yourviewname' and viewschema=yourviewschema'
tell you ??

If the value is 'Y', then post your full view definition .. There may be something there which makes it read only .

If the value is 'N', then I would go with ddiamond opinion.

You should be defining the primary key in your access also, i think

Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[brainy]

Thanks for the responses. I will answer all three here.

1. The table has a primary key, and it is included in the view.
2. Select readonly from Syscat... returns "N"
3. I cannot define the primary key in Access because it is an ODBC-linked table, and I cannot change the structure.

I tried the same thing on a smaller table (the original one had 366 fields) with the same results. Here are the SQL statements:

CREATE VIEW TRAILER_TEST AS SELECT * FROM TRAILER
GRANT ALL ON TRAILER_TEST TO TMADMINS

Results:
1. TRAILER_TEST remains read-only in Access, although I am a member of the TMADMINS group.
2. An ODBC link directly to the TRAILER table is updateable, but the view is not.
3. The view is updateable directly through SQL, but not via Access/ODBC.

 

[ddiamond]

Brainy,

When you linked the odbc view, you should have received a dialog box asking you to select which fields make the view unique. Did this not occur? If you want to try it again, you will have to drop the linked view from access, and then relink it. Hopefully when you relink it, you will be prompted to select a unique key.

- Dan

 

[brainy]

Bingo!. That was it. I had created the ODBC connection previously & was trying to set the primary key from within Access. Of course, it denies access to changing table structure, so I removed the link & re-created it with the PK as the unique field.

Thank you so much.