Click here to Skip to main content
13,140,591 members (48,992 online)
Click here to Skip to main content
Add your own
alternative version


27 bookmarked
Posted 4 Dec 2002

Correct Column Ordinals of an ADOX Table Object

, 2 Jun 2003
Rate this:
Please Sign up or sign in to vote.
Getting the correct column ordinals of an ADOX Table object.

Sample Image - ADOXColumnOrdinal.gif


The ADOX Table object allows us to easily retrieve each table column using the Columns collection. However, the collection is sorted by the column/field name. In one of my projects, I need to support the following:

  1. Represent the columns collection in the same order as when being displayed in MS Access
  2. Be able to insert new columns anywhere in the collection (another article will discuss this)

Using the ADOX Columns collection alone is inadequate. Hence, the need to get the actual column ordinals.


There are two ways to get the column ordinals:

  • By walking through the rowset returned by ADO OpenSchema(adSchemaColumns,...) - slow
  • Getting the DBCOLUMNINFO data from OLEDB IColumnsInfo interface - faster

This article discusses how to get the column ordinals from the IColumnsInfo OLEDB interface.

The IColumnsInfo is available from (implemented by) the command and rowset OLE DB objects. The method I present is basically composed of three steps:

  1. Create an ADO Recordset from the ADOX Table
  2. Convert the ADO Recordset into an OLEDB IRowset
  3. Get the IColumnsInfo from IRowset

Here we go...

Step 1. Create an ADO Recordset from the table

// ADO recordset
ADODB::_RecordsetPtr p_adoRecordset;

    _variant_t((IDispatch *)p_connection,true), ADODB::adOpenKeyset,
    ADODB::adLockReadOnly, ADODB::adCmdTable);

Step 2. Convert the ADO Recordset into IRowset

// First, get ADORecordsetConstruction interface from the the ADO Recordset
ADODB::ADORecordsetConstruction * p_adoRecordsetConstruct;
    (void **) &p_adoRecordsetConstruct);

// From it, we can get the OLEDB <code>IRowset
IRowset * p_rowset;
p_adoRecordsetConstruct->get_Rowset((IUnknown **)&p_rowset);
p_adoRecordsetConstruct->Release();  // don't need it anymore

Step 3. Get the IColumnsInfo from IRowset

From IColumnsInfo, we can get the DBCOLUMNINFO data which contains the information we want. In this example, I create a map of the column name and the ordinal.

// The IColumnsInfo that contains ordinals
CComPtr<IColumnsInfo> spColumns;

