[AccessD] re: SQL Server view not updateable in Access?

Nicholson, Karen cyx5 at cdc.gov
Thu Dec 30 05:09:34 CST 2004


To update the table from Access to SQL, you have to make sure that the key fields defined in the SQL table are all included in the view.  Also, check to see if there is a field in the SQL table that is an identity field.  That sometimes throws it.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Christopher Hawkins
Sent: Wednesday, December 29, 2004 6:10 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] re: SQL Server view not updateable in Access?


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-


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list