BUG: MS Access ODBC Driver Does Not Expose adFldIsNullable to ADO

ID: Q185823

The information in this article applies to:

SYMPTOMS

When using Microsoft Active Data Objects (ADO) with the Microsoft Access ODBC driver, attempts to programmatically check the nullability of a field with the ADO Field "Attributes" property, returns true even if the field is a required (non-nullable) field.


CAUSE

ADO calls the ODBC API function SQLColAttributes(), with the SQL_COLUMN_NULLABLE flag, to determine if a field is nullable with the Microsoft Access ODBC driver. The Microsoft Access ODBC driver does not properly report the nullability of a column using this API function. The Microsoft Access ODBC driver reports that every field is nullable when using the SQLColAttributes() ODBC API function.


RESOLUTION

The Microsoft Access ODBC driver does not provide a programmatic means of determining the nullability property of a column in a table. You can attempt to add a record and trap for the following error number returned by ADO:

-2147217887 (0x80040E21)
This error returns the following error description:
[Microsoft][ODBC Microsoft Access 97 Driver] The field '<fieldname>' can't contain a Null value because the Required property for this field is set to True.
You can also use Microsoft Data Access Objects (DAO) to open a table and this properly returns the nullability of a field using the "Required" property of the Field object.


STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.


MORE INFORMATION

The following VBA code sample demonstrates the issue: 


   Sub TestRequiredStatus()
   Dim conn As New ADODB.Connection
   Dim rs As New ADODB.recordset
   Dim f As ADODB.Field

   ' Open a Microsoft Access database using NW97 ODBC data source.
   conn.Open "NW97", "admin", ""
   ' Create a table with a nullable and non-nullable field.
   conn.Execute "CREATE TABLE AdoNullableTest " & _
                "(NonNullableField TEXT NOT NULL, NullableField TEXT)"

   ' Open a recordset on the new table.
   rs.Open "AdoNullableTest", conn, adOpenStatic, _
         adLockReadOnly, adCmdTable

   ' Print the status of the adFldIsNullable flag for each field.
   For Each f In rs.Fields

        Debug.Print f.Name & " is Nullable? " &  _
           IIf(f.Attributes And adFldIsNullable, "YES", "NO")

   Next f
   rs.Close
   conn.Close

   End Sub

Additional query words: 


Keywords          : kbAccess kbADO kbDriver kbJET kbODBC kbDSupport 
Version           : WINDOWS:1.0,1.5,2.0
Platform          : WINDOWS 
Issue type        : kbbug

Last Reviewed: July 13, 1999