ADOX in Detail

About article. This article is about database
creation and structure modification using MS ADOX library in a program. We’ll
discuss how to use ADOX for these purposes, some database object details and
samples. Additional information you can find on MSDN site, there are some links
to its resources in this text. Some database architecture advices are added for
more complete problem understanding.

About ADOX. Microsoft ADOX (Active Data Objects
Extension) is an ADO extension and, as written in MSDN, it is a companion
library for ADO. Some ADOX objects uses ADO objects and interfaces but it is
not necessary to know all ADO objects, methods and properties to use ADOX.

Same as ADO, ADOX is an interface level above OLE DB
interface. It is wrapper for OLE DB objects. You can use ADOX and do not need
to know something about OLE DB and providers. You just need to specify OLE DB
provider, which will be used to process operations with database.

ADOX is a COM library. It can be used in any language and
system that supports COM technology. It implements IDispatch interface so you
can use it in script languages which support only late COM binding. In most
cases it is used same way as ADO and if you are familiar with ADO, you won’t
get difficulties with ADOX. But sometimes you need some additional knowledge
about databases to get result you want. We’ll discuss some details in this
article.

Samples are tested on operation system Windows 2000,
databases Access2000 and MS SQL 2000, MDAC 2.6. Samples are written on VB
because it takes less code and C programmers usually understand VB. VB samples
in text are colored using one color, VC
samples are placed between lines “VC specific–>” and “<–VC specific end”
and colored using standard color schema of Visual Assist Add-in for VC
. So VB programmers may omit such parts of article and VC programmers
can pay attention to VC specific code. VC samples are created not for all cases
but only where it is necessary. I hope that most VC developers can create C
code from VB sample. If you think that it is better to add VC samples for all
cases, please send email to me.

Table of content:

  1. Database creation and modification techniques
    1. Transact SQL language
    2. DAO
    3. ADOX
  2. ADOX Object Model
    1. ADOX collections
    2. ADOX objects
  3. How to add ADOX support to your program
  4. Catalog object
  5. Table object
  6. Column object
  7. Index object
  8. Key object
  9. View object
  10. Procedure object
  11. User object
  12. Group object

1. Database creation and modification techniques

If you want to create or modify database objects in your
program you can use next ways:

a) Transact SQL language
(MS SQL, Access 2000)

You can write T-SQL clause, which creates or alters table,
columns, users etc. and execute it using ADO or other database access
component. For example next clause “ALTER TABLE UserLogs Add Column newCol
Number
” adds to Access database new column “newCol” to table “UserLogs”,
type of “newCol” data values is Numeric. You can create other T-SQL clause to
do other operations with database. Read SQL Books Online from MS SQL Server
distributive for more details about T-SQL language.

b) DAO (Access)

Data Access Object is API for access to database access via
MS Jet, ISAM or ODBC. It has objects that can be used to modify database
objects and request data. DAO is optimized for MS Access database but supports
other databases.

Later Microsoft created database support libraries based on
OLE DB interfaces. For most database application developers it looks like DAO
is splitted to 3 parts: ADO, ADOX and JRO, but of course, internal
implementation differs from DAO. It is recommended to use new libraries instead
of DAO in new projects. ADO is used to access to database data, ADOX to database
structure, JRO for replication and compacting database. You can read how to
migrate from DAO to ADO in MSDN.
To see differences between DAO, ADOX and JRO see another
article in MSDN

c) ADOX

ADOX can be used not only for Access or SQL but also for all
databases that have OLE DB provider. Some ADOX methods and properties may be
not supported by provider but most common operation usually can be performed.
Next article we’ll discuss MS SQL and Access compatibility with ADOX and get
application to test operations supported by specific provider.

2. ADOX Object Model

a) ADOX collections

All ADOX collections have next methods: Append, Delete,
Refresh and properties: Count, Item. Like most COM collections, ADOX
collections have default function if you use it in VB or VBScript. Item
property is default for ADOX collection. So you can omit word Item in your
program: collName.Item(“first”) is the same
as collName(“first”). You can refer to
collection item using either its name like above or order number collName(0). First item has number 0, second 1,
last collName.Count 1.

If you want to enumerate all objects in a collection you can
use For cycle in two ways:

1) Using Each keyword (VB, VBScript)


For Each dbItem In dbCollection
‘do smth. e.g. MsgBox dbItem.Name
Next

2) Using Count property


Dim ind As Integer
For ind=0 To dbCollection.Count – 1
‘ do smth. e.g. MsgBox dbCollection.Item(ind).Name
Next

VC specific ———->
VC has no keyword Each but
you can use method _NewEnum to get next
item in collection. Usually it is more convenient to enumerate items by
index.



long ind;
for (ind = 0; ind< dbColl->Count; ind++)
MessageBox(dbColl->GetItem(ind)->GetName());

<——- end of VC specific

Some objects in ADOX have collection “Properties” which
consists of provider-specific properties for object.

b) ADOX objects.

There are next objects in ADOX library: Catalog, Table, Column, Index, Key, Group, User, Procedure and
View. Objects Procedure
and View cannot be created as standalone
object using New operator in VB or CoCreateInstance in VC because they don’t have
public class factory. These objects can be created by corresponding collections
Procedures and Views.  Next picture from MSDN shows ADOX object
hierarchy (object Command is not ADOX object, it is ADO library object).

You can see that Columns
collection is a property of objects Table, Index and Key.
Also Users and Groups
collections have cross-references. But objects in different hierarchy level
usually have different properties values. E.g. property SortOrder
for Column object
under Table and Key
has undefined value but may have value for Index
object.

Objects Table, Index and Column
have collection Property with
provider-specific properties values. Remember that Properties
are provider-specific. Some provider supports one set of properties, other
providers support other set. First make sure that provider supports necessary
property or find same property name for other provider. Do not confuse with Properties collection that are provider specific
collection and objects’ fixed properties (name, type etc.) Once more, fixed
properties are common for all providers but Properties
collection contains provider-specific properties.

