ACC2000: Cannot Create a Bit Column That Allows NULL

ID: Q233985

The information in this article applies to:
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access project (.adp).


SYMPTOMS

Although the SQL Server and Microsoft Data Engine (MSDE) bit data type allows NULL values, when you create a table in a Microsoft Access project with the Table Designer, you cannot include a bit column that allows NULL values. If you click Allow Nulls for the bit column in the Table Designer, nothing happens.

In a pre-existing table that was created so that bit columns can accept NULL values, you are also unable to insert new records in which the bit column is set to Null, nor can you edit existing rows to specify a NULL value for the bit column.


RESOLUTION

If you need to create a bit column that allows NULL values in an Access project, use a stored procedure to create the table. When you need to insert NULL values in a table with a bit column that accepts NULL values, create a data access page to do your data entry.

To use a stored procedure to create a table

  1. Open an Access project currently connected to a SQL Server or MSDE database.


  2. In the Database window, click Stored Procedures under Objects, and then click New.


  3. Use the following Transact-SQL to create a stored procedure: 


    4. 
CREATE PROCEDURE MakeTable
AS
CREATE TABLE TestTableBit
(
	PK INT IDENTITY(1,1) PRIMARY KEY,
	MyBit BIT NULL,
	MyChar Char(10)
)
  4. Save the stored procedure with the name that Access suggests.


  5. Run the stored procedure.


  6. In the Database window, click Tables under Objects, and then press F5 to refresh the table list.


  7. Select TestTableBit and open it in Design View. Note that the MyBit column accepts NULL values.


To create a data access page for data entry

  1. Open the same Access project that you used to create the TestTableBit table.


  2. In the Database window, click Pages under Objects.


  3. Click New.


  4. In the New Data Access Page box, click Auto Page: Columnar, click TestTableBit in the Choose the table or query/view where the object's data comes from box, and then click OK.


  5. When the new data access page opens, type the value MyFirstTest in the MyChar field.


  6. In the Record Navigation bar of the data access page, click Save.


  7. Close the data access page without saving it.


  8. In the Database window, click Tables under Objects.


  9. Open TestTableBit and note the record that you just added: the column MyBit contains a NULL value.



STATUS

Microsoft has confirmed this to be a problem in Access 2000.


MORE INFORMATION

Steps to Reproduce Behavior

  1. In Microsoft Access 2000, open an Access project.


  2. In the Database window, click Tables under Objects.


  3. Click New.


  4. In the Table Designer, add a column with the bit data type. Note that Allow Nulls is not selected.


  5. Try to click Allow Nulls to change the value.



REFERENCES

For additional information about issues with the Table Designer, please see the following article in the Microsoft Knowledge Base:

Q201819 FIX: Table Designer Cannot Create a Bit Column Which Allows NULL

Additional query words: prodsql pra 


Keywords          : kbdta kbbug7.00 AccessCS 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbbug

Last Reviewed: June 17, 1999