// Determine if bookmark is supported (New in 3 Jun 2003)
BOOL b_hasBookMark = FALSE;
CComPtr<IRowsetInfo> spRowsetInfo;
HRESULT hr = p_rowset->QueryInterface(IID_IRowsetInfo, 
if (hr == S_OK)
    DBPROPSET* pPropSet = NULL;
    ULONG ulPropSet = 0;

    hr = spRowsetInfo->GetProperties(1, &set, &ulPropSet, &pPropSet);
    if (hr == S_OK)
        b_hasBookMark = 
          (pPropSet->rgProperties[0].vValue.boolVal == VARIANT_TRUE);

// Get the the IColumnsInfo from IRowset interface
// At this point, we may now release p_rowset

// IColumnsInfo will give us the DBCOLUMNINFO structure
DBCOLUMNINFO * p_columnInfo = NULL;
LPOLESTR pstr_stringBuffer = NULL;
ULONG ul_numColumns;

// Now get the DBCOLUMNINFO data 
spColumns->GetColumnInfo(&ul_numColumns, &p_columnInfo, &pstr_stringBuffer);

// Let's create a mapping for columName and the actual ordinal
typedef std::map<<_bstr_t, long>> ColumnNameToOrdinal;
ColumnNameToOrdinal columnOrdinals;

// Using the DBCOLUMNINFO, fill the name-to-ordinal map
TCHAR * psz_fieldName;
for (ULONG i = 0; i < ul_numColumns; i++)
    psz_fieldName = OLE2T(p_columnInfo[i].pwszName);
    if (p_columnInfo[i].pwszName != NULL)
        // iOrdinal of 0 is reserved for book mark, if supported
        columnOrdinals[_bstr_t(psz_fieldName)] = 
            p_columnInfo[i].iOrdinal- b_hasBookMark;

// Clean up

Using the code

The file ADOXColumnOrdinal.h contains static functions that you may want to directly use in your project or cut-and-paste into your class. You may include the file in your stdafx.h as shown below (items in bold are required):

// I assume you have this somewhere
#import "C:\Program Files\Common Files\System\ado\msado15.dll" \
    rename( "EOF", "adoEOF" ) rename("DataTypeEnum", "adoDataTypeEnum")
#import "C:\Program Files\Common Files\System\ado\msadox.dll" \
    rename( "EOF", "adoEOF" ) no_namespace 
            rename("DataTypeEnum", "adoDataTypeEnum")
// For _bstr_t and _variant_t
#include <comdef.h>
#include "oledb.h"
// And of course, our code
#include "ColumnOrdinals.h"
Note: If not already set, you need to set the /GX compiler option to support exception handling.

Data structures

The following map definitions are used, respectively, in the functions described in the next section. Please refer to the function descriptions and notes on how to use these maps.

typedef std::map<long, long> ColumnOrdinalToIndex
typedef std::map<_bstr_t, long> ColumnNameToOrdinal
typedef std::map<long, _bstr_t> ColumnOrdinalToName

Utility functions

The following three utilities are provided as static functions so that you can easily adapt them into your class or library.


static BOOL GetColumnOrdinalToIndexMap(_TablePtr p_table, 
                                 ColumnOrdinalToIndex & columnOrdinals)

This function will get a mapping of the ADOX field indices to the correct ordinals.


  • _TablePtr p_table - the ADOX table object
  • ColumnOrdinalToIndex & columnOrdinals - a reference to a map defined as std::map<long, long> which will receive the map of ADOX index to ordinals.


  • BOOL - TRUE if no errors were encountered, FALSE otherwise.


The ColumnOrdinalToIndex map (my favorite:-) is useful in determining the ADO index (index to GetItem) when accessing the nth field. e.g. sequentially listing the fields by their correct order:

ColumnOrdinalToIndex ordinals;
GetColumnOrdinalToIndexMap(p_tableFromSomewhere, ordinals);

_ColumnPtr p_column = NULL;
for (long i; i < p_tableFromSomewhere->Columns->Count; i++)
    p_column = p_tableFromSomewhere->Columns->GetItem(columnMap[i]);
    ATLTRACE(_T("%d.) %s\n"), i, p_column->GetName());


static BOOL GetColumnNameToOrdinalMap(_TablePtr p_table, 
                                   ColumnNameToOrdinal & columnOrdinals)

This function will get a mapping of the field names to the correct ordinals.


  • _TablePtr p_table - the ADOX table object
  • ColumnNameToOrdinal & columnOrdinals - a reference to a map defined as std::map<_bstr_t, long> which will receive the map of column names to ordinals


  • BOOL - TRUE if no errors were encountered, FALSE otherwise


The ColumnNameToOrdinal map is useful if you know a field name and you want to get the ordinal. For example:

ColumnNameToOrdinal ordinals;
GetColumnNameToOrdinalMap(p_tableFromSomewhere, ordinals);

ATLTRACE(_T("The ordinal of %s is %d\n"), _T("myField"), 


static BOOL GetColumnOrdinalToNameMap(_TablePtr p_table, 
                                       ColumnOrdinalToName & columnOrdinals)

This function will get a mapping of the ADOX field indices to the correct ordinals.


  • _TablePtr p_table - the ADOX table object
  • ColumnOrdinalToName & columnOrdinals - a reference to a map defined as std::map<long, _bstr_t> which will receive the map of map of ordinals to the field names


  • BOOL - TRUE if no errors were encountered, FALSE otherwise


The GetColumnOrdinalToNameMap map is useful in getting the name of the nth field. e.g. sequentially listing the fields by their correct order:

ColumnOrdinalToIndex ordinals;
GetColumnOrdinalToIndexMap(p_tableFromSomewhere, ordinals);

for (long i; i < p_tableFromSomewhere->Columns->Count; i++)
    ATLTRACE(_T("%d.) %s\n"), i, ordinals[i]);

In practice

The functions above gets the ordinals in bulk. It would be impractical to get the ordinals every time, say, you want to display a field. This could slow down your application tremendously.

The proper way of implementation would be to make one of the maps shown above as a member of your document, dialog or a wrapper class. So you will only get the ordinals once, when your object is created.

The sample application

The sample project is a simple ATL application. You can see a bit of:

  • Using ADO and ADOX together
  • Using ADOX Catalog, Table and Column objects
  • Scanning for OLEDB provider error
  • Basic way of subclassing a ListView control
  • Reflection, notification handling in WTL
  • Using DDX in ATL/WTL


  1. The base ATL application was generated using the ATL version 7's AppWizard.
  2. Lesson learned: When compiling the release mode I had to remove the _ATL_MIN_CRT defined in the project settings to resolve link error : LNK2001 symbol '_main' not found. The main() entry point is required by STL.
  3. Turn /GX option on to support exception handling
  4. If you want to compile it with ATL version 3, comment out the line with the AtlInitCommonControls() in ADOXColumnOrdinal.h file.


Finally, may I say - the codes here were written while I'm cooking my dinner :-). It may not be perfect, but I hope it helped to illustrate my points.


  • 3 Jun 2003 - Dynamically detects whether bookmark is supported in the rowset
  • 5 Dec 2002 - Initial release


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Software Developer (Senior)
United States United States
Roaming halfway around the globe programming in C++, MFC, COM/ATL, WTL, C#, .NET, OLEDB, ADO, ADO/X.

Living under the pleasant weather of Irvine, California, Ferdie is a Computer Engineering graduate of Mapua Institute of Technology (MIT Smile | :) ) in Philippines. Developed GIS applications in Japan for 5 years. Now a member of a team developing Windows GUI and real time software for semi-robotic equipments.

You may also be interested in...


Comments and Discussions

GeneralCrash in AddPropertyID [modified] Pin
7.e.Q16-Aug-07 2:46
member7.e.Q16-Aug-07 2:46 
QuestionVB.Net equivalent? Pin
JDMils11-Mar-06 16:36
memberJDMils11-Mar-06 16:36 
Generalatlres.h Pin
krippi23-Jun-04 0:36
memberkrippi23-Jun-04 0:36 
GeneralPlease help Pin
SAHorowitz22-Apr-03 17:30
memberSAHorowitz22-Apr-03 17:30 
GeneralRe: Please help Pin
Ferdie7-May-03 19:19
memberFerdie7-May-03 19:19 
GeneralTasty! Pin
Phil J Pearson6-Jan-03 1:46
memberPhil J Pearson6-Jan-03 1:46 
GeneralThanks Pin
Paul Selormey6-Jan-03 0:50
memberPaul Selormey6-Jan-03 0:50 
Generalatlres.h missing Pin
Gregor Schiller5-Jan-03 10:39
memberGregor Schiller5-Jan-03 10:39 
GeneralRe: atlres.h missing Pin
Christian Graus5-Jan-03 10:49
memberChristian Graus5-Jan-03 10:49 
GeneralRe: atlres.h missing Pin
Ferdie5-Jan-03 11:43
memberFerdie5-Jan-03 11:43 
GeneralRe: atlres.h missing Pin
Gregor Schiller5-Jan-03 16:59
memberGregor Schiller5-Jan-03 16:59 
GeneralRe: atlres.h missing Pin
krippi22-Jun-04 1:40
memberkrippi22-Jun-04 1:40 
GeneralRe: atlres.h missing Pin
Ferdie22-Jun-04 10:49
memberFerdie22-Jun-04 10:49 
GeneralThanks! it's just the one needed Pin
Mariduke5-Dec-02 4:47
memberMariduke5-Dec-02 4:47 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.170915.1 | Last Updated 3 Jun 2003
Article Copyright 2002 by Ferdie
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid