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

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.

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.

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:

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