ACC2: Default Boolean Value Causes SQL ODBC Error

• Microsoft Access version 2.0

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

When you view a SQL table attached to Microsoft Access, a zero appears in the new record as the default value for a Boolean field. If you try to save the record without making any changes, you receive an error message stating that you cannot save the record because the Boolean field contains a null value (even though a zero, not a null value, is displayed as the Boolean field's value).

If you change the Boolean field by retyping the zero, or changing it to any other value, you can save the record.

This problem can also manifest itself in another way. You can make changes to an attached SQL table containing a Boolean field, save the table without receiving any error messages, and then later open the table to find that the changes were not saved.

CAUSE

In Microsoft Access version 1.x, both the Microsoft Access user interface (UI) and the Microsoft Jet database engine assume a default value of zero for Boolean values. However, in Microsoft Access version 2.0, the Microsoft Jet database engine assumes a default value of null for Boolean values, while the UI still assumes a default value of zero. Thus, when no value is entered in a Boolean field in an attached SQL table, the UI displays a zero in the field, but a null is written by the Microsoft Jet database engine to the attached table. Since the SQL table does not accept null values for Boolean fields, you receive an error message.

If you close the table using Access Basic or a macro, you do not see the ODBC error message, and it appears that the table was saved successfully. However, the data was not saved, due to the ODBC error.

RESOLUTION

To work around this problem, make sure that you either enter a non-zero value in the Boolean field, or retype the zero to force the Microsoft Jet database engine to write a zero to the SQL table rather than the default null value.

To avoid missing possible error messages, choose the Save Record command from the File menu (or do so with Access Basic or a macro) to save records, then close the table.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access version 2.0. This problem no longer occurs in Microsoft Access version 7.0.