"Microsoft ADO Extensions for DDL and Security" is a COM class library which provides classes which complement the classes provided in ADO. The COM name for this library is ADOX.
In order to use ADOX types within a Visual Basic 6.0 project, it is recommended that a reference to "Microsoft ADO Ext. 2.8 for DDL and Security" be added to the project.
As at this writing, Version 2.8 is the most recently available version of ADO and ADOX, and is the version used in the supplied examples. However, Unity components are not linked to any specific version of ADO or ADOX, and most of the examples will work correctly with earlier versions of ADO and ADOX.
To add a reference to a Visual Basic 6.0 project, open the "Project" menu from the menu bar and choose "References". In the subsequent dialog, choose the required reference(s) and click OK.
Adding the following line to an asp file will make ADO constants available to asp code in the remainder of the file:
<!--METADATA TYPE="TypeLib" uuid={00000600-0000-0010-8000-00AA006D2EA4} -->
There is some apparent overlap in functionality between ADOX and UMD as both libraries expose classes which relate to the structure of relational databases.
The corresondence between ADOX and UMD classes is summarized in the following table:
| ADOX | UMD |
|---|---|
| Catalog | Schema |
| Tables | TableDefinitions |
| Table | TableDefinition |
| Columns | FieldDefinitions |
| Column | FieldDefinition |
| Indexes | n/a |
| Index | n/a |
The primary difference between the ADOX Catalog class and the UMD Schema class, is that an ADOX Catalog must be associated with an ADO connection to a physical database, whereas a UMD Schema can be initialized and manipulated in memory, without connecting to a database.
The structure of a physical database can be manipulated directly by manipulating Table and Column objects which belong to an ADOX Catalog associated with an ADO connection to that database.
In contrast, UMD Schema objects are connectionless. Manipulating TableDefinition and FieldDefinition objects belonging to a UMD Schema produces no immediate effect on any physical database.
UMD TableDefinition and FieldDefinition objects support automatic creation of application-specific properties at runtime.
In VbScript, JScript, Visual Basic 6, and VB.Net with Option Strict Off, referencing a property of a TableDefinition or FieldDefinition object causes it to exist, if it does not already exist.
It is therefore valid to write
MySchema.TableDefinitions("tblCustomer").IncludeInLocalCache = True
even though there is no pre-defined property named "IncludeInLocalCache". The above line causes the property to exist, and it can be later examined thus:
Public Sub PopulateLocalCache
Dim td As TableDefinition
Dim qry As IQuery
For Each td In MySchema.TableDefinitions
If td.IncludeInLocalCache Then
Set qry = MyQueryFactory.MakeQuery(td)
' ... more code here to fetch data from the server and save in the local cache.
End If
Next td
End Sub
ADOX also supports application-specific properties through the "Properties" property of Table and Column objects. Whereas automatically generated properties of TableDefinition and FieldDefinition objects exist only in memory, application-specific properties of Table and Column objects are stored in the database.
Creating a New Database
The connectionless nature of UMD means that UMD Schemas have no built-in means of creating a database.
A database can be created from a UMD Schema by iterating it's TableDefinitions collection and generating (and executing) SQL "CREATE TABLE" statements from each TableDefinition, or by iterating the TableDefinitions collection and instantiating an ADOX Table object for each TableDefinition and appending a Column to the Table object for each of the TableDefinition's FieldDefinitions.
In either case, the basic structure of the code is
Public Sub ImplementSchemaInCatalog(ByVal sch As Schema, ByVal cat As Catalog)
Dim td As TableDefinition
For Each td In sch.TableDefinitions
' ... more code here to create a new ADOX Table object from td.
Next td
End Sub
Unity does not supply any built-in method for generating SQL "CREATE TABLE" statements from a TableDefinition object.
However, the examples include source code for generating ADOX Tables from UMD TableDefinitions using the following programming languages:
VB.Net
Visual Basic 6.0
SqlQueryBuilders are not tightly linked to any specific data access technology, and can therefore be used to build SQL query strings for any data access layer that accepts SQL, including ADO.
In order to use ADO types within a Visual Basic 6.0 project, it is recommended that a reference to "Microsoft ActiveX Data Objects 2.8 Library" be added to the project.
As at this writing, this is the most recently available version of ADO, and is the version used in the supplied examples. However, SqlQueryBuilders are not linked to any specific version of ADO, and most of the examples will work correctly with earlier versions of ADO.
To add a reference from the Visual Basic 6.0