Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Null default (Access 2007)

    We have a table with several bit fields that do not allow nulls. We have a form for creating and modifying records in the table, and when we create a new record, one of the bit fields causes an error message : "ODBC - call failed ... cannot insert the value NULL into column [Name]... column does not allow nulls. INSERT fails. ...". If we tick and then untick the field referred to, the error goes away and the record is created OK. There is no default set in the field properties on the form. Where could the null default be coming from?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Null default (Access 2007)

    Is this a table in a SQL Server database? If so, it would have been relevant to state that in your post.

    Try setting a default value of 0 for the field in the design of the table.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Null default (Access 2007)

    OK thanks.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Null default (Access 2007)

    In addition to wat Hans has told you, if there a re any records already in the table you will need to go into SQL Server and write an Update query to change the value to 0 if appropriate.
    You cant do this from Access, it wont work from Access.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Null default (Access 2007)

    This is one of the cardinal rules when using bit fields in SQL Server tables that are linked using ODBC. The ODBC driver doesn't know what to do with bit fields that are Null, and will always throw an error if you try to update a table or add a new record. So always set a default value of either 0 or 1 for bit fields in the SQL Server table design. Note that 0 is always False, while -1 is True in Access and 1 is True in SQL Server. The ODBC driver handles the conversion for the True case.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •