Updating from Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

With reference to the previous post ...

The other way that Access can be used as a front-end for a Client/Server
solution is to use an Access Project. Here again, we connect to a SQL Server
database which contains Views that refer to remote tables in the Linked
Server. However, when we create Access forms referring to those Views the
data cannot be changed. Access says that the Recordset is not updateable. The
documentation says that these Views should be updateable if there are unique
indexes against those Views. Our database tables have unique indexes. Looking
at the SQL Profiler trace again, it appears Access is trying to determine the
same by executing the stored procedures 'sp_primary_keys_rowset' and
'sp_indexes_rowset'. But it is executing the wrong stored procedure against
the wrong table/view. A quick examination of the 'sp_indexes_rowset' stored
procedure reveals that there are several versions of this procedure and the
correct one to exceute for a Linked Server is 'sp_indexes_rowset;5'. Has
anyone encountered this problem before? Is there a way to force Access to
execute the correct stored procedure?
 
Supi

SQL Server Views and Tables are two different objects. Unless Access is
told, when linking to a View, which field(s) to use as a primary unique
index, Access won't find the recordset updateable. Moreover, if the View
does not include the table's unique index, the view isn't updateable.
 
Thanks for the reply.

How do we tell Access which field to use as the primary unique index when
linking a View. It doesn't give me such an option. I am using an .ADP Access
project and it directly connects to the SQL Server database and shows all the
tables and views that exist in the SQL Server database. How do I make the
View include the table's unique index to make it updateable.

Thanks
Supi
 
Supi

Sorry for the delay, I've been out of town. Consider reposting your
question, given this new information. I don't work in this arena and can't
help.

Jeff Boyce
<Access MVP>
 
Back
Top