What is ADOX?

• ADOX stands for ActiveX Data Objects Extensions for Data Definition Language (DDL) and Security.
• ADOX is an extension to ADO and a companion library for Microsoft ADO.
• ADO is provided as a DLL, and installed at "c:\program files\common files\system\ado\msadox.dll".
• ADOX is a COM library. It can be used in any language and system that supports COM technology.
• ADOX object collections create and delete database objects.

In my previous module, you have learned how to display data from a database, and how to edit, add, delete and manipulate the data with ADO. However, there are limitations to things you can do with ADO. For example, ADO can only work with objects that are already exist in the database. A simple case would be that you can't create a new table, a new query or Access databases with ADO, no matter what you do with the Connection, Command, or Recoredset objects. There were also security issues in the beginning of ADO.

ADOX is a companion library to the core ADO objects. It exposes additional objects for creating, modifying, and deleting schema objects, such as tables and procedures. It also includes security objects to maintain users and groups and to grant and revoke permissions on objects. With ADOX, you are dealing with database's schema rather than its data.

You can create Access databases by using the Microsoft Jet OLE DB Provider and Microsoft ADOX. Before using ADOX, you must reference it in your Microsoft Visual Basic project. In the Add References dialog, locate and click the check box "Microsoft ADO Ext. 2.7 or 2.8 for DDL and Security" [see diagram]. ADOX also allows you to manipulate the security properties of objects in your database, such as who owns an object and who has permission to work with the object.

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. 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 Objects Catalog Object Column Object Group Object Index Object Key Object Procedure Object Property Object Table Object User Object View Object ADOX Collections Columns Collection Groups Collection Indexes Collection Keys Collection Procedures Collection Tables Collection Users Collection Views Collection

ADOX Properties ActiveConnection Property Attributes Property Clustered Property Command Property DateCreated Property DateModified Property DefinedSize Property DeleteRule Property IndexNulls Property Name Property NumericScale Property ParentCatalog Property Precision Property PrimaryKey Property RelatedColumn Property RelatedTable Property SortOrder Property Type Property (Column) Type Property (Key) Type Property (Table) Unique Property UpdateRule Property

ADOX Methods Append Method (Columns) Append Method (Groups) Append Method (Indexes) Append Method (Keys) Append Method (Procedures) Append Method (Tables) Append Method (Users) Append Method (Views) ChangePassword Method Create Method Delete Method (Collections) GetObjectOwner Method GetPermissions Method SetObjectOwner Method SetPermissions Method

ADOX Enumerated Constants AllowNullsEnum ActionEnum ColumnAttributesEnum InheritTypeEnum KeyTypeEnum ObjectTypeEnum RightsEnum RuleEnum SortOrderEnum

The ADOX (for DDL and Security) object model observes more hierarchy than does the ADO object model as you can see in figures below.

		The ADO Objects and Collections - The Connection, Command, Recordset, and Field objects also have a Properties collection
	The ADOX Objects and Collections. Each of the Table, Index, and Column objects also has a standard ADO Properties collection.

Go To Top

Catalog Object (ADOX)

It is the first object you need to use in your ADOX program. It contains collections (Tables, Views, Users, Groups, and Procedures) that describe the schema catalog of a data source.

You can modify the Catalog object by adding or removing objects or by modifying existing objects. Some providers may not support all of the Catalog objects or may support only viewing schema information.

With the properties and methods of a Catalog object, you can:
 ● Open the catalog by setting the ActiveConnection property to an ADO Connection object or a valid connection string.
 ● Create a new catalog with the Create method.
 ● Determine the owners of the objects in a Catalog with the GetObjectOwner and SetObjectOwner methods.

Setting the ActiveConnection property to a valid, open connection "opens" the catalog. From an open catalog, you can access the schema objects contained within that catalog.

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 Properties/Collections :
ActiveConnection Property
Groups Collection
Procedures Collection
Tables Collection
Users Collection
Views Collection


Methods :
Create Method
GetObjectOwner Method
SetObjectOwner Method

Example:

' Purpose: to open the catalog to create a database using ADOX
Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog

' Connect the catalog.
Set cnn = CurrentProject.Connection
Set cat.ActiveConnection = cnn   ' Open the Catalog
MsgBox "ADOX catalog is now open"

cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
   "Data Source='C:\CreatedNEW.mdb'"   ' This creates a new database
MsgBox "A new Microsoft JET database named CreatedNEW.mdb has been created"

Set cnn = Nothing

Go To Top

Column Object (ADOX)

Column may be a part of Keys or Indexes Collections in ADOX object model. Table Column object differs from key Column and Index Column although they all have same properties (except provider-specific).

With the properties and collections of a Column object, you can:
 ● Identify the column with the Name Property (ADOX) property.
 ● Specify the data type of the column with the Type Property (Key) (ADOX) property.
 ● Determine if the column is fixed-length, or if it can contain null values with the Attributes Property (ADOX) property.
 ● Specify the maximum size of the column with the DefinedSize Property (ADOX) property.
 ● For numeric data values, specify the scale with the NumericScale Property (ADOX) property.
 ● For numeric data value, specify the maximum precision with the Precision Property (ADOX) property.
 ● Specify the Catalog Object (ADOX) that owns the column with the ParentCatalog Property (ADOX) property.
 ● For key columns, specify the name of the related column in the related table with the RelatedColumn Property (ADOX) property.
 ● For index columns, specify whether the sort order is ascending or descending with the SortOrder Property (ADOX) property.
 ● Access provider-specific properties with the Properties Collection (ADO) collection.

Note:

Example:

Dim tbl As New Table
Dim cat As New ADOX.Catalog
' Purpose: to open the catalog, create a new table and add some columns using ADOX
cat.ActiveConnection = "Provider='Microsoft.Jet.OLEDB.4.0';" & _
   "Data Source='C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';"

' This creates the table called "MyTestTable"
tbl.Name = "MyTestTable"
' Add 3 columns, Append field names and data types to "MyTestTable" Columns Collection
tbl.Columns.Append "EmployeeID", adInteger
tbl.Columns.Append "EmployeeName", adVarWChar, 30
tbl.Columns.Append "DateJoined", adDate, Date
' Append the newly created table to the Tables Collection
cat.Tables.Append tbl
Debug.Print "Table 'MyTestTable' is added."

' Delete the table "MyTestTable" with this code
'cat.Tables.Delete tbl.Name