If provider doesn’t support property which you want to set
value, error will occur. If you add new object with such property defined,
error will occur when you try to add it to collection, if you try to set
property for existing object error will occurred when you try to refer to it.
MSDN recommends reading provider documentation to get information about
supported properties.

All ADOX objects are “passive” objects. I mean that you can
change its state or some properties but they do not do some operations. You can
create them and add into collection or remove. ADO has “active” objects that
fetch records from database, change its order etc. You can see that only three
ADOX objects have methods in addition to properties. But these methods only get
and set owner for database objects (methods GetPermissions
and SetPermissions for objects Catalog, User and
Group; method SetPassword
of object User) and as a matter of fact they
also change property of objects. “Active” entities in ADOX are object
collections. ADOX object collections create and delete database objects. It is
good enough software architecture. Of course, it is some kind of “philosophy”
notes and usually it is not important for your application.

To see more details about ADOX object model see MSDN
article
.

3. How to add ADOX support to your program.

If you use Visual Basic, you can add ADOX to your program
choosing menu Project\References. ADOX is a
COM library so you don’t need to create Declare directive in VB. In the list of
COM objects under this menu item try to find record “Microsoft ADO Ext. 2.6 for
DDL and Security” or same (your version may differ) and check it on. If your
list is very big and you can’t find it try to add reference using “browse” button.
Usually file is placed in same directory as ADO: “Program Files\Common
Files\System\ADO\msadox.dll”. If you can’t find this library, install MDAC from
Microsoft site and then add reference to it.

VC specific begin—->

If you use Visual C, use directive #import.
Next two lines show sample of this directive for both libraries ADO and ADOX.
In case that you need to use only ADOX library, do not write first line that
adds reference to ADO library.


#import “C:\Program Files\Common Files\System\ADO\msado15.dll” rename(“EOF”, “EndOfFile”)
#import “C:\Program Files\Common Files\System\ADO\msadox.dll”

Note. Do not use no_namespace
attribute for #import directive if you use
both ADO and ADOX. Some names are declared in both libraries. In this case you
need to specify namespace before object library name e.g. ADOX::Table. Other variant is to use rename attribute in #import
directive same as for ADO in sample above but you need to enumerate all double
defined names and its new synonyms.

When you import COM library VC creates for you special
classes that wraps COM objects in library. It is very useful directive with
some attributes. I recommend you to read about it in MSDN
if you do not use it. There are 2 types of wrapper classes: one of them is
typed with Ptr suffix (_CommandPtr, TablePtr etc.) and second is without
suffix. First class has some functions which checks result of called function,
raises exception if necessary, returns value for properties etc. To understand
wrappers you can place break point on method call and go into method (F11 key)
or analyze .tli and .tlh files. You can see next example of wrapper class
usage:


void VCsample1(ADOX::TablesPtr tblColl)
{
ADOX::_Table *pTbl1; // without suffix
ADOX::_TablePtr pTbl2; // with suffix
// tblColl is a collection of tables, get first table from collection
pTbl2 = tblColl->GetItem((long)0);
// get name of table. Tree lines below calls one function.
//Second line Name is a synonym to GetName().
//Third line also is transformed into call of function GetName.

_bstr_t tN0 = pTbl2->GetName();
_bstr_t tN1 = pTbl2->Name;
_bstr_t tN2 = tblColl->Item[(long)0]->Name;
BSTR tN3;
HRESULT hr;
pTbl1 = tblColl->GetItem((long)0);
// get name of table. Next two lines calls raw interface functions.
hr = pTbl1->get_Name(&tN3);
SysFreeString(tN3);>
hr = pTbl2->get_Name(&tN3);
SysFreeString(tN3);
pTbl1->Release();
pTbl2->Release();
}

Use method, which is more suitable for you. I prefer in most
cases wrapper functions. Note that you cannot omit GetItem
method call unlike in VB. VC ignores “default” method declaration in COM
library typelib and you need to tell to compiler what you do want exactly. And
don’t forget to release objects when you don’t need them more. VB adds these
calls where it is necessary (it has Garbage Collector subsystem).

<—- VC specific end

4. Catalog object.

Catalog object is a
database instance object. It is first object, which you need to use in your
ADOX program. Its collections represent database objects: Tables, Views, Procedures, Users, and Groups. Every database object is included in
corresponding collection. If there is no object of some type in database (e.g.
database is empty) collection of this type is empty.

Catalog object property:

  • ActiveConnection ADO Connection
    object which defines database connection parameters.

Methods:

  • Create creates new database;
  • GetObjectOwner returns String user or group name of database object owner;
  • SetObjectOwner sets new or changes existing owner of database object.

Catalog is high-level
object and if we want to modify some database object we need first to create Catalog object: either create new database or
connect to existing one.

Connect to existing database:


Public Function initialize(connectionSting As String)
Dim dbMan As ADOX.Catalog
On Error Resume Next
Err.Clear
Set dbMan = New ADOX.Catalog
If Err.Number <> 0 Then
MsgBox “cannot create ADOX object. Please install MDAC from Microsoft site”
Else
dbMan.ActiveConnection = connectionSting
If Err.Number <> 0 Then
MsgBox “Cannot initialize connection. Please check connection string”
End If
End If
End Function

Property ActiveConnection
is ADO Connection object. As far as Connection object has default property ConnectionString which type is String line dbMan.ActiveConnection
= connectionSting
sets property ConnectionString
of object dbMan.ActiveConnection to
some value. To set Connection object to
property ActiveConnection you can use code Set dbMan.ActiveConnection = connectionObject.

Create new database method sample:


Public Function createDb(connectionSting As String)
Dim dbMan As ADOX.Catalog

On Error Resume Next
Set dbMan = New ADOX.Catalog
If Err.Number <> 0 Then
MsgBox “cannot create ADOX object. Please install MDAC from Microsoft site”
Else
dbMan.Create connectionSting
If Err.Number <> 0 Then
MsgBox “Cannot create database. Please check connection string”
End If
End If
End Function

Connection string in this case must point to not existing
file. If file already exists you’ll get error message.

