Using ADOX to Manipulate Data Objects

The ADOX library has an object model that allows you to create database objects such as tables, indexes, and keys, as well as to control security, establish referential integrity in a database, and perform cascade updates and deletions. The Catalog object is at the top of the ADOX object model, with Tables, Groups, Users, Procedures, and Views collections. Please consult the online help for the complete ADOX object model.

Just as with the ADODB library, if you want to make use of the ADOX library in your Access solutions, you must add a reference. You can add references by selecting Tools C> References in the Visual Basic Editor. Figure 5.18 shows a reference to the ADOX library as part of the current project.

Let's look at a few examples of what you can do with the ADOX library.

References - ChSCodeExamples

Available References:

0 Visual Basic For Applications 0 Microsoft Access 11.0 Object Library @ OLE Automaton

'.^i Microsoft ActiveX Data Objects 2.7 Library


i_! Microsoft ActiveX Data Objects 2.1 Library ..J Microsoft ActiveX Data Objects 2.5 Library ll Microsoft DAO 3.6 Object Library

IAS Helper COM Component 1.0 Type Library

□ IAS RADIUS Protocol 1.0 Type Library

□ Access 1.0 Type Library n Armbat ITintflpr_





Microsoft ADO Ext. 2.7 for DDL and Security

Location: C:\Program Files\Common Files^ystem'^do^sadox.c Language: Standard

Figure 5.18

Creating a Table with ADOX

For starters, you can create new databases, tables, and other objects using the ADOX library. The following procedure is an example of how you can create a new table in an existing database.

Sub TestCreateTable()

Dim catCatalog As ADOX.Catalog

Dim tblSupplier As ADOX.Table

Set catCatalog = New ADOX.Catalog

Set catCatalog.ActiveConnection =


1 Create and name the new table

Set tblSupplier = New ADOX.Table

With tblSupplier

.Name = "tblSupplier"

.Columns.Append "CompanyName",

adVarWChar, 50

.Columns.Append "CompanyPhone"

, adVarWChar, 12

End With

'append the new table to the database

catCatalog.Tables.Append tblSupplier

'release memory

Set catCatalog.ActiveConnection =


Set catCatalog = Nothing Set tblSupplier = Nothing

End Sub

First, a new Catalog object is declared and is assigned to the current open connection. Then, a new table called tblSupplier is created, and a name and new columns are assigned to it. The new table is then appended to the Catalog.Tables collection, which added it to the database. After running the preceding procedure, you can see (in Figure 5.19) that a new table called tblSupplier has been added to the database.

Ch5CodeExamp!es : Database (Access 2002 -... ^pjQ

L-j^Open /Design jNew -j IV j: : [HI


■¿¡J Create table in Design view '¿LJ Create table by using wizard '¿¡J Create table by entering data □ tblContacte


Queries |3 Forms

..'2 tblSupplier:

Figure 5.19

Managing Security with ADOX

In addition to managing databases and tables, you can also use the ADOX library to manage security, including users, passwords, and groups. The following example adds a new group called Wrox Readers and then adds a new user called John Doe to that new group.

Sub TestAddUsersGroups()


catCatalog As ADOX.Catalog


usrUser As ADOX.User


grpGroup As ADOX.Group


catCatalog = New ADOX.Catalog


catCatalog.ActiveConnection = CurrentProject.Connection

With catCatalog

'Create a new group

.Groups.Append "Wrox Readers"

1 Create a new user

Set usrUser = New ADOX.User usrUser.Name = "John Doe"

usrUser.ChangePassword "", "initialpassword1" .Users.Append usrUser

'add the new user to the new group usrUser.Groups.Append "Wrox Readers"

End With

'release memory

Set catCatalog.ActiveConnection = Nothing Set catCatalog = Nothing Set usrUser = Nothing

End Sub

Notice again how a Catalog object is used, and this time so is a User and a Group object. The new group is created first, and then the new user is created, and finally the new user is appended to the new group. After executing the preceding code, you can see in Figure 5.20 that the John Doe user was added to the current project along with the Wrox Readers group. The dialog box in Figure 5.20 can be displayed by selecting Tools C> Security C> User and Group Accounts.

Figure 5.20

The Group object also has other methods, such as SetPermissions and GetPermissions. SetPermissions method assigns different access rights and GetPermissions retrieves current permissions in effect for the group. These and many other features can be manipulated using the ADOX library. This section is not meant to be exhaustive, but just to provide you with a few examples of how you might use the ADOX library.

0 0

Post a comment