' Clean up objects
Set cat.ActiveConnection = Nothing
Set cat = Nothing
Set tbl = Nothing

Example:

' Purpose: Create a new table and columns, and add multi-fields Primary Key using ADOX
Dim cnn As ADODB.Connection, cat As ADOX.Catalog, tbl As ADOX.Table, ky As ADOX.Key

Set cnn = New ADODB.Connection
Set cat = New ADOX.Catalog
Set tbl = New ADOX.Table
Set ky = New ADOX.Key

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb;"
Set cat.ActiveConnection = cnn   'Open the Catalog
tbl.Name = "MyTestTable"  'Catalogis creates the table called 'MyTestTable'
'Add 2 columns, Append field names and data types to Columns Collection of 'MyTestTable'
tbl.Columns.Append "MyPrimaryKey_Field1", adInteger
tbl.Columns.Append "MyPrimaryKey_Field2", adInteger

ky.Name = "PrimaryKey"  'create Primary keys
ky.Type = adKeyPrimary
'Append 2 Primary keys to the Columns Collection of Key
ky.Columns.Append "MyPrimaryKey_Field1"
ky.Columns.Append "MyPrimaryKey_Field2"

tbl.Keys.Append ky  'Append new Key to the Keys Collection of primary key table 'MyTestTable'
cat.Tables.Append tbl  'Append the newly created table to the Tables Collection

'clean up objects
Set ky = Nothing
Set tbl = Nothing
Set cat = Nothing
cnn.Close
Set cnn = Nothing

ADOX library defines special data type called DataTypeEnum. This type is subset of integer values of Variant variable type (integer value which Visual Basic function VarType returns). See table below.

 

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

 

Go To Top

Group Object (ADOX)

Represents a group account that has access permissions within a secured database. It contains a list of users in the catalog. Its main role is to allow the retrieval and setting of permission for a named group or for allowing access to the Users collection, which contains a list of all users belonging to this group.

The Groups collection contains a list of Groups that belong to a particular catalog, or a list of Groups that a particular user belongs to.

With the properties, collections, and methods of a Group object, you can:
 ● Identify the group with the Name property.
 ● Determine whether a group has read, write, or delete permissions with the GetPermissions and SetPermissions methods.
 ● Access the user accounts that have memberships in the group with the Users collection.
 ● Access provider-specific properties with the Properties collection.

Group Properties/Collections :
Name Property
Properties Collection
Users Collection

Methods :
GetPermissions Method
SetPermissions Method

Example:

' Purpose: enumerates the Groups in a Catalog, and then the Users in a Group
Dim objGroup As ADOX.Group
Dim objUser As ADOX.User

For Each objGroup In objCatalog.Groups
   Debug.Print objGroup.Name
   For Each objUser In objGroup.Users
      Debug.Print objUser.Name
   Next
Next

Example:

' Purpose: demonstrates the GetPermissions and SetPermissions methods.
Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim lngPerms As Long

' Openning connection to the Northwind database
cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
cnn.Open "C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb"

Set cat.ActiveConnection = cnn

' Retrieve original permissions using this syntax:
'ReturnValue = GroupOrUser.GetPermissions(Name, ObjectType[,ObjectTypeId])
lngPerms = cat.Users("admin").GetPermissions("Orders", adPermObjTable)
Debug.Print "Original permissions: " & str(lngPerms)

' Revoke all permissions
cat.Users("admin").SetPermissions "Orders", adPermObjTable, adAccessRevoke, adRightFull

' Give the Admin user full rights on the orders object
cat.Users("admin").SetPermissions "Orders", adPermObjTable, adAccessSet, adRightFull

' Restore original permissions
cat.Users("admin").SetPermissions "Orders", adPermObjTable, adAccessSet, lngPerms

' Display permissions
Debug.Print "Final permissions: " & str(cat.Users("admin").GetPermissions("Orders", _
  adPermObjTable))

Go To Top

Index Object (ADOX)

Represents an index from a database table.

With the properties and collections of an Index object, you can:
 ● Identify the index with the Name property.
 ● Access the database columns of the index with the Columns collection.
 ● Specify whether the index keys must be unique with the Unique property.
 ● Specify whether the index is the primary key for a table with the PrimaryKey property.
 ● Specify whether records that have null values in their index fields have index entries with the IndexNulls property.
 ● Specify whether the index is clustered with the Clustered property.
 ● Access provider-specific index properties with the Properties collection.

Note:
An error will occur when appending a Column to the Columns collection of an Index if the Column does not exist in a Table object already appended to the Tables collection.

Index Properties/Collections :
Clustered Property
Columns Collection
IndexNulls Property
Name Property
PrimaryKey Property
Properties Collection
Unique Property

Example:
' Purpose: to create a new index on two columns in the table.
Dim tbl As New Table
Dim idx As New ADOX.Index
Dim cat As New ADOX.Catalog

' Connect to the catalog.
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection

' Define the table and append it to the catalog.
tbl.Name = "MyTestTable"
tbl.Columns.Append "MyColumn1", adInteger
tbl.Columns.Append "MyColumn2", adVarWChar, 30
cat.Tables.Append tbl 'MyTestTable is added to the Catalog

' Define a multi-column index.
idx.Name = "multiCol_Index"
idx.Columns.Append "MyColumn1"
idx.Columns.Append "MyColumn2"

' Append the index to the table 'MyTestTable'.
tbl.Indexes.Append idx

' This deletes the table 'MyTestTable'.
'cat.Tables.Delete tbl.Name

' Clean up objects
Set idx = Nothing
Set tbl = Nothing
Set cat = Nothing
Set cat.ActiveConnection = Nothing
Exit Sub

' Purpose: to create a new index on two columns in the table.
Dim idx As ADOX.Index, tbl As ADOX.Table

Set idx = New ADOX.Index
idx.Name = "tblPrimaryIndex"
idx.Clustered = True
idx.IndexNulls = adIndexNullsDisallow
idx.PrimaryKey = True
idx.Unique = True

idx.Columns.Append "MyColumn1"
tbl.Keys.Append idx
tbl.Indexes.Append "column2Index", "MyColumn2"

Go To Top

Key Object (ADOX)

Represents a primary, foreign, or unique key field from a database table. Key means something that identifies record. For example, table column has unique values in a table, and this column may be called as "primary" key. Key can also consist of some columns, but in most cases primary key is one column.

Records form one table may refer to another. In this case column, which refers to other table, is called "foreign key". Usually foreign key refers to primary key in another table. But sometimes it refers to column(s) that is not a primary key. Such column in referred table is usually called "unique" key.

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, and NULL values also are not allowed.

