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.
|