ADO Recordset Field Names And Extended Column Information

Checking this box allows extended column information to be retrieved. The extended column information provides support for the ODBC API SQLGetDescField (and SQLColAttribute) to return information for the following:

SQL_DESC_AUTO_UNIQUE_VALUE
SQL_DESC_BASE_COLUMN_NAME
SQL_DESC_BASE_TABLE_NAME
SQL_DESC_TABLE_NAME
SQL_DESC_LABEL
SQL_DESC_SCHEMA_NAME
SQL_DESC_SEARCHABLE
SQL_DESC_UPDATABLE

The extended column name feature is not enabled by default. Most of our clients that are programming with ODBC do not use the ODBC API directly. It is far more common to use Microsoft's ActiveX Data Object library, more commonly known as ADO or ADODB. This ActiveX library, when coupled with Microsoft's MSDASQL OLE DB provider for ODBC data sources, allows programmers to use ADO with ODBC connections to write applications in much less time and with far fewer lines of code than if they coded directly to the ODBC API.

The ADO recordset object represents a result set, and it has rows containing fields that have attributes and values. An individual field can be referenced by its name or by its ordinal value in the row. For example, if the name of the first field is 'CUSNUM', then rs.fields(1) and rs.fields('CUSNUM') are equivalent.

The name of the field in the recordset varies when the extended column information setting is changed and the column has a column heading defined. For example, here is a table that was created by using the following DDS:

.....A..........T.Name++++++RLen++TDpB......Functions+++++++++++++++++++++++++++
*************** Beginning of data **********************************************
                R CUSBOTHREC                                                    
                  CUSNUM         6S 0B      COLHDG('Customer' 'Number')        
                                            TEXT('Customer Number Field')      
                  LSTNAM         8A  B      COLHDG('Customer' 'Last' 'Name')    
                                            TEXT('Last Name Field')            
                  INIT           3A  B      COLHDG('First &' 'Middle Initial')  
                                            TEXT('First & Middle Initial Field')
                  STREET        13A  B      COLHDG('Street' 'Address')          
                                            TEXT('Street Address Field')        
                  CITY           6A  B      COLHDG('City')                      
                                            TEXT('City Field')                  
                  STATE          2A  B      COLHDG('State')                    
                                            TEXT('State Abbreviation Field')    
                  ZIPCOD         5S 0B      COLHDG('Zip' 'Code')                
                                            TEXT('Zip Code Field')              
                  CDTLMT         4S 0B      COLHDG('Credit' 'Limit')            
                                            TEXT('Credit Limit Field')          
                  CHGCOD         1S 0B      COLHDG('Change' 'Code')            
                                            TEXT('Change Code Field')          

The name of the first recordset field is 'CUSNUM' when the extended column information is not retrieved; however, it is 'Customer Number' when extended column information is selected. The reason for the name change is that ADO assigns the name to the recordset field based on the value returned from the SQL_DESC_LABEL, and that value depends on whether the option is set to retrieve extended column information and on whether the column has a column heading.

A second point of confusion can be that the label has so many embedded spaces. The reason is that the column heading is really a value made up of three subfields that are 20 characters in length each. When you see the DDS function COLHDG (in the previous example), that becomes clearer.

A common problem that a user of an ADO-based program might encounter is to get an error stating that the column was not found. The likely cause for this is that the program was written to reference the fields in the recordset by name and in the case where the error occurs, the ODBC data source being used has a different setting for the extended column information option than what the programmer used. To prevent this problem, a programmer might reference the fields by ordinal value, perhaps substituting constants with meaningful names for the integer values. Or the application might make a DSN-less connection. Or perhaps the easiest way to prevent the use of extended column information when a DSN is being used on the connection is to override the extended column information attribute on the connection string. For example:

cnn.Open "Provider=MSDASQL;Data Source=aaa;EXTCOLINFO=0;"