Type of columns used in relationship between tables must be same. For example, you cannot refer from Text column to Integer. You'll get error in such case. Also you cannot create reference from not nullable column to nullable. 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.

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".

With the properties and collections of a Key object, you can:
 ● Identify the key with the Name property.
 ● Determine whether the key is primary, foreign, or unique with the Type property.
 ● Access the database columns of the key with the Columns collection.
 ● Specify the name of the related table with the RelatedTable property.
 ● Determine the action performed on deletion or update of a primary key with the DeleteRule and UpdateRule properties.

Key Object Properties/Collections:
Columns Collection - Columns means database key columns.
DeleteRule Property - DeleteRule is action performed on delete primary key.
Name Property - Name is the key name.
RelatedTable Property - name of related table.
Type Property (Key) - has key type: primary, foreign or unique.
UpdateRule Property - UpdateRule is action performed on update primary key.

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. DeleteRule and UpdateRule properties are constants of type RuleEnum.

RuleEnum constants specify the rule to follow when a Key is deleted.

' Purpose: Create a foreign key between the Employees and Users tables
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim kyForeign As New ADOX.Key
Dim col As New ADOX.Column

' Connect to the catalog.
Set cat.ActiveConnection = CurrentProject.Connection

' Retrieve the foreign key table called "Employees".
Set tbl = cat.Tables("Employees")

' Create the foreign key and append it to the table "Employees".
kyForeign.Name = "EmployeeKey"
kyForeign.Type = adKeyForeign
kyForeign.RelatedTable = "Users"
kyForeign.DeleteRule = adRICascade
kyForeign.UpdateRule = adRICascade

' Add a secondary table's column "EmployeeID" to foreign key
col.Name = "EmployeeID"  ' create name of column in the foreign key table
col.RelatedColumn = "EmployeeID"  ' name of column in the primary key table
kyForeign.Columns.Append col
tbl.Keys.Append kyForeign   ' append key to the keys collection of the foreign key table

' Clean up objects
Set cat.ActiveConnection = Nothing
Set cat = Nothing
Set kyForeign = Nothing

Go To Top

Procedure Object (ADOX)

Represents a stored procedure. When used in conjunction with the ADO Command object, the Procedure object can be used for adding, deleting, or modifying stored procedures. In Access you can create database queries (Insert Into, Select Into, Alter Table) and they are called stored procedure. The Procedure object allows you to create a stored procedure without having to use the provider's "CREATE PROCEDURE" syntax.

The Procedure object and the View object are similar. They represent a stored procedure and a view in the database, respectively. They also expose the same set of four properties.

With the properties of a Procedure object, you can:
 ● Identify the procedure with the Name property.
 ● Specify the ADO Command object that can be used to create or execute the procedure with the Command property.
 ● Return date information with the DateCreated and DateModified properties.

Procedure Properties:
Command Property
DateCreated Property
DateModified Property
Name Property

Example:

' Purpose: To create the new procedure.
Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim prm As ADODB.Parameter

' Open the Connection
cnn.Open _
"Provider='Microsoft.Jet.OLEDB.4.0'; Data Source=D:\MyData\Northwind.mdb;"

' Open the Catalog
Set cat.ActiveConnection = cnn

' Create the parameterized command text (Microsoft Jet specific)
Set cmd.ActiveConnection = cnn
cmd.CommandText = "PARAMETERS [CustID] Text;" & _
"SELECT * FROM Customers WHERE CustomerID = [CustID]"

' Create the new Procedure
cat.Procedures.Append "CustomerByID", cmd
' cat.Procedures.Delete "CustomerByID"    ' Delete the procedure.
' cat.Procedures.Refresh "CustomerByID"   ' Refresh the Procedures collection

' Clean up objects
cnn.Close
Set cat = Nothing
Set cmd = Nothing
Set cnn = Nothing

Example:

' Purpose: To use the Command property to update the text of a procedure.

' Get the command.
Set cmd = cat.Procedures("CustomerByID").Command
' Update the CommandText.
cmd.CommandText = "SELECT * FROM Customers WHERE CustomerID = 'ANTON'"
' Update the procedure.
Set cat.Procedures("CustomerByID").Command = cmd

Go To Top

Property Object (ADOX)
Represents a characteristic of an ADOX object. The Property object and Properties collection are identical to their ADODB equivalents.

ADOX objects have two types of properties: built-in and dynamic. Built-in properties are those properties immediately available to any new object, using the MyObject.Property syntax. They do not appear as Property objects in an object's Properties collection, so although you can change their values, you cannot modify their characteristics.

Dynamic properties are defined by the underlying data provider, and appear in the Properties collection for the appropriate ADOX object. Dynamic properties can be referenced only through the collection, using the MyObject.Properties(0) or MyObject.Properties("Name") syntax. You cannot delete either kind of property.

Dynamic Property Object Properties:
Name Property - is a string that identifies the property.
Attributes Property - is a long value that indicates characteristics of the property specific to the provider.
Type Property - is an integer that specifies the property data type.
Value Property - is a variant that contains the property setting. Value is the default property for a Property object.

Example:

' Purpose: To update Attributes property of a Column.
' Setting Attributes property to adColNullable allows the user to set the value of a Recordset Field to an empty string.
Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim col As New ADOX.Column
Dim rst As New ADODB.Recordset
Dim tbl As ADOX.Table
Dim strSql As String

' Connect the catalog.
Set cnn = CurrentProject.Connection
Set cat.ActiveConnection = cnn

Set tbl = cat.Tables("Employees")

' Create a new Field object and append it to the Fields collection of the Employees table.
col.Name = "Phone"
col.Type = adInteger
col.DefinedSize = 24
col.Attributes = adColNullable
cat.Tables("Employees").Columns.Append col.Name
' or alternatively,   cat.Tables("Employees").Columns.Append col.Name, adVarWChar, 24

' Open the Employees table for updating as a Recordset
rst.Open "Employees", cnn, adOpenDynamic, adLockOptimistic, adCmdTable
strSql = "INSERT INTO Employees (Phone) VALUES ('1118168');"

cnn.Execute strSql      ' execute the SQL string
'tbl.Columns.Delete col.Name      ' to delete the Field object 'FaxPhone'

Go To Top

Table Object (ADOX)
Represents a database table including columns, indexes, and keys.

