Recordset not updatable

B

Brandon Campbell

Hello,

I am using Access a client for my SQL Server 2000 databases. I have a form
with a subform. The subforms recordset is based on a join query. I have in
instead of insert trigger on the query. I have included the WITH
VIEW_METADATA option in the VIEW. The query works properly from the SQL
Analyzer when I try to insert a value. The view, however, is not updatable
when I use Access. Is there something else that I need to do in order for the
Access to be able to use the view.

Thank you,

Brandon
 
J

Jeff Boyce

Brandon

As I recall, SQL Server 2000 views are not updateable unless Access knows
which field to use as a primary key back through the view to the underlying
table.

I have no idea what you meant by "I have in instead of insert trigger on the
query."
 
B

Brandon Campbell

Jeff,

When you create a query (View) based on two or tables, the query cannot
update the underlying tables unluss you use an 'Instead of Insert' or an
'Instead of Update' type trigger on the View.

The problem simply lies with one line of code in the VIEW. Because Access
ignores the VIEW and goes straight to the table to get the Metadata
information, you must type in WITH VIEW_METADATA in the options when you
create the VIEW. This forces the metadata from the view to be used and the
update is based on the VIEW, not the tables.

The problem is this, if I remove the WITH VIEW_METADATA, the VIEW will
update the records, but I need to have the trigger running on the VIEW,
therefore the WITH VIEW_METADATA must be in the VIEW. When it is present, the
form becomes read only or more precisely, I get the message, "Recordset is
not Updatable" in the status bar. I would like to use Access to as my client,
but I may have to write this is VB. Could you tell me if there is a solution
for the Access problem.

Thank you,

Brandon Campbell
 
J

John Spencer (MVP)

Also, IF I recall correctly, Access really needs a TimeStamp field in the SQL
table(s) in order to be able to update. I could be wrong on this, but that is
what I seem to remember (at least with Access97 and MS SQL 6.5 and 2000.
 
R

Rick Brandt

A TimeStamp field is always a good idea, but not always required. When it is
required (IME) is if the table includes Text fields (mapped to memo in Access)
and/or any datatypes that might not map perfectly to an Access DataType like
Decimal or Numeric, or any DataTypes where Access will impose a different level
of precision than the server table (like DateTime). In those cases it can be
difficult for Access to query the data in those fields to accurately determine
if they have been changed. This can cause the "another user has changed this
record..." error. When TimeStamp columns are present only the TimeStamp field
is checked to see if the record has been changed and avoids those problems.
 
Top