Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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

View products that this article applies to.

This article was previously published under Q245493

↑ Back to the top


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.

↑ Back to the top


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.

↑ Back to the top


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:

↑ Back to the top

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

↑ Back to the top

Keywords: KB245493, kbmdac260fix, kbfix, kbdatabase, kbbug

↑ Back to the top

Article Info
Article ID : 245493
Revision : 6
Created on : 5/17/2007
Published on : 5/17/2007
Exists online : False
Views : 382