Connection string for ADOX have same format as for ADO.
E.g.”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\new.mdb”. If you
don’t know how to create connection string you can read about it in MSDN
or create file with extension “udl”, e.g. “connection.udl” and double click
left button on it in explorer. You’ll see dialog box with some tabs on it.
Specify provider, file and other information. Then view it in a text editor
(e.g. notepad). You’ll get connection string to chosen file or DSN.

Methods GetObjectOwner
and SetObjectOwner
are used to get and
change owner of database object. For example, if you want to delete user from
database, you have to reassign its objects to other user or delete them at all.
SetObjectOwner may be used to change object
owner.


Sub changeTableOwner(dbCatalog As ADOX.Catalog)
Dim ownerName As String
On Error GoTo errCatch
ownerName = dbCatalog.GetObjectOwner(“Table1”, adPermObjTable)
dbCatalog.SetObjectOwner “Table1”, adPermObjTable, “Admin”
Exit Sub
errCatch:
MsgBox Err.Description
End Sub

Let’s see methods description.

Owner = Catalog.GetObjectOwner(ObjectName, ObjectType [, ObjectTypeId])

Returns a String value that specifies the Name of the User
or Group that owns the object.

Catalog.SetObjectOwner
ObjectName, ObjectType , OwnerName [, ObjectTypeId]

Specifies the owner of an object in a Catalog.

Parameters:

ObjectName – a String value that specifies the name of the object for which to return the
owner.
ObjectType – a Long value which can be one of the ObjectTypeEnum
constants, that specifies the type of the object for which to get the owner.
OwnerName – a String value that specifies the Name of
the User or Group
to own the object.
ObjectTypeId – optional. A Variant value that specifies the GUID for a provider object type
not defined by the OLE DB specification. This parameter is required if
ObjectType is set to adPermObjProviderSpecific;
otherwise, it is not used.

Remarks

An error will occur if the provider does not support returning object owners.

Unfortunately in my case (ADOX 2.6, both providers Jet 4.0
and SQLOLEDB) providers do not support this methods. I get error number
0x800A0CB3 (“Object or provider is not capable of performing requested
operation”) for SQLOLEDB and error number 0x80004005 for Jet 4.0 provider
(“Unspecified error”).

ObjectTypeEnum
constants have next values:

Constant

Description

adPermObjColumn

The object is a column.

adPermObjDatabase

The object is a database.

adPermObjProcedure

The object is a procedure.

adPermObjProviderSpecific

The object is a type defined by the provider. An error
will occur if the ObjectType parameter is adPermObjProviderSpecific and an ObjectTypeId is not supplied.

adPermObjTable

The object is a table.

adPermObjView

The object is a view.

You can see that Catalog
object do not have properties same as DAO Database
object properties (Version, Name, Updatable, Replicable etc.). Some databases
don’t support same properties and as far as ADOX is universal library for
different databases, it doesn’t support such properties.

Lets suppose you create application, which works with Access
database, and database structure may be changed from one version of your
program to other. You need to determine which database structure you have.
First variant is checking database structure features which define database
version, e.g. if some table exists in one version of database and does not
exist in other, you can check whether this table exists in unknown version of
database. Second variant is creating special table, which contain database
version. I think that second variant is very ugly solution, first variant much
better.

Let’s enumerate all database tables and users using Catalog object collection.


Private Sub enumerateTableCollections(dbCatalog As ADOX.Catalog)
Dim dbTable As ADOX.Table
Dim dbUser As ADOX.User
Dim itemsNo As Integer
Dim str As String

str = “There are ” & dbCatalog.Tables.Count & ” tables” & vbCrLf
For Each dbTable In dbCatalog.Tables
str = str & dbTable.name & ” ”
Next
MsgBox str

On Error Resume Next

str = “Users:” & vbCrLf
itemsNo = dbCatalog.Users.Count
If (Err.Number = 0) And (itemsNo > 0) Then
For Each dbUser In dbCatalog.Users
str = str & dbUser
Next
Else
str = str & “there are no”
End If
MsgBox str
End Sub

You can see differences between cycles. In first case we get
items from Tables collection without checking collection length (items count).
We just get collection item using For Each
cycle. It is normal usage of collection. If collection is empty this cycle
won’t be executed. But realization of Users
and Groups collections in ADOX 2.6 possible
has bugs. Workaround of this problem is to check length of collection (items
count). If collection is empty it causes error when you refer to Count property. So we need to catch error. We placed On
Error Resume Next
statement and check Err.Number
value. If there is no error collection is not empty and vise versa.

I tried to analysis this problem and emulated For Each cycle in VC but didn’t find errors yet.
Possible it takes more detailed analysis. We’ll talk about these collections
more detailed in next article.

You can walk through other collections like in sample above.
You can find procedure which work with all collections in a file with sample
code.

Begin of VC specific ——–>

Sample code on VC that make same operations. See inline
comments in sample text.


//this procedure emulates For Each cycle in VB and gets
collection items’ name

void enCollections(ADOX::_Collection *pColl)
{
IEnumVARIANT *pEnum;
ULONG lFetched;
VARIANT var;
pColl->raw__NewEnum((IUnknown**)&pEnum);// get enumerator object
for ( ; ; )
{
//clear variable which will contain collection item object
VariantInit(&var);
// Next method returns IDispatch encapsulated into VARIANT variable
// if VARIANT type is VT_EMPTY then collection is empty

pEnum->Next(1, &var, &lFetched);
if (var.vt== VT_EMPTY) break;
if (var.vt== VT_DISPATCH)
{
DISPID dspId;
HRESULT hr;
OLECHAR *propertyName = L”Name”;
// Since we work with IDispatch interface we need to know DISP_ID
//of “Name” property

hr = var.pdispVal->GetIDsOfNames(IID_NULL, &propertyName, 1,
LOCALE_SYSTEM_DEFAULT, &dspId);
if (SUCCEEDED(hr))
{
VARIANT vVal;
// clear variable which will contain property value
VariantInit(&vVal);
DISPPARAMS dispparamsNoArgs = {NULL, NULL, 0, 0};
// get property value
hr = var.pdispVal->Invoke(dspId, IID_NULL, LOCALE_USER_DEFAULT,
DISPATCH_PROPERTYGET,&dispparamsNoArgs, &vVal, NULL, NULL);
if (SUCCEEDED(hr) && VT_BSTR == vVal.vt)
{
char ascName[256];
memset(ascName, 0, 256);
// convert UNICODE string to ASCII string
WideCharToMultiByte(CP_ACP, 0, vVal.bstrVal, -1, _
ascName, 256, NULL, NULL);
MessageBox(NULL, ascName, “Name”, 0);
}
}
// release collection item object
var.pdispVal->Release();
}
// clear object container variable
VariantClear(&var);
}
// release resources
pEnum->Release();
VariantClear(&var);
}

