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

SYMPTOMS

When using Microsoft ActiveX 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.

Using Microsoft ActiveX Data Objects (ADO) with the Microsoft Jet OLE DB Provider version 3.51 also returns True when checking the nullability of a field, 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.

The functionality was not added to the Microsoft OLE DB Provider for Jet version 3.51.

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.

This functionality is available with the Microsoft Jet OLEDB Provider 4.0 and later that comes with Microsoft Data Access Objects 2.1 and later.

You may obtain Microsoft Data Access Objects 2.1 and later from the following site:

http://msdn.microsoft.com/library/default.asp?url=/downloads/list/dataaccess.asp

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

This is not a problem with MDAC versions 2.1 and later, as they install the Microsoft JET OLEDB Provider 4.0, which has the proper functionality.

This was fixed in Jet 4.0, not supported in Jet 3.51. Jet 4.0 was first available in MDAC 2.1 GA.

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", ""

   'Uncomment the following lines to test with the Jet OLE DB Provider 3.51
   'conn.Open "Provider=Microsoft.Jet.OLEDB.3.51;" & _
   '   "Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb"

   'Uncomment the following lines to test with the Jet OLE DB Provider 4.0
   'conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
   '   "Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb"

   ' 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