With the properties and collections of a Table object, you can:

 ● Identify the table with the Name Property (ADOX).
 ● Determine the type of table with the Type Property (Table) (ADOX).
 ● Access the database columns of the table with the Columns Collection (ADOX).
 ● Access the indexes of the table with the Indexes Collection (ADOX).
 ● Access the keys of the table with the Keys Collection (ADOX).
 ● Specify the Catalog that owns the table with the ParentCatalog Property (ADOX).
 ● Return date information with the DateCreated Property (ADOX) and DateModified Property (ADOX).
 ● Access provider-specific table properties with the Properties Collection (ADO).

Note:
Table object has provider-specific collection Properties. An error will occur if you have set a value for a Table object property that the data provider does not support.

Set property ParentCatalog to existing Catalog object already connected to Microsoft Jet database. ADOX will get provider type from Connection property of Catalog object. If you do not set ParentCatalog property, Properties collection is empty.

Example:

' Purpose: Use the ParentCatalog property to access a provider-specific property prior to appending a table to a catalog.
Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
' Connect the catalog.
cnn.Open "Provider='Microsoft.Jet.OLEDB.4.0';Data Source=D:\MyData\Northwind.mdb;"
Set cat.ActiveConnection = cnn

With tbl
  .Name = "tbl_MyCustomers"
  Set .ParentCatalog = cat  ' Or after the new column is appended, using .Columns("ContactID").ParentCatalog = cat
    ' Create fields and append them to the new Table object.
    .Columns.Append "ContactID", adInteger
    ' Make the ContactID column and 'auto-incrementing'.
  .Columns("ContactId").Properties("AutoIncrement") = True
  .Columns.Append "CustomerID", adVarWChar
  .Columns.Append "CustomerName", adVarWChar
  .Columns.Append "CustomerAddress", adLongVarWChar
  .Columns.Append "Phone", adVarWChar, 15
End With

cat.Tables.Append tbl
' cat.Tables.Delete tbl.Name    ' Delete the table 'MyContacts' with this code

' Clean up objects
cnn.Close
Set cat = Nothing
Set tbl = Nothing
Set cnn = Nothing

Go To Top

User Object (ADOX)
Represents a user account that has access permissions within a secured database.

The Users collection of a Catalog represents all the catalog's users. The Users collection for a Group represents only the users of the specific group. The Group and User Objects are tightly connected to each other in that each User object exposes a Groups collection (all the groups the user belongs to), and each Group object exposes a Users collection (all the users that belong to that group).

With the properties, collections, and methods of a User object, you can:
 ● Identify the user with the Name property.
 ● Change the password for a user with the ChangePassword method.
 ● Determine whether a user has read, write, or delete permissions with the GetPermissions and SetPermissions methods.
 ● Access the groups to which a user belongs with the Groups collection.
 ● Access provider-specific properties with the Properties collection.
 ● Determine the ParentCatalog for a user.

User Properties/Collections:
 Name Property
 ParentCatalog Property (ADOX)
 Groups Collection
 Properties Collection

User Methods:
 ChangePassword Method
 GetPermissions Method
 SetPermissions Method

Example:

Sub CreateGroupAndUsers()
' Purpose: Create a new security group, add users and passwords, update access permissions.
Dim cat As New ADOX.Catalog
Dim strOwner As String

' Connect the catalog pointing to this database
Set cat.ActiveConnection = CurrentProject.Connection

' Add a group named 'Planning'  [see diagram]
cat.Groups.Append "Planning"

' Add the Admin in this group
cat.Groups("Planning").Users.Append "Admin"

' Add a User named 'William' and password 'kuching'
cat.Users.Append "William", "kuching"

' Add the user in the Planning group [see diagram]
cat.Users("William").Groups.Append "Planning"

' Change user's password
cat.Users("William").ChangePassword "Kuching", "Cat"

' Show the current owner of the 'Orders' table [see diagram]
strOwner = cat.GetObjectOwner("Orders", adPermObjTable)
Debug.Print "Orders table is owned by " & strOwner

' Display permissions of current owner of the 'Orders' table. Refer to RightsEnum Constants.
Debug.Print "Full permissions: " & _
str(cat.Users("admin").GetPermissions("Orders", adPermObjTable))

' Change the ownership  [see diagram]
cat.SetObjectOwner "Orders", adPermObjTable, "William"
Debug.Print "Orders table is now owned by William"

' Grant user full access right permissions on the 'Orders' table  [see diagram]
cat.Users("William").SetPermissions "Orders", adPermObjTable, adAccessSet, adRightFull

' these 2 lines are to delete user's account and group 'Planning'
cat.Users.Delete ("William")
cat.Groups.Delete ("Planning")

' Or alternatively, to revoke only all permissions to the user
'cat.Users("William").SetPermissions "Orders", adPermObjTable, adAccessRevoke, adRightFull

' Set ownership back to Admin with full access permissions
cat.SetObjectOwner "Orders", adPermObjTable, "Admin"
cat.Users("Admin").SetPermissions "Orders", adPermObjTable, adAccessSet, adRightFull
End Sub

Go To Top

View Object (ADOX)
Represents a filtered set of records or a virtual table. When used in conjunction with the ADO Command object, the View object can be used for adding, deleting, or modifying views. The View object and the Procedure object are similar. They represent a view and a stored procedure in the database, respectively. They also expose the same set of four properties. The Command property returns a reference to an ADODB.Command object that can also execute the stored procedure.

With the properties of a View object, you can:
 ● Identify the view with the Name property.
 ● Specify the ADO Command object that can be used to add, delete, or modify views with the Command property.
 ● Return date information with the DateCreated and DateModified properties.

View Object Properties:
 Command Property
 DateCreated Property
 DateModified Property
 Name Property

Example:

Dim cmd As New ADODB.Command
Dim cat As New ADOX.Catalog
Dim objView As ADOX.View

' Open the Catalog
cat.ActiveConnection = "Provider='Microsoft.Jet.OLEDB.4.0';" & _
   "Data Source=D:\MyData\Northwind.mdb;"

' Create the command representing the view
cmd.CommandText = "Select * From Customers"

' Create the new View and new query 'qry_AllCustomers'
cat.Views.Append "qry_AllCustomers", cmd

' To obtain all views in the catalog
For Each objView In cat.Views
   Debug.Print objView.Name
Next

' To delete the View
'cat.Views.Delete "qry_AllCustomers"

Example:

