I have an Access 2007 application which has linked tables to SQL2000.
The form shows a Listbox with a series of records (datasource is a query based on a single table).
When you choose a record from the listbox (on_click event) the form selects the entire record from the SQL database and displays the details on the form in a series of fields that are .Enabled = False. An Edit button enables the fields for editing. One of these fields is a checkbox. A Save button runs some validation on the form data, then refetches the record into a recordset, edits the record and updates. Every time I tried to save the record I got an error message with the code 3197 and text indicating that the update failed because the record was already being edited by another user but this was not possible as I am the only one using the database and I did not have management studio open at the time.
The cause of the error was that one of the fields in the table (which I had just modified and added new columns to) was set to be a Bit field and the existing records had the value of NULL. Access does not have bit fields and converts them to Boolean. In Access an undefined boolean is -1, not NULL and this caused Access to throw the error message - Wrong message - Thanks Microsoft. The solution is to update the existing records to be 0 (False) as a default, and to change your database so there is a default value for the column.
