Insert Field (not append) Using ADOX

• Download source files - 7.12 Kb
• Download demo project - 81.1 Kb

Introduction

It is not possible to insert fields (just like MS Access) using plain ADOX append column method. The reasons being:

1. Append of course, will just add a column at the end of collection;
2. The collection of fields/columns are arranged in alphabetical order

This article presents a way to programmatically insert a field into a MS Access database table using ADOX. There are other ways like using ALTER TABLE SQL command. However, using ADOX is very attractive because it allows access to provider specific field properties like description, default, auto-increment, nullable, allow-zero-length, etc.

Methodology

The method is pretty straightforward: reconstruct the table!

1. Create an empty temporary table
2. Copy each field from the original table using the correct ordinal sequence. Append the field to insert when the insertion point is reached.
3. Copy the records from the original table into the temporary table
4. Copy the indexes into the temporary table
5. *Copy/Restore other table properties or objects if any
6. *Temporarily remove the original table from the relationships (MSysRelationships)
7. Delete the original table
8. Rename the temporary table to the name of the original table
9. *Restore original table's relationship

*Not addressed in this article or in the code sample.

Reconstructing the table is actually not that straightforward. The first problem to overcome here is that the fields collection sorted the fields in alphabetical order. The solution is already presented in another article: Getting the Correct Column Ordinals of an ADOX Table Object.

Furthermore, providers have their own special table and field properties. When reconstructing the table, make sure properties that your application use are restored properly. This may be a case-by-case basis, so this article does not deal further into specifics. (See Limitations section and the TODO items in the code as to where improvements and specialization may occur.)

<SandBox>Well, for purist, the method is plain ugly. I don't like it either, but it works and served my project pretty well. I just hope that this article becomes a starting point to better solutions.</SandBox>

Using the code

Here is the function prototype:

static BOOL InsertField(_TablePtr p_table, 
   _ColumnPtr p_field, long l_beforeIndex, _bstr_t & rstr_error)

Inserts a new field into the table before a specific ordinal position.

Parameters

• OField & p_newField - a reference to a new OField object
• long l_beforeIndex - inserts the new field at this position
• _bstr_t & rstr_error - reference to a string that will receive the error message if any

Returns

• BOOL - TRUE if the field was successfully inserted

Notes

The new field should have a default value or, the "Nullable" property should be set to TRUE, otherwise the insert operation will fail. What happens is that after the temporary table is filled up with the original table's records, the new field value is NULL (if no default is given).

You will need to include the file ADOXInsertField.h into your project; or copy-paste it into your class. The file ADOXColumnOrdinal.h is required and is also included in this package.

Note: If not already set, you need to set the /GX compiler option to support exception handling.

Please make sure you have the following in your stdafx.h or somewhere accessible.

// 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>
// OLEDB<
#include "oledb.h"
// And of course, our code<
#include "ADOXInsertField.h"

Example

The following code inserts a new field called Quality at the ordinal position currently held by the field Value.

_TablePtr p_table =  mp_catalog->Tables->GetItem(_T("MyTable"));

// Get a conversion map from ADOX collection index into actual field ordinal
ColumnNameToOrdinal columnMap;
if (GetColumnNameToOrdinalMap(p_table, columnMap))
{
    // Here is the field we want to insert
    _ColumnPtr fieldToAdd;
    fieldToAdd.CreateInstance(__uuidof (Column));

    // Set the reference catalog so that we can access Jet OLEDB properties
    fieldToAdd->PutRefParentCatalog(mp_catalog);
    // Field Name
    fieldToAdd->PutName(_T("Quality"));
    // Description of the field
    fieldToAdd->Properties->GetItem
         (_T("Description"))->PutValue(_T("Quality assessment"));
    // Field Type
    fieldToAdd->PutType(adVarWChar);
    // Field Size
    fieldToAdd->PutDefinedSize(64);
    // Nullable
    fieldToAdd->Properties->GetItem
         (_T("Nullable"))->PutValue
         (_variant_t(VARIANT_TRUE,VT_BOOL));
    // Allow Zero Length 
    fieldToAdd->Properties->GetItem
         (_T("Jet OLEDB:Allow Zero Length"))->PutValue
         (_variant_t(VARIANT_TRUE,VT_BOOL));
    // Default Value 
    fieldToAdd->Properties->GetItem
         (_T("Default"))->PutValue(_bstr_t("Not assessed"));

    // Now do the insert thing
    _bstr_t str_error;
    if (InsertField(p_table, fieldToAdd, 
            columnMap[_T("Value")], str_error))
    {
        // Note! p_table is no longer valid
        // because it is pointing to a table that has 
        // already been replaced by a new one containing the inserted field

        // So get it again
        p_table = mp_catalog->Tables->GetItem(_T("MyTable"));
    }
    else
    {
        ::MessageBox(NULL, str_error,_T("Insert"), 
                            MB_ICONINFORMATION|MB_OK);
    }

}

