ACC2: Default Boolean Value Causes SQL ODBC Error

Last reviewed: June 3, 1997
Article ID: Q119709
The information in this article applies to:
  • 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.


Additional query words: attaching sql server
Keywords : kbinterop OdbcOthr OdbcSqlms
Version : 2.0
Platform : WINDOWS
Hardware : X86
Issue type : kbbug
Resolution Type : kbworkaround


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 3, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.