About ADOX Related Knowledge _ database other

Turn from: http://blog.junstudio.org/article.asp?id=262

I. Overview of ADOX
Microsoft? Activex? The data Objects Extensions for data Definition Language and Security (ADOX) is an extension of the ADO object and the programming model. ADOX includes objects for schema creation and modification, as well as security. Because it is an object-based implementation mode operation, users can write code that works well for a variety of data sources, regardless of the differences in their original syntax.

ADOX is an extension library of core ADO objects. Other objects that it exposes can be used to create, modify, and delete schema objects, such as tables and procedures. It also includes security objects that can be used to maintain users and groups, as well as permissions to grant and revoke objects.

To use ADOX with development tools, you need to establish a reference to the ADOX type library. The description for the ADOX library is "Microsoft ADO Ext. For DDL and security." The ADOX library file name is "Msadox.dll" and the program ID (ProgID) is "ADOX". For more information about building library references, see the documentation for your development tools.
Second, the ADOX object
Catalog contains a collection that describes the data source schema directory.
column represents the columns of a table, index, or keyword.
Group accounts for groups that have access rights within the secure database.
Index represents the indexes in a database table.
Key represents a primary key, external keyword, or unique keyword in a database table.
Procedure represents a stored procedure.
Table represents database tables, including columns, indexes, and keywords.
User accounts that have access rights in the secure database.
View represents a filtered set of records or virtual tables.
Three, ADOX method
Append (Columns) adds a new Column object to the Columns collection.
Append (Groups) adds a new Group object to the Groups collection.
Append (Indexes) adds a new Index object to the Indexes collection.
Append (keys) adds a new Key object to the Keys collection.
Append (Procedures) adds a new Procedure object to the procedures collection.
Append (tables) adds a new Table object to the Tables collection.
Append (users) adds a new User object to the Users collection.
Append (views) adds a new View object to the Views collection.
ChangePassword change the password for the user account.
Create creates a new directory.
Delete Deletes the objects in the collection.
GetObjectOwner returns the owner of the object in the catalog.
GetPermissions Gets the permissions of the group or user on the object.
Item returns the specified member of the collection by name or ordinal.
Refresh updates the objects in the collection to reflect the available and specified objects for the provider.
SetObjectOwner the owner of the object in the specified directory.
SetPermissions sets the permissions on the group or user on the object.
Four, ADOX property
ActiveConnection indicates the ADO Connection object to which the directory belongs.
Attributes describes column attributes.
Clustered Indicates whether the index is clustered.
The command specifies the ADO Command object that can be used to create or execute procedures.
Count indicates the number of objects in the collection.
DateCreated indicates the date the object was created.
DateModified indicates the date on which the object was last changed.
DefinedSize indicates the specified maximum size of the column.
DeleteRule the action that will be performed when the primary key is deleted.
IndexNulls indicates whether a record with a Null value in the indexed field has an index entry.
Name indicates the names of the objects.
NumericScale indicates the range of values in the column.
PARENTCATALOG Specifies the parent directory of a table or column to access the properties of a specific provider.
Precision indicates the highest precision of the data values in the column.
PrimaryKey Indicates whether the index represents the primary key of the table.
RelatedColumn indicates the name of the related column in the related table (the Keyword column only).
RelatedTable indicates the name of the related table.
SortOrder indicates the sort order of the columns (indexed columns only).
Type (column) indicates the data type of the column.
Type (keyword) indicates the data type of the keyword.
Type (table) indicates the types of the table.
Unique indicates whether the index keyword must be unique.
UpdateRule the action that is performed when the primary key is updated.

V. Examples
5.1 Creating a database example
The following code shows how to create a new Jet database by using the Create method.
Sub CreateDatabase ()

Dim Cat as New ADOX. Catalog
Cat. Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\new.mdb"

End Sub
5.2 Creating a table example
Sub createtable ()

Dim TBL as New Table
Dim Cat as New ADOX. Catalog

' Open the directory.
' Open the directory.
Cat. ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data source=c:\program Files\Microsoft Office\" & _
"Office\samples\northwind.mdb;"

Tbl. Name = "MyTable"
Tbl. Columns.Append "Column1", adinteger
Tbl. Columns.Append "Column2", adinteger
Tbl. Columns.Append "Column3", adVarWChar, 50
Cat. Tables.append TBL

End Sub
5.3 Creating an Index sample
The following code shows how to create a new index. Indexes are established for two columns of a table.
Sub CreateIndex ()

Dim TBL as New Table
Dim idx as New ADOX. Index
Dim Cat as New ADOX. Catalog

' Open the directory.
' Open the directory.
Cat. ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data source=c:\program Files\Microsoft Office\" & _
"Office\samples\northwind.mdb;"

' Define the table and append it to the directory
Tbl. Name = "MyTable"
Tbl. Columns.Append "Column1", adinteger
Tbl. Columns.Append "Column2", adinteger
Tbl. Columns.Append "Column3", adVarWChar, 50
Cat. Tables.append TBL

' Define multiple-column indexes
Idx. Name = "Multicolidx"
Idx. Columns.Append "Column1"
Idx. Columns.Append "Column2"

' Append the index to the table
Tbl. Indexes.append IDX

End Sub
5.4 Creating a keyword Example
The following code shows how to create a new external keyword. It is assumed that two tables (Customers and orders) already exist.
Sub CreateKey ()

Dim kyforeign as New ADOX. Key
Dim Cat as New ADOX. Catalog

Cat. ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data source=c:\program Files\Microsoft Office\" & _
"Office\samples\northwind.mdb;"

Kyforeign.name = "CustOrder"
Kyforeign.type = Adkeyforeign
kyforeign.relatedtable = "Customers"
KyForeign.Columns.Append "CustomerId"
Kyforeign.columns ("CustomerId"). RelatedColumn = "CustomerId"
Kyforeign.updaterule = Adricascade
Cat. Tables ("Orders"). Keys.append kyforeign
End Sub