Error message when you try to delete a SQL Linked table record in Access

https://support.microsoft.com/en-us/kb/318882

CAUSE
This problem occurs if fields with a bit data type in the SQL Server-based database have been left blank. Microsoft Access interprets blank fields as fields that contain Null values, and the Jet database engine does not release them. As a result, the records remain locked and are not available for deletion.
RESOLUTION
To resolve this problem, do not allow Null values, or establish a default value on the fields with a bit data type. To do so, follow these steps:
  1. NOTE: To perform this procedure, you must have the appropriate permissions to modify database objects (db_ddladmin or db_owner).

  2. Start SQL Server Enterprise Manager, and then locate the server where the database is located.
  3. Expand the Databases folder, double-click the database name, and then click Tables.
  4. In the right pane of SQL Server Manager, right-click the table where the field with a bit data type is located, and then click Design Table.
  5. Under Default Value, type either 0 (zero) or 1.

    NOTE: To disallow Null values, clear the appropriate Allow Nulls check box.

Add comment

Loading