ACC: Operation Stops When Editing Attached SQL Tables

ID: Q96897

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

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

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 <Table Name>

To remove an index, use the following command:

DROP INDEX <Table_Name.Index_Name>

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 <Table Name> 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 kbusage OdbcOthr 
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: March 20, 1999