BUG: Problems Reading and Writing Dynamic Properties of ADOX Column When You Use SQLOLEDB (288444)

SYMPTOMS

When you connect to SQL Server using the SQL Server OLE DB Provider, if you use ActiveX Data Objects Extensions for DDL and Security (ADOX), the ADOX Column object exposes six dynamic properties in its Properties collection: Autoincrement, Default, Fixed Length, Nullable, Primary Key, and Unique.

However, any attempt to read one of these properties on an existing Column in an existing Table generates the following error message:

Error 3251, "Object or provider is not capable..."

You may also encounter problems when you try to set these properties on a new Column unless you follow specific steps, which are described in the "More Information" section.

MORE INFORMATION

According to the Microsoft Data Access Components (MDAC) Readme file, the properties of existing Column objects in existing Tables are read-only. However, as a result of the bug discussed in this article, the above-mentioned properties cannot be read for existing Columns. You may also encounter problems when you try to set these properties on a new Column, unless you follow the specific steps, as described below.

Steps to Reproduce Behavior

How to Read Properties from Existing Column in an Existing Table

Create a new Standard EXE project in Visual Basic. Form1 is created by default.
From the Project menu, click References, and then click Microsoft ADO Ext. 2.x for DDL and Security and Microsoft ActiveX Data Objects 2.x Library.

Paste the following code into a Form1, and adjust the SQL Server connection parameters as necessary.

Note You must change User ID =<UID> and password =<strong password> to the correct values before you run this code. Make sure that <UID> has the appropriate permissions to perform this operation on the database.

  Dim cn As ADODB.Connection
  Dim cat As ADOX.Catalog
  Set cn = New ADODB.Connection
  With cn
    .Provider = "SQLOLEDB"
    .ConnectionString = "Data Source=(local);Initial Catalog=Northwind;User ID=<UID>;Password=<strong password>;"
    .Open
  End With
  Set cat = New ADOX.Catalog
  Set cat.ActiveConnection = cn
  Debug.Print cat.Tables("Customers").Columns("CustomerID").Properties("Primary Key").Value

This sample attempts to determine whether the CustomerID column in the Customers table is part of the table's Primary Key and should return True. In fact, it causes the above-mentioned error message.

How to Set Properties on New Column in New Table

Create a new Standard EXE project in Visual Basic. Form1 is created by default.
From the Project menu, click References, and then click Microsoft ADO Ext. 2.x for DDL and Security and Microsoft ActiveX Data Objects 2.x Library.

  Dim cn As ADODB.Connection
  Dim cat As ADOX.Catalog
  Dim tbl As ADOX.Table
  Dim col As ADOX.Column
  Set cn = New ADODB.Connection
  With cn
    .Provider = "SQLOLEDB"
    .ConnectionString = "Data Source=(local);Initial Catalog=Northwind;User ID=<UID>;Password=<strong password>;"
    .Open
  End With
  Set cat = New ADOX.Catalog
  Set cat.ActiveConnection = cn
  Set tbl = New ADOX.Table
  tbl.Name = "ADOXTest"
  Set col = New ADOX.Column
  With col
    .Name = "testing"
    .Type = adInteger
  End With
  tbl.Columns.Append col
  tbl.ParentCatalog = cat     '<--- This is the important line!
  col.Properties("Default").Value = 1
  cat.Tables.Append tbl

This sample specifies the Default value for new column. If you omit the line that specifies the table's ParentCatalog property, the last line of code generates the above-mentioned error message.