Cannot edit data in a Query

  • Thread starter Thread starter Bruce
  • Start date Start date
B

Bruce

I am running Access 2007. I have written a query to a linked table
(AssetMaster) that resides in a SQL Database (SQL Server 2005). I can enter
or change data in a field for one record (it does not matter if I refresh, I
still can only update this one record), but once I move from that record to
another record to either update or enter new data I get the Write Conflict
error message box. The save record option is grayed out and not available as
one of the options. I have tried all of the suggestions listed in the online
help, but to no avail. I need some help.

Bruce
 
I would check the SQL Database table and make sure it has a primary key AND
that it has a timestamp field (also known as a RowID). A field with the data
type of timestamp is not a datetime field but a specific type of field that is
not user changeable

From SQL Books on Line
TimeStamp is a data type that exposes automatically generated, unique binary
numbers within a database. timestamp is generally used as a mechanism for
version-stamping table rows. The storage size is 8 bytes.

Mu understanding (could be wrong)
Access in consjunction with MS SQL Server uses the value to determine if a row
has been changed or not. If Access does not see a timestamp field it cannot
determine if the row it has fetched and is working on has been changed or not
by someone else. So Access won't update the row.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
John,

Thank you for your reply, I will check as you have advised.

I have discovered that if i write the Query in Access 2003 I have no
problems updating or inserting new data in any field. Does this make sense to
you? I would have thought Access 2007 would have been more compliant with SQL
than Access 2003????
 
John,

There is no timestamp field. Is this bad table/database design?? What other
problems might arise because of this condition?
 
John,

I did some further testing: Found that if you write the Query in 2003 and
then run it in 2007 it will work. If you write in 2007 and try to run in 2003
it will not work. It appears that 2007 is doing something 2003 is not and I
cannot determine just what is going on behind the scenes.
 
Don't know. Sorry.

The timestamp field is not necessary for good design. It is often needed if
you are using Access to update fields in an MS SQL database.

Timestamp may not be needed if you are adding records. I don't recall if it
may be needed if you are deleting records.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top