Microsoft KB Archive/96897

= ACC: Operation Stops When Editing Attached SQL Tables =

Article ID: 96897

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q96897



Moderate: Requires basic macro, coding, and interoperability skills.



SYMPTOMS
Microsoft Access may return an inconsistent value for a floating point field in an SQL table when it checks the value of the field before editing and before updating. This can result in the error message

Data Has changed; Operation Stopped

when you are trying to update a record in a linked (attached) SQL table.



CAUSE
The behavior occurs if an SQL field data type is set to Float and the table does not contain a TimeStamp field, or when an SQL indexed field data type is set to either Date/Time or Float.



RESOLUTION
 Remove the index from any fields with the Date/Time or Float data types.

To obtain information on any index within a SQL table, you can run the following system stored procedure within the System Administrator Facility (SAF):

sp_helpindex 

To remove an index, use the following command:

DROP INDEX 

Note that you must be logged into the SQL Server as either the table owner or the System Administrator. If other fields in the table have the Float data type, you must insert a TimeStamp field in the table. If a field with this data type is present, Microsoft Access returns a consistent value for fields of this data type and will use the value in this field to verify whether the record has been modified.

This can be accomplished by performing the following command within SAF:

ALTER TABLE  ADD TimeStamp timestamp

Note that you must be logged in as either the database owner or the System Administrator to run this command.

NOTE: If the Timestamp field is added to an existing table with existing data it will have a null value for the existing data and therefore the problem will still occur on operations to that existing data. New data added with a Timestamp being created will be fine. It is better to create the table from scratch and copy the data over.

Keywords: kberrmsg kbprb kbusage KB96897

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.