void CADOXDBwalkerDlg::enumerateCollections()
{
CoInitialize(NULL);
ADOX::_CatalogPtr pDbCatalog;
ADOX::Users *pUsrColl;
ADOX::Views *pVwColl;
ADOX::TablesPtr tblColl;
try
{
HRESULT hr = pDbCatalog.CreateInstance(__uuidof(Catalog));
_variant_t connStr(L”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\newDb.mdb;”);
//set ActiveConnection property
pDbCatalog->PutActiveConnection(connStr);
// get Tables collection and enumerate its items via IDispatch interface
// same as VB process For Each cycle
tblColl = pDbCatalog->Tables;
enCollections(tblColl);
// get collection items count
long tblCount = tblColl->Count;
// other way.
long ind;
for (ind = 0; ind< tblColl->Count; ind++)
MessageBox(tblColl->GetItem(ind)->GetName());
pVwColl = pDbCatalog->Views;
enCollections(pVwColl);
long vwCount = pVwColl->Count;


pUsrColl = pDbCatalog->Users;
enCollections(pUsrColl);
long usrCount = pUsrColl->Count;
}

catch (_com_error & er)
{
MessageBox(er.Description(), “error”);
}
CoUninitialize();
}

<——– end of VC specific

All ADOX collections have method Append that adds new item to collection. You can read
how to add new table, view, procedure, user and group to corresponding
collection in next paragraphs.

5. Table
object

Table is one of basic concept of database and simplest
databases may be represented as tables set. When database is created first of
all tables structure is created. Lets discuss this important object in ADOX.

ADOX Table
object represents database table or view. In other words, in ADOX views are
tables also. View in some point is same as table (you can get data from both
table and view same way and even change data for some views) and possible this
is a reason why views can be represented by Table object.

Properties:

Name database table name, unique in Tables collection;
Type – type of table: users’ table, system table
etc.(read only);
ParentCatalog reference to parent Catalog object which table belong;
DateCreated and DateModified – date when table was created and last
modification date (read only).

Collections:

Columns – table columns;
Indexes – table indexes;
Keys – table keys;
Properties – provider-specific table properties.

As we discussed above, Table object can refer to database table or to
database view. Property Type
is a string value, which shows table type, e.g. “VIEW”, “TABLE”, “ACCESS
TABLE”, “SYSTEM TABLE” etc. Users’ tables have Type “TABLE”. This property is read only and you
can’t set table type, it is set by ADOX automatically. If you create new table
it have type “TABLE” i.e. it is user-defined table.

Table object has provider-specific collection Properties. When you create new Table and append columns or indexes to it,
you may access to such properties for new objects. But first you need to specify
provider. You can do it in ADOX using one way. Set property ParentCatalog to existing Catalog object already connected to some database.
ADOX will get provider type from Connection property of Catalog object. If you
do not set ParentCatalog property
Properties collection is
empty.


Public Sub newTable(db_Table As String, dbCatalog As ADOX.Catalog)
Set dbTable = New ADOX.Table
dbTable.name = db_Table
Set dbTable.ParentCatalog = dbCatalog
dbTable.Columns.Append “newColumn”
dbTable.Columns(“newColumn”).Properties(“Autoincrement”)=True
End Sub

Next table shows which items in Properties collection of Table object is available for Access Jet OLE DB
provider v4. All items are typed with necessary space symbols and same case as
they are named in collection.

Property name Property type Read/Write
Temporary Table adBoolean Read only
Jet OLEDB:Table Validation Text adBSTR R/w
Jet OLEDB:Table Validation Rule adBSTR R/w
Jet OLEDB:Cache Link Name/Password adBoolean R/w
Jet OLEDB:Remote Table Name adBSTR R/w
Jet OLEDB:Link Provider String adBSTR R/w
Jet OLEDB:Link Datasource adBSTR R/w
Jet OLEDB:Exclusive Link adBoolean R/w
Jet OLEDB:Create Link adBoolean R/w
Jet OLEDB:Table Hidden In Access adBoolean R/w

Next article we’ll discuss Properties more detailed.

Lets talk about Table collections. Columns collection contains objects that represent
database columns. You already know how to access to existing collection item. If
you want to add new Column object
you can do it by name or by object reference. In sample above we added column by
name. In this case we will get column with default properties (data type, length
etc.) To add column with different properties you need first to create Column object and set its properties to
some values and only then append it. Some properties you can add after appending
new column to collection (e.g. DefinedSize) but if it is possible in your program
use common way: create object, set its properties and then add it to
collection.

Other collections Indexes and Keys are same as Columns collection except some nuances. If you want
to append index or column by name without creating object same as in sample
above we created column object, you need to specify other parameters. As a
matter of fact parameters called as optional are not optional in such case. As
we told above Append
method has 2 possible usages: adding item by name and by reference to existing
object. In second case parameters may be optional if you have specified all
necessary properties for Index or Key object. You can use them but if you do mistake
and parameters of object will conflict with parameters that you specify in Append function call, you’ll get an error
message. If you add object by name and do not create standalone object before
add it to collection, you need to define all necessary parameters. Which
parameters are necessary we’ll tell in next paragraphs [7, 8].

