Introduction
It is not possible to insert fields (just like MS Access) using plain ADOX append column method. The reasons being:
Append
of course, will just add a column at the end of collection;
- 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!
- Create an empty temporary table
- Copy each field from the original table using the correct ordinal sequence. Append the field to insert when the insertion point is reached.
- Copy the records from the original table into the temporary table
- Copy the indexes into the temporary table
- *Copy/Restore other table properties or objects if any
- *Temporarily remove the original table from the relationships (
MSysRelationships
)
- Delete the original table
- Rename the temporary table to the name of the original table
- *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.
#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")
#include <comdef.h>
#include "oledb.h"
#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"));
ColumnNameToOrdinal columnMap;
if (GetColumnNameToOrdinalMap(p_table, columnMap))
{
_ColumnPtr fieldToAdd;
fieldToAdd.CreateInstance(__uuidof (Column));
fieldToAdd->PutRefParentCatalog(mp_catalog);
fieldToAdd->PutName(_T("Quality"));
fieldToAdd->Properties->GetItem
(_T("Description"))->PutValue(_T("Quality assessment"));
fieldToAdd->PutType(adVarWChar);
fieldToAdd->PutDefinedSize(64);
fieldToAdd->Properties->GetItem
(_T("Nullable"))->PutValue
(_variant_t(VARIANT_TRUE,VT_BOOL));
fieldToAdd->Properties->GetItem
(_T("Jet OLEDB:Allow Zero Length"))->PutValue
(_variant_t(VARIANT_TRUE,VT_BOOL));
fieldToAdd->Properties->GetItem
(_T("Default"))->PutValue(_bstr_t("Not assessed"));
_bstr_t str_error;
if (InsertField(p_table, fieldToAdd,
columnMap[_T("Value")], str_error))
{
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
InsertField
does not work on tables containing BLOB
s or Long Binary
s. 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 BLOB
s between the tables.
- 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
- 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
- The base ATL application was generated using the ATL version 7's AppWizard.
- 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.
- Turn /GX option on, to support exception handling
- 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