' Purpose: use ADO Command property to execute a stored procedure or query to view the underlying SQL command
Dim cmd As ADODB.Command, cat As New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("qry_CustomersView").Command
MsgBox cmd.CommandText

Example:

' Purpose: use DateCreated and DateModified properties to obtain the 2 dates of a table
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Set cat = New ADOX.Catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=D:\MyData\Northwind.mdb;"
Set tbl = cat.Tables("Customers")
MsgBox "DateCreated = " & tbl.DateCreated & vbCrLf _
  & "DateModified = " & tbl.DateModified

Go To Top

ADOX Collections

Columns Collection (ADOX)
Contains all Column objects of a table, index, or key.

The Append method for a Columns collection is unique for ADOX. You can:
 ● Add a new column to the collection with the Append method (ADOX).

The remaining properties and methods are standard to ADO collections. You can:
 ● Access a column in the collection with the Item property (ADO).
 ● Return the number of columns contained in the collection with the Count property (ADO).
 ● Remove a column from the collection with the Delete method (ADO).
 ● Update the objects in the collection to reflect the current database's schema with the Refresh method (ADO).

Note:
An error will occur when appending a Column to the Columns collection of an Index if the Column does not exist in a Table that is already appended to the Tables collection.

Properties:
Count Property
Item Property

Methods:
Append Method (Columns)  [see example]
Delete Method (Collections) [see example1  example2  example3]
Refresh Method

Go To Top

Groups Collection (ADOX)
Contains all stored Group objects of a catalog or user.

The Groups collection of a Catalog represents all of the catalog's group accounts. The Groups collection for a User represents only the group to which the user belongs.

The Append method for a Groups collection is unique for ADOX. You can:
 ● Add a new security group to the collection with the Append method.

Go To Top

Indexes Collection (ADOX)
Contains all Index objects of a table.

The Append method for an Indexes collection is unique for ADOX. You can:
 ● Add a new index to the collection with the Append method.

Go To Top

Keys Collection (ADOX)
Contains all Key objects of a table.

The Append method for a Keys collection is unique for ADOX. You can:
 ● Add a new key to the collection with the Append method.

Go To Top

Procedures Collection (ADOX)
Contains all Procedure objects of a catalog.

The Append method for a Procedures collection is unique for ADOX. You can:
 ● Add a new procedure to the collection with the Append method.

The remaining properties and methods are standard to ADO collections. You can:
 ● Access a column in the collection with the Item property (ADO).
 ● Return the number of columns contained in the collection with the Count property (ADO).
 ● Remove a column from the collection with the Delete method (ADO).
 ● Update the objects in the collection to reflect the current database's schema with the Refresh method (ADO).

Properties:
Count Property
Item Property

Methods:
Append Method (Indexes)  [see example]
Delete Method (Collections) [see example1  example2  example3]
Refresh Method

Go To Top

Tables Collection (ADOX)
Contains all Table objects of a catalog.

The Append method for a Tables collection is unique for ADOX. You can:
 ● Add a new table to the collection with the Append method.

Note:
Some providers may return other schema objects, such as a view, in the Tables collection. Therefore, some ADOX collections may contain multiple references to the same object. Should you delete the object from one collection, the change will not be visible in another collection that references the deleted object until the Refresh method is called on the collection. For example, with the OLE DB Provider for Microsoft Jet, views are returned with the Tables collection. If you drop a view, you must refresh the Tables collection before the collection will reflect the change.

The remaining properties and methods are standard to ADO collections.

Go To Top

Users Collection (ADOX)
Contains all stored User objects of a catalog or group.

The Users collection of a Catalog represents all the catalog's users. The Users collection for a Group represents only the users that have a membership in the specific group.

The Append method for a Users collection is unique for ADOX. You can:
 ● Add a new user to the collection by using the Append method.

The remaining properties and methods are standard to ADO collections.

Properties:
Count Property
Item Property

Methods:
Append Method (Users)  [see example]
Delete Method (Collections) [see example1  example2  example3]
Refresh Method

Go To Top

Views Collection (ADOX)
Contains all View objects of a catalog.

The Append method for a Views collection is unique for ADOX. You can:
 ● Add a new view to the collection with the Append method.

The remaining properties and methods are standard to ADO collections.

ActiveConnection Property (ADOX)
It indicates the ADO Connection object to which the Catalog belongs. It returns the active Connection object.

Setting the ActiveConnection property to a valid, open connection "opens" the catalog. From an open catalog, you can access the schema objects contained within that catalog.

Applies To:
Catalog Object (ADOX)

Example:

cnn.Open "Provider='Microsoft.Jet.OLEDB.4.0';" & _
"Data Source=C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb;"
Set cat.ActiveConnection = cnn   'Open the Catalog

Attributes Property (ADOX)
It sets or returns a Long value. The value specifies characteristics of the table that is represented by the Column object. The value can be a combination of ColumnAttributesEnum constants. The default value is zero (0), which is neither adColFixed nor adColNullable.

Applies To:
Column Object (ADOX)

Example:

ObjColumn.Attributes = adColNullable

[See this example]

Go To Top

Clustered Property (ADOX)
Indicates whether the index is clustered. This means that whether records in table has same physical sort order as index, i.e. records stored in a database in the order defined by index. You can create only one clustered index per table.

It sets and returns a Boolean value. The default value is False. This property is read-only on Index objects that have already been appended to a collection.

Applies To:
Index Object (ADOX)

Example:

Dim idx As ADOX.Index
Set idx = New ADOX.Index

idx.name = "tblPrimaryIndex"
idx.Clustered = True
idx.IndexNulls = adIndexNullsDisallow
idx.PrimaryKey = True
idx.Unique = True

Command Property (ADOX)
Specifies an ADO Command object that can be used to create or execute the procedure. It Sets or returns a valid ADO Command object.

Applies To:
Procedure Object (ADOX)
View Object (ADOX)

Example:

Dim cnn As New ADODB.Connection, cat As New ADOX.Catalog, cmd As New ADODB.Command
cnn.Open "Provider='Microsoft.Jet.OLEDB.4.0'; Data Source=D:\MyData\Northwind.mdb;"
Set cat.ActiveConnection = cnn
' Get the command
Set cmd = cat.Procedures("CustomerByID").Command
' Update the CommandText
cmd.CommandText = "SELECT * FROM Customers WHERE CustomerID = 'ANTON'"
' Update the procedure
Set cat.Procedures("CustomerByID").Command = cmd
' Create another command
cmd.CommandText = "SELECT CompanyName FROM Customers WHERE city = 'Barcelona'"
' Create the new View and new query 'qry_AllCustomers'
cat.Views.Append "qry_CustomersByCity", cmd