Limitations, where to go from here

1. InsertField does not work on tables containing BLOBs or Long Binarys. This is due to the fact that the temporary table is populated by standard SQL statement. You may need to use ADO/OLEDB methods to transfer BLOBs between the tables.
2. Only the following field properties collection items are supported:
   • Default
   • Description
   • Nullable
   • Jet OLEDB: Allow Zero Length

   To improve the code, you may add support for the following in the CopyFieldProperties function.

   • Autoincrement
   • Fixed Length
   • Seed
   • Increment
   • Jet OLEDB:Column Validation Text
   • Jet OLEDB:Column Validation Rule
   • Jet OLEDB:IISAM Not Last Column
   • Jet OLEDB:AutoGenerate
   • Jet OLEDB:One BLOB per Page
   • Jet OLEDB:Compressed UNICODE Strings
   • Jet OLEDB:Hyperlink
3. Does not work on tables used in relationships. This can be examined by scanning the MSysRelationShips table szObjects field. If your table is there, step 7 of the method above will fail. To make it work, you will have to figure out how to remove and restore the entries (steps 6 and 9 respectively).

Observations

Most of the problems were encountered when copying fields properties from the original table into the temporary table. Here are the most painful:

Nullable field property anomaly

On some Jet OLE DB configurations (2.5 or 2.6 mixed with MS Access installation?), the "Nullable" property from the column property collection is reversed. For example: Set "nullable" to TRUE and after you add the column, and get the "nullable" property back - you get FALSE! Although it is actually set to TRUE when you look at it from MS Access.

To get around it, when reading "nullable" property use the GetAttributes method:

BOOL b_nullable = p_sourceField->GetAttributes()&adColNullable;

but when setting the "nullable" property, use the field property collection:

p_targetField->Properties->GetItem(_T("Nullable"))->PutValue
                                        (_variant_t(VARIANT_TRUE,VT_BOOL));

Then why not use Get/PutAttributes altogether? Answer: because using p_field->PutAttributes() method to set the "nullable" property will cause an exception when appending the field into the collection. Go figure :-(

Don't touch my property!

When transferring properties collection, it would be natural to just loop to the collection and set it to the other field. This way, I won't be listing #2 item under Limitations section above. For example:

for (long i = 0; i < p_source->Properties->GetCount(); i++)
{
    _variant_t var_prop;
    _bstr_t name = p_source->Properties->GetItem(i)->GetName();
    ATLTRACE(_T("Field Property %d: %s\n"), i+1, (LPCTSTR)name);
    try
    {
        var_prop = p_source->Properties->GetItem(i)->GetValue();
        if (var_prop.vt != VT_EMPTY && var_prop.vt != VT_NULL)
        {
            p_destination->Properties->GetItem(i)->PutValue(var_prop);
        }
    }
    catch(...)
    {
    }
}

The above code would run ok but when we finally add the field (p_destination) into the table, boom! OLEDB 0x80040e21 - multistep error. Is the property collection that sensitive, it does not want you to touch properties that are not relevant to the field? I don't have more time to fuss around this, so I'm leaving it to you guys to tell me what's going on.

To work around this problem, the CopyFieldProperties() will just copy selected properties and before doing so, will test the relevance of the type of the field. See Limitations section on what properties are supported.

Demo application

The sample project is a simple ATL application. It was intended just to show how the InsertField works but it has grown to support browsing of field properties and delete field. Anyway, in the demo project, 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 list view control
• Reflection, notification handling in WTL
• Using DDX in ATL/WTL
• and of course, using both ADOXColumnOrdinal.h and ADOXInsertField.h

Notes

1. The base ATL application was generated using the ATL version 7's AppWizard.
2. 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 ADOXInsertField.cpp file.

Finally

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.

History

• 2 Jun 2003 - Included the change in ADOXColumnOrdinal.h to dynamically detect bookmark support
• 8 May 2003 - Initial release