This situation with collection as far as I see is a lack
of ADOX collection design. For some experienced programmers it may be more
flexible to use such design but potentially it may cause some bugs, which at
first sight not very easy to catch. Use carefully Append method. It is better to use one programming
technique with it. Either add all objects by name and all necessary parameters
or create object, set its properties and then add it without additional
parameters.

We know how refer to existing table and create new one.
Next step is to add new table to database. As it was documented in MSDN, it is
possible to add new table by its name or by refer to new Table object same as
columns and indexes discussed above. But unfortunately I couldn’t force ADOX 2.6
to add Table by name. It returns an error. But we know other way how to create
Table object and set its properties.
Next operation adds it to collection:


dbCatalog.Tables.Append dbTable

6. Column
object

Column object
refer to database column in a table. Also Column may be a part of Keys or Indexes collections in ADOX object model. Table Column object differs from key Column and index Column thought they all have
same properties (except provider-specific) but some properties have empty value.
It depends on collection, which Column
belongs.

Properties:

Name – column name, it has to be unique in a Columns collection.
Type – type of column data: Integer, String etc.
ParentCatalog – pointer from column to catalog, which
owns column
Attributes – specify whether column can contain nulls
or be fixed-length
DefinedSize maximum size of column data (in
bytes)
NumericScale – specify the scale for data types Numeric and Decimal
Precision specify the maximum precision for numeric
data value
RelatedColumn – specify the name of the related
column in the related table, for key columns
SortOrder – specify whether the sort order is
ascending or descending for index columns.

Collection:

Properties – provider-specific column properties.

Most properties are valid for table columns only (name
have defined value for all collections). RelatedColumn property have value only for
columns that belong to Key collection Columns, SortOrder property have
value for Index
collection Columns. First let’s talk
about Table columns properties.

Type is a value that defines
column data type (e.g. Integer, Text, Date etc.) Type values are set of some
predefined integer values. ADOX library defines special data type called DataTypeEnum. DataTypeEnum article in MSDN. This type is subset of
integer values of Variant variable type (integer value which VB function VarType returns). Values of Type are
provider specific and not all values from DataTypeEnum type can be set to this property.

Available types for Access provider Jet:

Type property value (ADOX) Column data type (Access)
adBoolean Yes/No
adInteger Number, LongInteger
adSmallInt Number, LongInt
adUnsignedTinyInt Number, Byte
adNumeric Number, Decimal
adDecimal Number, Decimal
adDouble Number, Double
adSingle Number, Single
adCurrency Currency
adBinary Binary
adVarBinary Binary
adLongVarBinary OLE Object
adWChar Text
adVarWChar Text
adLongVarWChar Memo
adGUID Binary
adDate DateTime

We’ll discuss data types in
ADOX more detailed in next article.

Attributes property for Column object specify next data attributes for
column: adColFixed (the column is a fixed length), adColNullable (column can
contain null values) and 0 (default, there are no attributes).

Property DefinedSize specifies maximum size for column
data. Use this property for types that may have different size (Text and Binary
in Access). For other types provider may ignore its value. You can try to set
this property for column type adInteger to 100 but field size in Access as a
matter of fact will be 4 bytes. You need to use Type property to set size of
numeric column (LongInteger or Byte).

Property NumericScale ignored for all column data types except adNumeric and adDecimal. It
specifies number of decimal places to the right of decimal point.

As written in MSDN property Precision used only for numeric data
types. Provider can ignore this value for some types. E.g. Access do not
supports precision=2 for adLongInt data type. I tried to create column with this
values but when I read this property value after appending new column, it was
equal to 10. I think this value is important for adNumeric and adDecimal data
types only.

Property RelatedColumn can be used for columns used in references to other
tables. If database table has foreign key reference to other table, each column,
which included into foreign key has its related column. In this case property
RelatedColumn has some sensible
value. For columns in other type of key, columns in Index and Table objects collections this property either has
empty value of type Variant or empty BSTR string.

Property SortOrder has defined value for columns in table
Index object collection. Its values
are adSortAscending (default) and adSortDescending. It defines how column data
is sorted in the index.

 Read about indexes in next paragraph ([7]).

Provider-specific properties collection Properties depends not only on provider but also
on type of column collection container object (Table, Index and Key). E.g. Index and Key columns don’t have items in Properties collection for my ADOX database but Table columns have items in this
collection.

Next table shows which items in Properties collection of Column object is available for Access Jet provider
v4. All items are typed with necessary space symbols and same case as they are
named in collection.

Property
name

Property
type

Read/Write

Autoincrement

adBoolean

R/w

Default

adVariant

R/w

Description

adBSTR

R/w

Nullable

adBoolean

R/w

Fixed Length

adBoolean

R/w

Seed

adInteger

R/w

Increment

adInteger

R/w

Jet OLEDB:Column Validation Text

adBSTR

R/w

Jet OLEDB:Column Validation Rule

adBSTR

R/w

Jet OLEDB:ISAM Not Last Column

adBoolean

R/w

Jet OLEDB:AutoGenerate

adBoolean

R/w

Jet OLEDB:One BLOB per Page

adBoolean

R/w

Jet OLEDB:Compressed UNICODE Strings

adBoolean

R/w

Jet OLEDB:Allow Zero Length

adBoolean

R/w

Jet OLEDB:Hyperlink

adBoolean

R/w

You can see that some numeric types, which in Access have
type Number and subtypes (Byte, Integer, LongInt etc.) in ADOX are different
types. Some ADOX types represented in Access as one type (adVarBinary and
adBinary). Type Hyperlink in ADOX has additional property in Properties collection.
Sometimes before create columns look at tables above and analyze them.

Note. Unique column values is not a column property but
property of its key (see next paragraphs). So if you want to create unique
constraint, create unique key. Insert duplicate values into column will create
error.

And finally lets see VB sample.


Sub addNewTable
Dim dbTable As New ADOX.Table

On Error Resume Next

dbTable.name = “myNewTable”
Set dbTable.ParentCatalog = dbCatalog

