BUG: MS Access ODBC Driver, Jet OLE DB 3.51 Do Not Expose adFldIsNullable to ADO

ID: Q185823

The information in this article applies to:

ActiveX Data Objects (ADO), versions 1.0, 1.5, 2.0
Microsoft ODBC Driver for Access, versions 3.5, 4.0
Microsoft OLE DB Provider for Jet, version 3.51

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://www.microsoft.com/data/download.htm

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

   '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

Additional query words:

Keywords : kbAccess kbADO kbADO200bug kbDatabase kbDriver kbJET kbODBC kbOLEDB kbProvider kbODBC350bug kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2
Version : WINDOWS:1.0,1.5,2.0,3.5,3.51,4.0
Platform : WINDOWS
Issue type : kbbug