Go To Top

DateCreated Property (ADOX)
Returns a Variant value specifying the date created. The value is null if DateCreated is not supported by the provider.

The DateCreated property is null for newly appended objects. After appending a new View or Procedure, you must call the Refresh method of the Views or Procedures collection to obtain values for the DateCreated property.

Applies To:
Procedure Object (ADOX)
Table Object (ADOX)
View Object (ADOX)

Example:

Set ObjTable = ObjCatalog.Tables("Customers")
Debug.Print "Date created: " & ObjTable.DateCreated
 

DateModified Property (ADOX)
Indicates the date the object was last modified. The DateModified property is null for newly appended objects. After appending a new View or Procedure, you must call the Refresh method of the Views or Procedures collection to obtain values for the DateModified property.

Applies To:
Procedure Object (ADOX)
Table Object (ADOX)
View Object (ADOX)

Example:

Set ObjTable = ObjCatalog.Tables("Customers")
Debug.Print "Date modified: " & ObjTable.DateModified

Go To Top

DefinedSize Property (ADOX)
Indicates the stated maximum size of the column. It sets and returns a Long value that is the maximum length in characters of data values.

Applies To:
Column Object (ADOX)

Example:

Set ObjTable = ObjCatalog.Tables("Employees")
' Create a new Field object and append it to the Fields collection of the Employees table.
With ObjColumn
  .Name = "Phone"
  .DefinedSize = 24
  .Attributes = adColNullable
End with
ObjCatalog.Tables("Employees").Columns.Append ObjColumn.Name, adVarWChar

Go To Top

DeleteRule Property (ADOX)
Indicates the action performed when a primary key is deleted. It sets and returns a Long value that can be one of the RuleEnum constants. The default value is adRINone. This property is read-only on Key objects already appended to a collection.

Applies To:
Key Object (ADOX)

[See this example]

IndexNulls Property (ADOX)
Indicates whether records that have null values in their index fields have index entries. It sets and returns an AllowNullsEnum value. The default value is adIndexNullsDisallow. This property is read-only on Index objects already appended to a collection.

Applies To:
Index Object (ADOX)

[See this example]

Go To Top

Name Property (ADOX)
Indicates the name of the object and it sets or returns a String value.

The Name property is read/write on Column, Group, Key, Index, Table, and User objects. The Name property is read-only on Catalog, Procedure, and View objects.

For read/write objects (Column, Group, Key, Index, Table and User objects), the default value is an empty string ("").

Applies To:
Column Object (ADOX)
Group Object (ADOX)
Index Object (ADOX)
Key Object (ADOX)
Procedure Object (ADOX)
Property Object (ADO)
Table Object (ADOX)
User Object (ADOX)
View Object (ADOX)

Example:

Sub NamesPropertiesExample()
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
Dim col As New ADOX.Column
Dim ky As New ADOX.Key
Dim kyForeign As New ADOX.Key
Dim cmd As New ADODB.Command
Dim myView As ADOX.View
Dim strSql As String
Dim grp As ADOX.Group
Dim usr As ADOX.User

cat.ActiveConnection = CurrentProject.Connection

tbl.Name = "MyTestTable"   ' create the table 'MyTestTable'
tbl.Columns.Append "TestColumn_1", adInteger   ' add a new column, integer data type
tbl.Columns.Append "TestColumn_2", adVarWChar  ' add a new column, text data type
cat.Tables.Append tbl   ' append 'MyTestTable' to the Tables Collection

ky.Name = "PrimaryKey"   ' create a Primary key
ky.Type = adKeyPrimary
ky.Columns.Append "TestColumn_1"   ' Append Primary key to the Columns Collection
tbl.Keys.Append ky   ' Append new Key to the Keys Collection of the table

' Create the foreign key and append it to the table "Employees"
kyForeign.Name = "foreinKey"
kyForeign.Type = adKeyForeign
kyForeign.RelatedTable = "MyTestTable"
col.Name = "TestColumn_2"
kyForeign.Columns.Append col
kyForeign.Columns("TestColumn_2").RelatedColumn = "TestColumn_2"
kyForeign.UpdateRule = adRICascade

strSql = "SELECT * FROM Customers WHERE CustomerID = 'ANTON'"
cmd.CommandText = strSql
cat.Views.Append "MyNewQuery", cmd
 For Each myView In cat.Views
    Debug.Print myView.Name
 Next myView
 For Each grp In cat.Groups
    Debug.Print grp.Name
 Next grp
 For Each usr In grp.Users
    Debug.Print usr.Name
 Next usr
End Sub

Go To Top

NumericScale Property (ADOX)
Indicates the scale of a numeric value in the column. It sets and returns a Byte value that is the scale of data values in the column when the Type property is adNumeric or adDecimal. NumericScale is ignored for all other data types. The default value is zero (0). NumericScale is read-only for Column objects already appended to a collection.

Applies To:
Column Object (ADOX)

Example:

cat.ActiveConnection = _
  "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\MyData\Northwind.mdb;"
Set tbl = cat.Tables("Order Details")

' Display numeric scale and precision of small integer fields.
For Each colLoop In tbl.Columns
    If colLoop.Type = adSmallInt Then
       MsgBox "Column: " & colLoop.Name & vbCr & _
       "Numeric scale: " & colLoop.NumericScale & vbCr & _
       "Precision: " & colLoop.Precision
    End If
Next colLoop

ParentCatalog Property (ADOX)
Specifies the parent catalog of a Table, User, or Column object to provide access to provider-specific properties. It sets and returns a Catalog object. Setting ParentCatalog to an open Catalog allows access to provider-specific properties prior to appending a table or column to a Catalog collection.

Note:
Some data providers allow provider-specific property values to be written only at creation: that is, when a table or column is appended to its Catalog collection. To access these properties before appending these objects to a Catalog, specify the Catalog in the ParentCatalog property first.

An error occurs when the table or column is appended to a different Catalog than the ParentCatalog.

Applies To:
Column Object (ADOX)
Table Object (ADOX)
User Object (ADOX)

[See this example]

Precision Property (ADOX)
Indicates the maximum precision of data values in the Column. It sets and returns a Long value that is the maximum precision of data values in the column when the Type property is a numeric type. Precision is ignored for all other data types. The default value is zero (0).