‘ we set ParentCatalog property to access to
‘ provider-defined properties for Column object
‘ If we don’ t use Properties collection before appending
‘ table, we don’t need to set this property

dbTable.Columns.Append “id_Column”, adInteger
dbTable.Columns.Append “newColumn”, adInteger
dbTable.Columns(“id_Column”).Properties(“Autoincrement”) = True
If Err.Number <> 0 Then
MsgBox Err.Description & Err.Number
Err.Clear
End If

dbCatalog.Tables.Append dbTable
If Err.Number <> 0 Then
MsgBox Err.Description & Err.Number
MsgBox Err.Description & Err.Number, vbOKOnly, “error”, Err.HelpFile()
Err.Clear
End If

dbTable.Columns(“newColumn”).Type = adWChar
‘ we’ll get error because we set column type
‘ after appending it to table

If Err.Number <> 0 Then
MsgBox Err.Description & Err.Number
Err.Clear
End If

Dim mColumn As New ADOX.Column
mColumn.name = ” newColumn2″
mColumn.Properties(“myProp”) = “myVal”
dbTable.Columns.Append mColumn

‘ we’ll get error because property “myProp” doesn’t
‘ exists in Properties collection.
‘ but column will be added to table

If Err.Number <> 0 Then
MsgBox Err.Description & Err.Number
Err.Clear
End If
End Sub

7. Index object

First lets talk about database indexes. Sometimes
developers have troubles because they don’t understand what are indexes for.
Index is a special database structure, which allow searching records faster than
using simple table scan. Index can be created on one or more columns in table.
Column values may differ from one record to another. When database engine search
records with certain column value it have to compare column values of records
with defined value in SQL clause. This situation is same as search item with
necessary value in array or linked list. Scan method takes to see all items in
worse case. More smart methods are used to increase performance. For large
data   
storages (like database) more effective is creating B-Tree or other tree
structure and searching elements in it.

Key field (I mean value which is used to search item, not a database key) for index tree nodes may be
different, it depends on database engine. But it is important that this key is
created depending on column value. There are so many different key values for
tree as many different values for table column. If database column has Boolean
type (only two values) tree has only two nodes and it takes other additional
methods to find necessary record. But if database field has unique values it is
good case for index creation. Sure, you can create key for not unique columns.
For this case database engine possible creates additional structure to each tree
node, which has more than one column reference. For example, this structure may
be a list of column references.

Key can be created not for single column but for a group
of columns. Key field for tree nodes created depending on all column values. For
example, integer and Boolean columns make good enough key for index tree if
integer column values range is big enough.

It is not important for you how indexes are implemented
in database engine, which you use (if you are not database engine developer) but
if you know a little about it you can create indexes that really increase
performance and won’t increase memory treatments. Use indexes only where it is
necessary, do not create indexes for memo fields, do not create indexes for all
possible combinations of columns etc. Analyze your application SQL requests
statistics and only then create necessary index.

Note. Key field for tree, which we told above, is not the
same as database key. It is standard term. You can read article about B-Tree.

Let’s return to ADOX Index object.

Properties:

Name index name;
Unique indicates whether the index keys must be unique;
PrimaryKey indicates whether the index is the
primary key for a table;
IndexNulls indicates whether records that have null
values in their index fields have index entries;
Clustered indicates whether the index is
clustered.

Collections:

Columns columns that index consist of;
Properties provider-specific properties.

Unique property type is boolean. It
only specifies index structure type. It tells to database engine whether create
simple B-Tree for unique columns or add list to B-Tree nodes.

PrimaryKey is also boolean type
property. Only one index may be primary key index for a table because only one
primary key is allowed. As a matter of fact, usually keys have corresponding
indexes e.g. primary key. But not every index has a corresponding key.

Property IndexNulls has next values (MSDN):

adIndexNullsAllow

The index does allow entries in which the key
columns are null. If a null value is entered in a key column, the entry is
inserted into the index.

adIndexNullsDisallow

Default. The index does not allow entries in which
the key columns are null. If a null value is entered in a key column, an
error will occur.

adIndexNullsIgnore

The index does not insert entries containing null
keys. If a null value is entered in a key column, the entry is ignored and
no error occurs.

adIndexNullsIgnoreAny

The index does not insert entries where some key
column has a null value. For an index having a multi-column key, if a null
value is entered in some column, the entry is ignored and no error
occurs.

Property Clustered means that records in table has same
physical sort order as index. I.e. records are stored in a database in order
defined by index. You can create only one clustered index per a table.

You can set properties of object Index only before you append it to database. If Index object already exist in collection
Indexes, its properties are read only. When you add index to database it creates
all necessary structures for existing data. To change Index object property you need first to delete it
from collection and then create new one. It takes some time to delete and create
index. Also if you want to create clustered index on a table, which already has
data, it will also take some time to reorganize table physical storage.

You can read articles by Dusan
Petkovic t
o know more about indexes: Indexing for Sort Performance and Which is Faster: Index Access or Table Scan? and
other (e.g. Tips on Optimizing SQL Server Indexes)

VB Sample:



Public Sub newIndex(dbTable As ADOX.Table)
‘ dbTable is table where we add new index
Dim dbIndex As ADOX.Index
On Error GoTo errCatch

Set dbIndex = New ADOX.Index
dbIndex.name = “tblPrimaryIndex”
dbIndex.Clustered = True
dbIndex.IndexNulls = adIndexNullsDisallow
dbIndex.PrimaryKey = True
dbIndex.Unique = True

dbIndex.Columns.Append “id_Column”
‘ id_Column is a name of column

dbTable.Keys.Append dbIndex

dbTable.Indexes.Append “column2Index”, “newColumn2”
‘ newColumn2 is name of column in our table
Exit Sub

errCatch:
MsgBox Err.Description & Err.Number
End Sub

8. Key
object

Key means something that identifies record. For example,
table column has unique values in a table. This column may be called as key. Key
can consist from some columns. There are primary and foreign keys.

