[AccessD] SQL Server view not updateable in Access?

Christopher Hawkins clh at christopherhawkins.com
Wed Dec 29 17:04:44 CST 2004


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-

 



More information about the AccessD mailing list