Applies To:
Column Object (ADOX)

[See this example]

PrimaryKey Property (ADOX)
Indicates whether the index represents the primary key on the table. It sets and returns a Boolean value. The default value is False.

Applies To:
Index Object (ADOX)

[See this example]

Go To Top

RelatedTable Property (ADOX)
Indicates the name of the related table. The default value is an empty string (""). If the key is a foreign key, then RelatedTable is the name of the table that contains the key.

Applies To:
Key Object (ADOX)

Example:

Dim kyPrimary As New ADOX.Key
Dim cat As New ADOX.Catalog
Dim tblNew As New ADOX.Table

cat.ActiveConnection = "Provider='Microsoft.Jet.OLEDB.4.0';" & _
   "data source='Northwind.mdb';"
tblNew.Name = "MyNewTable"
tblNew.Columns.Append "NumericField", adInteger, 20
tblNew.Columns.Append "TextField", adVarWChar, 20
cat.Tables.Append tblNew

' Define the Primary key
kyPrimary.Name = "NumField"
kyPrimary.Type = adKeyPrimary
kyPrimary.RelatedTable = "Customers"
kyPrimary.Columns.Append "NumericField"
kyPrimary.Columns("NumericField").RelatedColumn = "CustomerId"
kyPrimary.DeleteRule = adRICascade

' Append the primary key
cat.Tables("MyNewTable").Keys.Append kyPrimary

RelatedColumn Property (ADOX)
Indicates the name of the related Column Object (ADOX) in the related table (key columns only). It sets and returns a String value that is the name of the related column in the related table. The default value is an empty string ("").

Applies To:
Column Object (ADOX)

[See this example]

Type Property (Column) (ADOX)
Indicates the data type of a column. It sets or returns a Long value that can be one of the DataTypeEnum constants. The default value is adVarWChar. This property is read/write until the Column object is appended to a collection or to another object, after which it is read-only.

Applies To:
Column Object (ADOX)

Example:

Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As New ADOX.Column
Dim ky As New ADOX.Key

cnn.Open "Provider='Microsoft.Jet.OLEDB.4.0';" & _
    "data source=D:\MyData\Northwind.mdb;"
Set cat.ActiveConnection = cnn
Set tbl = cat.Tables("Employees")
Debug.Print tbl.Type   ' cache tbl.Type info

tbl.Columns.Append "MyTestColumn1", adInteger  ' create a column, integer type
ky.Name = "PrimaryKey" ' create Primary keys
ky.Type = adKeyPrimary
ky.Columns.Append "MyTestColumn"  ' append Primary key to the Columns Collection

col.Name = "MyTestColumn2"
col.Type = adLongVarWChar
col.Attributes = adColNullable
cat.Tables("Employees").Columns.Append col.Name   ' create another column, memo type

cnn.Close
Set cat = Nothing
Set cnn = Nothing

Type Property (Key) (ADOX)
Indicates the type of the key. It sets or returns a Long value that can be one of the KeyTypeEnum constants. The default value is adKeyPrimary. This property is read-only on Key objects.

Applies To:
Key Object (ADOX)

Type Property (Table) (ADOX)
Indicates the type of a table. Returns a String value that specifies the type of table; for example, "TABLE", "SYSTEM TABLE", or "GLOBAL TEMPORARY". This property is read-only.

Applies To:
Table Object (ADOX)

[see above example]

Unique Property (ADOX)
Indicates whether the index keys must be unique. It sets and returns a Boolean value. The default value is False.

Applies To:
Index Object (ADOX)

[see above example]

UpdateRule Property (ADOX)
Indicates the action performed when a primary Key is updated. It sets and returns a Long value that can be one of the RuleEnum constants. The default value is adRINone. This property is read-only on Key objects.

Applies To:
Key Object (ADOX)

[see above example]
 

ADOX Methods
 
Append Method (ADOX Columns)
Adds a new Column object to the Columns collection.

An error will occur when appending a Column to the Columns collection of an Index if the Column does not exist in a Table that is already appended to the Tables collection.

Applies To
Columns Collection (ADOX)

Syntax:

Columns.Append Column [,Type] [,DefinedSize]

Example:

' This creates the table called "MyTestTable"
objTable.Name = "MyTestTable"
' Add 3 columns, Append field names and data types to "MyTestTable" Columns Collection
objTable.Columns.Append "EmployeeID", adInteger
objTable.Columns.Append "EmployeeName", adVarWChar, 30
objTable.Columns.Append "DateJoined", adDate, Date

[see example 1, example 2, example 3]

Append Method (ADOX Groups)
Adds a new Group object to the Groups collection. The Groups collection of a Catalog represents all of the catalog's group accounts. The Groups collection for a User represents only the group to which the user belongs.

Note:
Before appending a Group object to the Groups collection of a User object, a Group object with the same Name as the one to be appended must already exist in the Groups collection of the Catalog.

Applies To
Groups Collection (ADOX)

Syntax:

Groups.Append Group

[see this example]

Append Method (ADOX Indexes)
Adds a new Index object to the Indexes collection.

Applies To
Indexes Collection (ADOX)

Syntax:

Groups.Append GroupIndexes.Append Index [,Columns]

Example:

' Define a multi-column index.
objIndex.Name = "multiCol_Index"
objIndex.Columns.Append "MyColumn1"
objIndex.Columns.Append "MyColumn2"

' Append the index to the table 'MyTestTable'.
objTable.Indexes.Append idx

Append Method (ADOX Keys)
Adds a new Key object to the Keys collection.

Syntax:

Keys.Append Key [,KeyType] [,Column] [,RelatedTable] [,RelatedColumn]

Paameters:
Key - The Key object to append or the name of the key to create and append.

KeyType - Optional. A Long value that specifies the type of key. The Key parameter corresponds to the Type property of a Key object.

Column - Optional. A String value that specifies the name of the column to be indexed. The Columns parameter corresponds to the value of the Name property of a Column object.

RelatedTable - Optional. A String value that specifies the name of the related table. The RelatedTable parameter corresponds to the value of the Name property of a Table object.

RelatedColumn - Optional. A String value that specifies the name of the related column for a foreign key. The RelatedColumn parameter corresponds to the value of the Name property of a Column object.

Note:
The Columns parameter can take either the name of a column or an array of column names.

Example:

Dim kyForeign As New ADOX.Key
Dim cat As New ADOX.Catalog

