BUG: Refreshing ADO Record's Fields Collection Does Not Retrieve Metadata Information

• Microsoft Data Access Components versions 1.5, 2.0, 2.1, 2.1 (GA), 2.1 SP1, 2.1 SP2, 2.5

SYMPTOMS

Using ActiveX Data Objects (ADO), when implicitly preparing a recordset by calling RS.Fields.Refresh method, the metadata information (for example, BASETABLENAME, BASECOLUMNNAME, KEYCOLUMN) is not available. There are no errors generated when querying for such information.

RESOLUTION

The work around is to set the Recordset's "Unique Rows" property to TRUE. Note that "Unique Rows" is a provider property that is available only when using the adUseServer cursor location. You may change the cursor location to adUseClient immediately after setting the "Unique Rows" property. See implementation details in the More Information section of this article.

STATUS

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

MORE INFORMATION

Steps to Reproduce Behavior

1. Start a new project in Visual Basic and choose Standard EXE. Form1 is created by default.



2. In the Visual Basic project, add a reference to the Microsoft ActiveX Data Objects 2.5 Library or an earlier library.



3. Double-click Form1. Copy and paste the following code into the Form_Load() event:




    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim fld As ADODB.Field
    
    cn.Open "Provider=SQLOLEDB;Data Source=MyServer;User ID=sa;Password=xxxx;Initial Catalog=Pubs"
    rs.ActiveConnection = cn

  ' Uncomment the following line to workaround this problem
  ' rs.Properties("Unique Rows").Value = True

  ' You may uncomment the following line to use Client Cursors
  ' rs.CursorLocation = adUseClient

    rs.Source = "SELECT * FROM Authors"
    rs.Fields.Refresh

    For Each fld In rs.Fields
        Debug.Print fld.Properties!BASETABLENAME.Value
        Debug.Print fld.Properties!BASECOLUMNNAME.Value
        Debug.Print fld.Properties!KEYCOLUMN.Value
    Next fld 

4. Run the project and notice that for each field in the fields collection, the immediate window shows:




Null
Null
False

Additional query words:

Keywords : kbGrpVBDB kbGrpMDAC kbDSupport kbMDAC250bug
Version : WINDOWS:1.5,2.0,2.1,2.1 (GA),2.1 SP1,2.1 SP2,2.5
Platform : WINDOWS
Issue type : kbbug