ADOX
ADOX adds data definition language features previously available only in DAO. You can use ADOX objects to create, modify, or examine tables, queries, and security settings for databases. Rather than having to create tables with action queries such as
CREATE TABLE Customers (CustomerID varchar(5) NOT NULL CONSTRAINT PK_Customers PRIMARY KEY, CompanyName varchar(40), BalanceDue money) |
you can use code such as the following:
'Create a new Table object. Set tblCustomers = New ADOX.Table With tblCustomers .Name = "Customers" 'Specify column information. .Columns.Append "CustomerID", adVarChar, 5 .Columns.Append "CompanyName", adVarChar, 40 .Columns.Append "BalanceDue", adCurrency 'Add the primary key. .Keys.Append "PK_Customers", adKeyPrimary, "CustomerID" End With 'Add the new table to your database. catDatabase.Tables.Append tblCustomers |
What a Great Idea!
I don't mind admitting it. I love the concept of ADOX. That might not sound like the most glowing recommendation, but in a moment, you'll see some of the benefits and limitations of the ADOX implementation.
Some of the Microsoft Visual Studio 6 development tools allow you to generate new tables, views, and stored procedures for Microsoft SQL Server and Oracle databases. The database tools, including the DataView window, allow you to fill out a grid with information about each column and generate a CREATE TABLE query to your database that creates the table. Users often ask why these features don't work with other databases.
Each database has its own data definition language quirks. For example, a text field in SQL Server is called a long field in Oracle and a memo field in Access. These differences make it difficult to build a generic tool to generate DDL queries that work on all databases. The DataView window must include SQL Server-specific and Oracle-specific code that accepts input and generates the appropriate DDL query. But wouldn't it be easier if you could generate database-independent code to build tables and queries?
ADOX is designed to let you do exactly that. You don't have to know what SQL Server, Oracle, and Access call their large text fields. You simply have to remember what ADO calls them: adLongVarChar. But wait! When you use ADOX, who generates the database-specific DDL queries? ADOX doesn't inherently know all the DDL idiosyncrasies of each database, and ADOX isn't like the Visual Studio database tools that are designed to create and modify objects in just two databases—SQL Server and Oracle.
It's actually the OLE DB provider that generates the DDL queries. Think of ADOX as a simple standard that asks the OLE DB provider to generate DDL queries. ADOX is merely a middleman. That's why I think ADOX is such a great idea. It provides the structure but leaves it up to the OLE DB provider to offer the functionality.
Now for the Bad News
Unfortunately, this means that in order to use ADOX to interact with your database, you need to use an OLE DB provider that supports ADOX interfaces. At the time of this writing, only two OLE DB providers support a significant portion of the features available in ADOX: the OLE DB Provider For SQL Server and the Jet 4.0 OLE DB Provider.
The Jet 4.0 OLE DB Provider supports almost all of ADOX. You can use this provider with ADOX to create an Access 2000 database and populate it with tables, queries, users, and groups. However, you cannot add Access-specific objects such as reports, forms, and modules by using ADOX. There's a sample titled Make Northwind on the companion CD that creates an Access 2000 database using ADOX and the Jet 4.0 OLE DB Provider. This sample is nearly identical to the Northwind database included with Access and Microsoft Visual Basic, except that it doesn't include the forms, reports, and modules. Discounting those objects, I'd say that using the Jet 4.0 OLE DB Provider with ADOX allows you to create about 95 percent of the functionality of an Access 2000 database. The only significant feature the Jet 4.0 OLE DB Provider with ADOX lacks is the ability to create new security databases (.mdw).
The SQL Server OLE DB provider supports a smaller subset of the ADOX features. I've yet to find definitive documentation stating exactly which ADOX features this provider does support or outlining any plans to add more ADOX functionality to the provider. However, here's what I have learned through my own experiences:
- You cannot access the Users, Groups, and Views collections, and you cannot create Catalogs (databases).
- Using ADOX with the SQL Server OLE DB provider lets you access about two-thirds of the functionality of a SQL Server database.
ADOX Object Model
Unless you plan to interact with only Access databases, I don't recommend using ADOX. For that reason, I won't cover every property and method of every object in the ADOX hierarchy. Instead, I'll cover the highlights of each object and collection in the ADOX object model, shown in Figure A-1. Version 2.5 of the ADOX objects can be accessed in Visual Basic by selecting Microsoft ADO Ext. 2.5 For DDL And Security in the References dialog box.
Figure A-1 The ADOX object model.
Catalog object
The ADOX Catalog object corresponds to your database. To connect to an existing database, use the Catalog's ActiveConnection property just as you would for the Connection object. Set the property to a valid connection string or a Connection object.
You can use the Catalog object to create new Access databases with the Jet 4.0 OLE DB Provider. Call the Create method on the Catalog object and supply a connection string that includes the path to the Access database you want to create, as shown here:
strPathToFile = "C:\Windows\Desktop\New.mdb" strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strPathToFile & ";" Set cat = New ADOX.Catalog cat.Create strConn |
By default, the Jet 4.0 OLE DB Provider will create an Access 2000 database (Jet 4.0). You can specify that you want to create a Jet 3.x (Access 95 and Access 97) database by using the Jet OLEDB:Engine Type property in the connection string, as follows:
strPathToFile = "C:\Windows\Desktop\New97.mdb" strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strPathToFile & ";" & _ "Jet OLEDB:Engine Type=4;" Set cat = New ADOX.Catalog cat.Create strConn |
You can use the Catalog object to determine and specify which user owns an object by calling the GetObjectOwner and SetObjectOwner methods, as shown here:
'Display the current owner of the Customers table. MsgBox cat.GetObjectOwner("Customers", adPermObjTable) 'Set JoeUser to the owner of the Customers table. Call cat.SetObjectOwner("Customers", adPermObjTable, "JoeUser") |
NOTE
Keep in mind that when you're using ADOX security features (the User and Group objects and the GetObjectOwner and SetObjectOwner methods) you'll need to specify the location of the system database file, which is usually named system.mdw.
The Catalog object also exposes the Tables, Views, Procedures, Users, and Groups collections.
Table object and Tables collection
As its name indicates, the Table object refers to a table in your database. The Table object exposes the Columns, Indexes, Keys, and Properties collections. There are two properties of the Table object that you should be aware of: Name and Type.
Before you try to append a Table object to a Catalog's Tables collection, be sure you set the Name property on the Table object. You might find more entries in a Catalog object's Tables collection than expected. Items in the collection can include views and system tables. To determine which type of object an item is, check the Table object's Type property, which will return a string. Checking the Access Northwind database, you'll see Table objects whose Type property returns TABLE, VIEW, SYSTEM TABLE, and ACCESS TABLE.
To see some sample code that shows how to create a Table object, check out the "Property object and Properties collection" section.
Column object and Columns collection
The Column object corresponds to a column in your table. It exposes many of the same properties that the ADO Field object exposes: Attributes, DefinedSize, Name, NumericScale, Precision, and Type.
The Column object's RelatedColumn property is applicable only to Column objects in the Columns collection of a Key object and is designed for foreign keys. You'll see how to use the RelatedColumn property shortly when we cover the Property object and the Properties collection.
The SortOrder property applies only to Column objects in the Columns collection of an Index object and controls whether entries in an Index object are stored in ascending or descending order.
There are two ways to use a Table object's Columns collection to append a Column object. First you can create a Column object, set its properties, and then append it to the Columns collection, as shown here:
Set colCustomerID = New ADOX.Column colCustomerID.Name = "CustomerID" colCustomerID.Type = adVarWChar colCustomerID.Size = 5 tblCustomers.Columns.Append colCustomerID |
The Append method on the Columns collection accepts a Variant as its first parameter. You can pass a Column object into this parameter as just shown.
A second way to append a Column object is by passing to the Append method a string that contains the name of the new Column object you want to create, as shown here:
tblCustomers.Columns.Append "CustomerID", adVarWChar, 5 |
When you use the Append method in this fashion, use the second parameter to specify the data type for the new Column object. The third parameter is optional and is used to specify the size of the new column if it's a variable-sized data type such as a character or binary field.
Key object and Keys collection
The Key object corresponds to a primary, unique, or foreign key (or constraint) on a table. The Type property controls what type of key the object is. The Key object's Columns collection contains the Column object that the key references.
For foreign keys, the RelatedTable property refers to the name of the related table. Use the DeleteRule or UpdateRule property to control what occurs when you delete or modify the primary key value of a row in the parent table of a relationship. The DeleteRule and UpdateRule properties are updatable only before you append the Key object to the Keys collection.
The Append method on the Keys collection is similar to the Columns collection's Append method. You can append a Key object to the Keys collection with code such as
Set keyPrimary = New ADOX.Key |
tblOrders.Keys.Append keyPrimary You can also use the Append method to create a new Key object and add it to the Keys collection in one call, as shown here:
tblOrders.Keys.Append "PK_Orders", adKeyPrimary, "OrderID" |
The first parameter specifies the name of the Key object. The second parameter corresponds to the Key object's Type property, and the third parameter corresponds to the name of the Column object referenced by the Key object. This syntax comes in handy when you're creating a primary key or a unique key.
But if you plan to create a foreign key, you'll need to specify the related table and corresponding column. Use the fourth and fifth parameters on the Append method to supply this information, as shown here:
tblOrders.Keys.Append "FK_Orders_Employee", adKeyForeign, "EmployeeID", _ "Employees", "EmployeeID" |
Index object and Indexes collection
To add an index to a table, create an Index object. You can control whether Null values are allowed in the index by setting the IndexNulls property to a value in AllowNullsEnum. The default value for IndexNulls is adIndexNullsDisallow. The Clustered property accepts a Boolean value that controls whether the index is clustered; this property is False by default. To control whether the entries in the index are unique, set the Unique property on the Index object. This property is set to False by default. The Clustered and Unique properties are read-only once you've appended the Index object to the Indexes collection.
The Index object exposes two collections, Columns and Properties. The Columns collection contains references to the columns that make up the index. The Properties collection contains database-specific properties for the index.
Like the Append method on the Columns and Keys collections, the Append method on the Indexes collection has the ability to create a new Index object and add it to the Indexes collection. This code passes an Index object to the Indexes collection's Append method:
Set idx = New ADOX.Index |
To create a new Index object and add it to the Indexes collection in a single call, supply the name of the new Index object as the first parameter and the name of the Column object that it references as the second parameter:
tbl.Indexes.Append "IndexName", "ColumnName" |
If you want to create an Index object that references multiple Column objects, pass a Variant array that contains the column names for the second parameter, as shown here:
tbl.Indexes.Append "IndexName", Array("Column1", "Column2", ... "ColumnN") |
Property object and Properties collection
Like the ADO object model, the ADOX object model exposes database-specific properties through the Properties collection. The Table, Column, and Index objects each have a Properties collection. For example, you might want to specify that a column in a table is auto-incrementing. This database-specific property is available in the Column object's Properties collection, rather than as a direct property of the Column object.
If you want to access a database-specific property of a Column object through its Properties collection, make sure that you've set the ParentCatalog property on the Column object or its parent Table object.
The following code creates the Orders table in the Northwind database. Note that the code sets the ParentCatalog property on the Table object to access the dynamic AutoIncrement property on the OrderID column.
'Create a new Table object. Set tbl = New ADOX.Table 'Set the ParentCatalog property on the Table ' to expose the database-specific properties. Set tbl.ParentCatalog = cat tbl.Name = "Orders" 'Add the Columns to the table. tbl.Columns.Append "OrderID", adInteger tbl.Columns("OrderID").Properties("AutoIncrement") = True tbl.Columns.Append "CustomerID", adWChar, 5 |
View and Procedure objects and Views and Procedures collections
The View object and the Procedure object have identical structures. They each expose a Name property as well as the DateCreated and DateModified properties. Both the View and Procedure objects make the structure of their queries available through a property called Command, which contains a Command object.
See the "Questions That Should Be Asked More Frequently" section at the end of this appendix for some peculiarities that occur when creating and interacting with Access QueryDefs, the Access term for stored queries.
User and Group objects and Users and Groups collections
The User object represents a user's account in the database, while the Group object corresponds to a group of users. Each object has a Name property as well as methods for setting and checking permissions on an object. The User object also has a method for changing the user's password, aptly named ChangePassword.
The User and Group objects are linked together in the ADOX object model. Because a user can be a member of many groups, the User object has a Groups collection that lists the groups of which that user is a member. Similarly, a group can contain multiple users, and the Group object has a Users collection that lists its members.
You can add a user to or remove a user from a group by using the User object's Groups collection. For example, the following code might be helpful if an employee is promoted:
cat.Users("Joe").Groups.Delete "Engineers" cat.Users("Joe").Groups.Append "Managers" |
You could also accomplish the same task by using the Users collection of the Group object, as shown here:
cat.Groups("Engineers").Users.Delete "Joe" cat.Groups("Managers").Users.Append "Joe" |
ADOX Security Sample
The following code sample helped me grasp the concept of using Jet security through the ADOX object model. This code creates a new Access database with an Orders table. The code then creates several new users and groups. Some of the users are placed in the Sales group, while one is placed in the Guests group. The Sales group is given read, insert, and update (but not delete) permissions to the Orders table, while the Guests group has no permissions on the table. The sample then uses ADO code to simulate each user logging in and trying to perform an action in order to demonstrate that the permissions were set up properly.
NOTE
Database security is not a minor topic. Jet programmers who want to learn about Jet security (if you're reading this portion of the appendix, this probably applies to you) should read Chapter 10, "Managing Security," of the Microsoft Jet Database Engine Programmer's Guide, Second Edition, by Dan Haught and Jim Ferguson [Microsoft Press, 1997].
Dim strPathToMDB As String, strPathToMDW As String Dim strConn As String, strSQL As String Dim cat As ADOX.Catalog, tblOrders As ADOX.Table Dim cn As ADODB.Connection, rs As ADODB.Recordset 'Initialize path to the new database. strPathToMDB = "C:\Windows\Desktop\Secure.mdb" 'Initialize path to an existing Access 2000 security database. strPathToMDW = "D:\Office\Access2000\Office\System.MDW" strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strPathToMDB & ";" & _ "Jet OLEDB:System Database=" & strPathToMDW & ";" If Dir(strPathToMDB) <> "" Then Kill strPathToMDB Set cat = New ADOX.Catalog cat.Create strConn & "User ID=Admin;Password=;" Set tblOrders = New ADOX.Table With tblOrders Set .ParentCatalog = cat .Name = "Orders" .Columns.Append "OrderID", adInteger !OrderID.Properties("AutoIncrement") = True .Columns.Append "OrderDate", adDate .Columns.Append "OrderAmt", adCurrency .Keys.Append "PK_Orders", adKeyPrimary, "OrderID" End With cat.Tables.Append tblOrders 'Create users. cat.Users.Append "Al", "" cat.Users.Append "Beth", "" cat.Users.Append "Charles", "" cat.Users.Append "Zed", "" 'Create groups. cat.Groups.Append "Sales" cat.Groups.Append "Guests" 'Add users to groups. cat.Groups("Sales").Users.Append "Al" cat.Groups("Sales").Users.Append "Beth" cat.Groups("Sales").Users.Append "Charles" cat.Groups("Guests").Users.Append "Zed" 'Set permissions on Orders table for groups. 'Allow the people in sales to modify the contents of the Orders table. 'Prevent guests from viewing the contents of the table. cat.Groups("Sales").SetPermissions "Orders", adPermObjTable, _ adAccessGrant, adRightRead + adRightInsert + adRightUpdate cat.Groups("Guests").SetPermissions "Orders", adPermObjTable, _ adAccessDeny, adRightNone 'Close this connection. cat.ActiveConnection.Close Set cat = Nothing strSQL = "SELECT * FROM Orders" 'Log in as someone in sales and add an order. Set cn = New ADODB.Connection cn.Open strConn, "Al", "" Set rs = New ADODB.Recordset rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic, adCmdText rs.AddNew Array("OrderDate", "OrderAmt"), _ Array(Date, "100.00") rs.Close cn.Close 'Log in as someone in sales, and modify an order. cn.Open strConn, "Beth", "" rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic, adCmdText rs.Update Array("OrderAmt"), _ Array("200.00") rs.Close cn.Close 'Log in as someone in sales, and try to delete an order. 'According to the permissions we've set, this should fail. cn.Open strConn, "Charles", "" rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic, adCmdText On Error Resume Next rs.Delete If Err.Number <> 0 Then Err.Clear cn.Errors.Clear rs.CancelUpdate Else MsgBox "Oops. This user should not have been able to delete a " & _ "row in the Orders table." End If On Error GoTo 0 rs.Close cn.Close 'Log in as a general user, and query the Orders table. 'According to the permissions we've set, this should fail. cn.Open strConn, "Zed", "" On Error Resume Next rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic, adCmdText If Err.Number <> 0 Then Err.Clear cn.Errors.Clear Else MsgBox "Oops. This user should not have been able to view the " & _ "Orders table." rs.Close End If On Error GoTo 0 cn.Close |
NOTE
Be sure to check out the "Questions That Should Be Asked More Frequently" section at the end of this appendix if you have trouble running this code a second time.
EAN: N/A
Pages: 131