Christopher Hawkins
clh at christopherhawkins.com
Wed Dec 29 17:10:24 CST 2004
P.S. I checked permissions too. The current login has full CRUD rights to the whole database (which is another problem entirely). -Christopher- ---------------------------------------- From: "Christopher Hawkins" <clh at christopherhawkins.com> Sent: Wednesday, December 29, 2004 4:04 PM To: accessd at databaseadvisors.com Subject: SQL Server view not updateable in Access? A client of mine called me to look at a view in his SQL Server instance. It is linked in to his Access front-end like a table. It is also not updateable (he wants to use the view instead of an Access query). OK, I think to myself. Do all the tables in the view have keys? Check. Does the view have a 'TOP x PERCENT' clause in it? Nope. Hmmm. Then another idea hits me - the view tables may have primary keys, but the view itself does not have a key or an index of any kind. That may be why Access can't update it. But how would I indicate an index or key column on a view? I know that if I manually link tables using a DSN, Access will prompt for tables or view when it cannot identify a unique index, but *this* application links to the SQL Server programmatically by creating a TableDef object, setting the .Connect property to something like "ODBC;DRIVER=SQL Server;SERVER=ServerName;DATABASE=DatabaseName;TABLE=dbo.TableName", then appending the new TableDef to the TableDefs. Is there some parameter I can throw in the connect string to indicate the unique index of the view? I'm pretty stumped, and my searches of Books Online and Google have left me with nothing. -Christopher-