Primary key is a key which value is unique for table and
number of its columns less than number of columns in other unique column
combination values. In most cases primary key is one column.

Records form one table may refer to other (there is
relationship between these columns). In this case column, which refers to other
table, is called “foreign key”. Usually foreign key refers to primary key in
other table. But sometimes it refers to column(s) that is not a primary key.
Such column in referred table is usually called “secondary” (in ADOX
“unique”).

Key almost always used in reference to “constraint” term.
Constraint is some kind of restriction. Primary key is column(s) unique value
constraint. Primary key cannot have NULL value. Foreign key constraint usually
means that column value refers to only one column in other table, NULL values
also are not allowed. Foreign key used to force database integrity. Foreign key
constraint can perform cascade deleting rows with foreign key value when
referred row in primary table is deleted.

When you add key to table, provider can add index for
this key, e.g. Access Jet adds index for columns, which compose key. It may be
done to increase database performance. But if you read Dusan
Petkovic
article “Which is Faster: Index Access or Table Scan?” you know
that indexes do not always increase performance.

ADOX Key object properties:

Name key name;
Type key type: primary, foreign or unique;
RelatedTable name of related table;
DeleteRule action performed on delete primary
key;
UpdateRule – action performed on update primary
key.

Collection:

Columns database key columns.

Property Type can have values: adKeyPrimary (default, for
primary keys), adKeyForeign (foreign key), adKeyUnique (for unique constraints
in a table).

RelatedTable property has valid value only for foreign
keys. It doesn’t make sense for other types of keys.

DeleteRule and UpdateRule properties are constants of type
RuleEnum (MSDN):

adRICascade

Cascade changes.

adRINone

Default. No action is taken.

adRISetDefault

Foreign key value is set to the default.

adRISetNull

Foreign key value is set to
null.

Type of columns used in relationship between tables must
be same. You cannot refer from Text column to Integer one and from Integer to
LongInt. You’ll get error in such case. Also do not create reference from not
nullable column to nullable, i.e. column types and some attributes must be
same.

If you add relationship from unique key to unique key
you’ll get relation One-To-One. Relation from unique column to not unique is
One-To-Many relationship. Table with unique column in this case is called
“primary”. Relation from not unique column to not unique one is a nonsense. You
cannot create standard constraint for such references. If you want to create
relation Many-To-Many, create additional intermediate table for references from
first table to second and create references One-To-Many from first table to
intermediate table and One-To-Many from second table to intermediate. Both first
and second tables have unique columns, intermediate table has not unique
columns.

When you add foreign key first you have to create index
on a column in other table. ADOX expects that you add reference to column, which
has unique values. If referred column do not have unique index, error occurs “no
unique index found for the referenced field of the primary table”. Primary table
is table, which you refer to, table where you create key is secondary. When you
create unique index over non-unique, ADOX do not automatically delete existing
index. You’d better do it yourself.

Next sample works for Jet OLE DB provider version 4.
Unfortunately SQLOLEDB provider returned error message “Match Type is invalid or
the value is not supported by the provider” although later I created foreign key
constraint using ALTER TABLE clause. We’ll discuss this point in next article.
Sample adds keys to table:


Sub addNewKey(dbPrimTable As ADOX.Table)
Dim dbKey As New ADOX.Key
On Error Goto errCatch

‘ add primary key
dbKey.name = “newColumnKey”
dbKey.Type = adKeyPrimary
dbKey.Columns.Append “id_Column”
dbTable.Keys.Append dbKey

‘ add foreign key
dbKey.name = NameEB.Text
dbKey.Type = adKeyForeign
dbKey.RelatedTable = “myPrimaryTable”
dbKey.DeleteRule = adRICascade
dbKey.UpdateRule = adRICascade
‘ add secondary table column “newColumn2” to key
dbKey.Columns.Append “newColumn2”
‘ set referenced column (primary column) in primary table
dbKey.Columns.Item(“newColumn2”).RelatedColumn = “id_clmn”

‘insert reference index
Dim dbIndex As ADOX.Index
Set dbIndex = New ADOX.Index
dbIndex.name = “Ref_” & dbKey.name
dbIndex.Unique = True
dbIndex.Columns.Append “id_clmn”
dbPrimTable.ParentCatalog.Tables(“myPrimaryTable”).Indexes.Append dbIndex

dbPrimTable.Keys.Append dbKey
Exit Sub
errCatch:
MsgBox Err.Description & Err.Number
End Sub

9. View
object

View is a special table, which is created by select
request. It is usually called “virtual” table because it may be processed same
as simple table (select, even update and insert data in a updateable view) but
view data are stored in a tables that are used in a view. To know about
updateable views read your database documentation.

Usually views are used as a frequently used select clause
or complex select clause that demands some analysis by database engine. Database
engine usually prepares execution plan for view and data is fetched according
this plan faster than using simple select clause.

ADOX View
object properties:

Name database view name;
Command ADO Command object reference;
ParentCatalog – pointer to Catalog object which view belong (missed in MSDN);
DateCreated and DateModified – view creation and last modification
dates

As far as View is not a Table object, do not create indexes and keys for it
(usually you get error message). Use view as complex query but not as table.


Public Sub addView(dbCatalog As ADOX.Catalog)
On Error Resume Next
Dim dbCommand As New ADODB.Command

dbCommand.CommandText = “SELECT * FROM mTable1 INNER JOIN mTable2 _
ON mTable1.id_clmn=mTable2.ref_id;”
dbMan.getViews.Append “myNewView”, dbCommand
End Sub

10. Procedure
Object

Usually database stored
procedures are used to process some difficult database operations. We won’t
discuss whether use or not stored procedures in your database. You can read
about it some other articles.

In Access you can create
database query and store it in database. Such queries have 2 types: select
queries and create (Insert Into, Select Into, Alter Table) queries. First type
of queries is view, which we discussed above. Second type is procedure.
Unfortunately, you cannot create more complex procedures in Access Jet 4.0
(Access 2000). It doesn’t understand CREATE Procedure’ and ALTER Procedure’
clauses, possible Access XP does.