' Define the foreign key
kyForeign.Name = "CustOrder"
kyForeign.Type = adKeyForeign
kyForeign.RelatedTable = "Customers"
kyForeign.Columns.Append "CustomerId"
kyForeign.Columns("CustomerId").RelatedColumn = "CustomerId"
kyForeign.UpdateRule = adRICascade
' Append the foreign key to the keys collection
cat.Tables("Orders").Keys.Append kyForeign

Append Method (ADOX Procedures)
Adds a new Procedure object to the Procedures collection. Creates a new procedure in the data source with the name and attributes specified in the Command object.

Note:
When using the OLE DB Provider for Microsoft Jet, the Procedures collection Append method will allow you to specify a View rather than a Procedure in the Command parameter. The View will be added to the data source and will be added to the Procedures collection. After the Append, if the Procedures and Views collections are refreshed, the View will no longer be in the Procedures collection and will appear in the Views collection.

Syntax:

Procedures.Append Name, Command

Paameters:
Name - a String value that specifies the name of the procedure to create and append.
Command - an ADO Command object that represents the procedure to create and append.

Example:

Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim cat As New ADOX.Catalog
' Open the Connection
cnn.Open "Provider='Microsoft.Jet.OLEDB.4.0';Data Source='D:\MyData\Northwind.mdb';"
' Create the command
Set cmd.ActiveConnection = cnn
cmd.CommandText = "Select * From Customers Where CustomerId = 'Frank' and Country='Germany'"
' Open the Catalog
Set cat.ActiveConnection = cnn

' Create the new Procedure
cat.Procedures.Append "CustomerById", cmd

Append Method (ADOX Tables)
Adds a new Table object to the Tables collection.

Syntax:

Tables.Append Table

objTable.Columns.Append "EmployeeName", adVarWChar, 30
objCatalog.Tables.Append objTable

Append Method (ADOX Users)
Adds a new User object to the Users collection.

Note:
The Users collection of a Catalog represents all the catalog's users. The Users collection for a Group represents only the users that have a membership in the specific group.

Syntax:

Users.Append User[,Password]

Parameters:
User - a Variant value that contains the User object to append or the name of the user to create and append.

Password - Optional. A String value that contains the password for the user. The Password parameter corresponds to the value specified by the ChangePassword method of a User object.

Example:

With objCatalog
  .Groups.Append "Accounting"  ' Create and append new group with a string
  Set usr = New ADOX.User
  usr.Name = "Pat Smith"
  usr.ChangePassword "", "mypassword"
  .Users.Append usr   ' Create and append new user with an object
  usr.Groups.Append "Accounting"   ' create and add the Group object to the user's Groups collection
End With

Append Method (ADOX Views)
Creates a new View object and appends it to the Views collection.

Note:
The Users collection of a Catalog represents all the catalog's users. The Users collection for a Group When using the OLE DB Provider for Microsoft Jet, the Views collection Append method will allow you to specify a Procedure rather than a View in the Command parameter. The Procedure will be added to the data source and will be added to the Views collection. After the Append, if the Procedures and Views collections are refreshed, the Procedure will no longer be in the Views collection and will appear in the Procedures collection.

Syntax:

Views.Append Name, Command

Example:

' Create the command representing the view
objCommand.CommandText = "SELECT * FROM Customers WHERE City = 'London'"

' Create the new View and new query 'qry_AllCustomers'
objCatalog.Views.Append "Query_CustomersByCity", objCommand

ChangePassword Method (ADOX)
Changes the password for a user account.

Syntax:
User.ChangePassword OldPassword, NewPassword

Example:

' Add a User named 'william' and password 'kuching'
cat.Users.Append "William", "kuching"
' Change user's password
cat.Users("William").ChangePassword "Kuching", "Cat"

Create Method (ADOX)
Creates a new catalog. The Create method creates and opens a new ADO Connection to the data source specified in ConnectString. If successful, the new Connection object is assigned to the ActiveConnection property.

Applies To:
Catalog Object (ADOX)

Syntax:

Catalog.Create ConnectString

Example:
' create a new Microsoft Jet database with the Create method.
Dim cat As New ADOX.Catalog
objCatalog.Create "Provider='Microsoft.Jet.OLEDB.4.0';Data Source='CreateNew.mdb'"
' CreateNew.mdb will be created in Default Database Folder in Tools/Options/General

Delete Method (ADOX Collections)
Removes an object from a collection.

Applies To:
Columns Collection (ADOX)
Groups Collection (ADOX)
Indexes Collection (ADOX)
Keys Collection (ADOX)
Procedures Collection (ADOX)
Tables Collection (ADOX)
Users Collection (ADOX)
Views Collection (ADOX)

Syntax:

Collection.Delete Name

Example:

' To delete the procedure.
cat.Procedures.Delete "CustomerById"

' To delete the View
cat.Views.Delete "qry_CustomersByCity"

' To delete the table
cat.Tables.Delete tbl.Name

' To delete the column field
tbl.Columns.Delete col.Name
'Or, tbl.Columns.Delete "Column3"

' To delete user's account and group
cat.Users.Delete ("William")
cat.Groups.Delete ("Planning")

GetObjectOwner Method (ADOX)
Returns the owner of an object in a Catalog.

Applies To:
Catalog Object (ADOX)

Syntax:

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

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.

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.

[see this example]

GetPermissions Method (ADOX)
Returns the permissions for a group or user on an object or object container.

Applies To:
Group Object (ADOX)
User Object (ADOX)

Syntax:

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

Parameters:
Name - a Variant value that specifies the name of the object for which to set 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 ObjectTypeEnum constants, that specifies the type of the object for which to get permissions.

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.

[see this example]

SetObjectOwner Method [ADOX]
Specifies the owner of an object in a Catalog.

Applies To:
Group Object (ADOX)
User Object (ADOX)

Syntax:

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

Parameters:
ObjectName - a String value that specifies the name of the object for which to specify the owner.

ObjectType - a Long value which can be one of the ObjectTypeEnum constants that specifies the owner type.

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 that is not defined by the OLE DB specification. This parameter is required if ObjectType is set to adPermObjProviderSpecific; otherwise, it is not used.

[see this example]

SetPermissions Method (ADOX)
Specifies the permissions for a group or user on an object.

Applies To:
Group Object (ADOX)
User Object (ADOX)

Note:
When calling SetPermissions, setting Actions to adAccessRevoke overrides any settings of the Rights parameter. Do not set Actions to adAccessRevoke if you want the rights specified in the Rights parameter to take effect.