FIX: Refreshing ADO Record's Fields Collection Does Not Retrieve Metadata Information (245493)


The information in this article applies to:

  • Microsoft Data Access Components 1.5
  • Microsoft Data Access Components 2.0
  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.1 (GA)
  • Microsoft Data Access Components 2.1 SP1
  • Microsoft Data Access Components 2.1 SP2
  • Microsoft Data Access Components 2.5
This article was previously published under Q245493

SYMPTOMS

Using ActiveX Data Objects (ADO), when you implicitly prepare 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 that this is a bug in the Microsoft products that are listed at the beginning of this article.

Microsoft has confirmed that this is a problem in Microsoft ActiveX Data Objects 2.6 and later. You can download the latest version of Microsoft ActiveX Data Objects from the following Microsoft Web site:

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

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 Library.
  3. Double-click Form1. Copy and paste the following code into the Form_Load() event. Modify the Connection String to connect to your SQL Server.

    Note You must change User ID <User ID> and password <Strong Password> to the correct values. Make sure that User ID has the appropriate permissions to perform this operation on the database. 
        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=<User ID>;Password=<Strong Password>;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

Modification Type:MajorLast Reviewed:11/5/2003
Keywords:kbBug kbDatabase kbfix kbMDAC260fix KB245493
©2003 Microsoft Corporation. All rights reserved.