And in other hand, I couldn’t
create stored procedure for SQLOLEDB provider (MS SQL) using ADOX and see
existing procedure. When Procedure
object property Command is called
from program used this provider, it returns E_NOINTERFACE error (0x80004002).
But same program with Jet OLE DB provider works correctly. We’ll discuss this
point in next article.



Public Sub getProcInfo(db_Procedure As ADOX.Procedure)
Dim str As String
On Error Resume Next

str = “Procedure ” & dbProcedure.name & vbCrLf
str = “Date Created ” & dbProcedure.DateCreated & vbCrLf
str = “Date Modified ”
str = str & dbProcedure.DateModified
‘ Procedure may be not modified after creation and
‘ when you try to get DateModified property
‘ provider can raise error. Same as for Table object

If Err.Number<>0 Then
str = str & dbProcedure.DateCreated
Err.Clear
End If
str = str & vbCrLf

str = “Command Text ”
CommandEB.Text = “‘” & dbProcedure.Command.CommandText & “‘”
If Err.Number<>0 Then
str = str & “
Err.Clear
End If
str = str & vbCrLf
MsgBox str
End Sub

Public Sub createProc(dbCatalog As ADOX.Catalog)
Dim dbCommand As New ADODB.Command
On Error Resume Next

dbCommand.CommandText = “ALTER TABLE UserLogs Add Column newCol Number”
dbMan.getProcedures.Append “myNewProc”, dbCommand
End Sub

11. User
object

Database can have data access delimitation engine. Users
can have different database access restrictions and database checks user
database privilegies and restricts not allowed operations. Unfortunately Jet 4.0
and SQLOLEDB providers with ADOX 2.6 do not support all User object functionality. Possible new MDAC version include all necessary components to support all ADOX functionality for MS Access
and MS SQL server.

ADOX User
object property:

Name database user name.

Methods:

ChangePassword change the password for
a user;
GetPermissions determine whether a user has read,
write, or delete permissions;
SetPermissions qhange user
permissions.

Collections:

Groups all groups which user belong;
Properties provider-specific properties
collection

ChangePassword method description:

User.ChangePassword OldPassword, NewPassword

Parameters:

OldPassword – a String value that specifies the user’s existing password. If the user doesn’t currently have a password, use an empty string (“”) for OldPassword.
NewPassword – a String value that specifies the new password.

GetPermissions and SetPermissions methods:

ReturnValue = User.GetPermissions(Name,
ObjectType  
[, ObjectTypeId])


Returns a Long value that
specifies a bitmask containing the permissions that the user has on the object.
This value can be one or more of the RightsEnum constants.

User.SetPermissions Name, ObjectType, Action, Rights [, Inherit] [, ObjectTypeId]

Specifies the permissions for a
user on an object.

Parameters:

Name – a Variant value that specifies the name of the object for
which to set or get permissions. Set Name to a null value if you want to get the
permissions for the object container.
ObjectType – a Long value which
can be one of the constants, that specifies the type of the object for which to
get or set permissions.
Action – a Long value which can be one of the ActionEnum constants that specifies the type of
action to perform when setting permissions.
Rights> – a Long value which can be a bitmask of one or more of the
RightsEnum constants,
that indicates the rights to set.
Inherit – optional. A Long value which can be one of the InheritTypeEnum constants, that specifies
how objects will inherit these permissions. The default value is adInheritNone.
ObjectTypeId – optional. A Variant
value that specifies the GUID for a provider object type not defined by the OLE
DB specification. This parameter is required if ObjectType is set to adPermObjProviderSpecific; otherwise, it is not used.

You can read about constants in MSDN: RightsEnum,  InheritTypeEnum, ActionEnum, ObjectTypeEnum is described in this article above (paragraph about Catalog
object), and in MSDN.

As far as my system do not supports User object, lets see
sample based on sample from MSDN:


Sub ChangeUserPassword(dbUser As ADOX.User, oldPassword As String, newPassword As String)
dbUser.SetPassword oldPassword, newPassword
End Sub

Sub ChangeUserPermissions(dbUser As ADOX.User)
Dim lngPerm As Long
lngPerm = dbUser.GetPermissions(“Orders”, adPermObjTable)
‘ Revoke all permissions
dbUser.SetPermissions “Orders”, adPermObjTable, adAccessRevoke, adRightFull
End Sub

12. Group
object

Database users can be organized in different users
groups, e.g database administrators, database users, database data readers,
database data writers etc.

ADOX Group
object property:

Name database group name.

Methods:

GetPermissions determine whether a group has read,
write, or delete permissions;
SetPermissions change group permissions.

Collections:

Users all users which belong to group;
Properties provider-specific properties
collection.

Situation with Group object is same as for User object. I cannot force to work it as described
in MSDN documentation and shown on MSDN samples. Possible next versions of MDAC
fix these problems.

All methods and properties of Group object are same as
User object properties except SetPassword.


Sub ChangeGroupPermissions(dbGroup As ADOX.Group)
Dim lngPerm As Long
lngPerm = dbGroup.GetPermissions(“Orders”, adPermObjTable)
‘ Revoke all permissions
dbGroup.SetPermissions “Orders”, adPermObjTable, adAccessRevoke, adRightFull
End Sub

Resume. Using ADOX is a good
solution for database management applications except some problems with some
versions of OLE DB providers. It may be used for most operations of database
creation and structure changing. One of its important advantages is
provider-independent interface, you can change connection string to change
database type from Access to MS SQL or Oracle. Sometimes you need to change
provider-specific properties but this way is more easy and faster than to change
a lot of queries text and source code. For some cases you can create your own
class wrappers over ADOX in addition to standard wrappers created by #import directive.

As far as English is not my native language, excuse me
for ugly language strustures.

Next article we’ll discuss ADOX compatibilities for Jet
and OLEDB providers.

If you have questions or some comments please send me email. Best regards.

More by Author

Get the Free Newsletter!

Subscribe to Data Insider for top news, trends & analysis

Must Read