Lesson 28: ADO Extended (ADOX)

	ADO Extended (ADOX)

ADOX Fundamentals

Introduction

The ADO library by itself has some limitations in the areas of security or the ability to create a database. To address these issues, Microsoft created an additional library called Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security, abbreviated ADOX.

Before using ADOX, you must reference it in Microsoft Visual Basic. To do this, open the References dialog box from the Tools menu and select your latest version of Microsoft ADO Ext. X for DDL and Security:

References

Like Microsoft Access' own library and ADO, ADOX relies on objects for its functionality. Most of the objects are stored in a workspace (or namespace) named ADOX. To use one of those objects, you can declare its variable and qualify its class from ADOX.

The Catalog Class of ADOX

To support the creation of a database, the ADOX library provides the Catalog class. To use it, declare a variable of type Catalog and qualify it as ADOX.Catalog. Here is an example:

Private Sub cmdAction_Click()
    Dim objCatalog As ADOX.Catalog
End Sub

Before using the Catalog variable, you must allocated memory for it. This is done using the New operator. Here is an example:

Private Sub cmdAction_Click()
    Dim objCatalog As ADOX.Catalog
    
    Set objCatalog = New ADOX.Catalog
End Sub

You can also refer to this property when declaring the variable. Here is an example:

Private Sub cmdAction_Click()
    Dim objCatalog As New ADOX.Catalog
End Sub

Whenever you create an ADO or ADOX object and while it is being used, it consumes resources. Therefore, after using the object, you should/must remove it from memory (if you don't, you may start seeing abnormal behavior on your computer after a while, as resources become scarce while other objects or applications are trying to use the same resources). To remove a resource from memory, after using it, assign it the Nothing constant.

Creating a Database

To create a database in ADOX, the ADOX workspace is equipped with a class named Catalog. To support database creation, the Catalog class is equipped with a method named Create. Its syntax is:

Catalog.Create(ByVal ConnectString As String)

This would be started as follows:

Private Sub cmdAction_Click()
    Dim objCatalog As ADOX.Catalog
    
    Set objCatalog = New ADOX.Catalog
    
    objCatalog.Create

    Set objCatalog = Nothing
End Sub

The Create() method of the Catalog class takes as argument a string, also called a connection string, that defines the assignment to perform. The connection string follows the same description we reviewed for the Connection class in ADO.

If you are dealing with a Microsoft SQL Server database, specify the provider as SQLOLEDB. Here is an example:

Private Sub cmdAction_Click()
    Dim objCatalog As ADOX.Catalog
    
    Set objCatalog = New ADOX.Catalog
    
    objCatalog.Create "provider=SQLOEDB"

    Set objCatalog = Nothing
End Sub

If you are dealing with a Microsoft Access database, specify the provider as Microsoft.JET.OLEDB.4.0 (case insensitive). Here is an example:

Private Sub cmdAction_Click()
    Dim objCatalog As ADOX.Catalog
    
    Set objCatalog = New ADOX.Catalog
    
    objCatalog.Create "provider=Microsoft.Jet.OLEDB.4.0"

    Set objCatalog = Nothing
End Sub

You can also specify the provider as Microsoft.ACE.OLEDB.12.0. Remember that you can include the name of the provider in single-quotes:

Private Sub cmdAction_Click()
    Dim objCatalog As ADOX.Catalog
    
    Set objCatalog = New ADOX.Catalog
    
    objCatalog.Create "provider='Microsoft.ACE.OLEDB.12.0'"

    Set objCatalog = Nothing
End Sub

If you are creating a SQL Server type of database, there are various other details you must provide to the connection string. For the rest of our lessons, we will use Microsoft JET.

To specify the second part of the connection string, which is the data source, use the same approach we saw for the Connection class of ADO. Here is an example:

Private Sub cmdCreateDatabase_Click()
    Dim objCatalog As ADOX.Catalog
    
    Set objCatalog = New ADOX.Catalog
    
    objCatalog.Create "provider='Microsoft.ACE.OLEDB.12.0';Data Source=Exercise.accdb"

    Set objCatalog = Nothing
End Sub

If you are creating a database and you provide (only) the name of the database, it would be created in the same folder as the application that called it. In the above example, the database would be created in the My Documents folder. If you want to create the database in a folder other than that of the application that called this method, provide a complete path of the database. Here is an example:

Private Sub cmdCreateDatabase_Click()
    Dim objCatalog As ADOX.Catalog
    
    Set objCatalog = New ADOX.Catalog
    
    objCatalog.Create "provider='Microsoft.ACE.OLEDB.12.0';Data Source=C:\Exercises\Exercise.accdb"

    Set objCatalog = Nothing
End Sub

If you are creating a Microsoft Access Office 2010 database, specify the extension as .accdb. If you want to create a database compatible with previous versions of Microsoft Access, specify the extension as .mdb.

Remember that you can create the connection string and store it in a String variable. Then pass that variable to the Create() method. Here is an example:

Private Sub cmdAction_Click()
    Dim objCatalog As ADOX.Catalog
    Dim strCreator As String
    
    Set objCatalog = New ADOX.Catalog
    strCreator = "provider='Microsoft.ACE.OLEDB.12.0';"
    strCreator = strCreator & "Data Source=C:\Exercises\Exercise.mdb'"
    
    objCatalog.Create strCreator

    Set objCatalog = Nothing
End Sub

ADOX and Tables

Creating a Table in ADOX

The ADOX library supports tables through a class named Table. Therefore, you can start by declaring a variable of type ADOX.Table. Allocate memory for it using the New operator. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim tblStudents As ADOX.Table
    
    Set tblStudents = New ADOX.Table
    
    Set colFirstName = Nothing
End Sub

To create a table, obviously the first characteristic you must specify is its name. To support this, the ADOX.Table class is equipped with a property called Name. Therefore, to start a table, assign a name to the Name property. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim tblStudents As ADOX.Table
    
    Set tblStudents = New ADOX.Table
    tblStudents.Name = "Students"

    Set tblStudents = Nothing
End Sub

After creating a table, you must add it to the database. To support tables, the Catalog class of the ADOX library is equipped with a property named Tables, which is a collection. Therefore, after creating a table, to make it part of the database, add it to the Tables collection of the Catalog object. To support this operation, the Tables collection is equipped with a method named Append, which takes as argument an ADOX.Table object. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim catStudents As ADOX.Catalog
    Dim tblStudents As ADOX.Table
    
    Set catStudents = New ADOX.Catalog
    catStudents.ActiveConnection = CurrentProject.Connection
    
    Set tblStudents = New ADOX.Table
    tblStudents.Name = "Students"
    
    . . . Continue creating the table
    
    catStudents.Tables.Append tblStudents

    Set tblStudents = Nothing
    Set catStudents = Nothing
End Sub

After creating the table, you should update the items of the Catalog object. To do this, call its Refresh() method. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim catStudents As ADOX.Catalog
    Dim tblStudents As ADOX.Table
    Dim colFirstName As ADOX.Column
    Dim colLastName As ADOX.Column
    
    Set catStudents = New ADOX.Catalog
    catStudents.ActiveConnection = CurrentProject.Connection
    
    Set tblStudents = New ADOX.Table
    tblStudents.Name = "Students"
    
    . . . Continue creating the table
    
    catStudents.Tables.Append tblStudents
    catStudents.Tables.Refresh
    Application.RefreshDatabaseWindow
    
    Set tblStudents = Nothing
    Set catStudents = Nothing
End Sub

Deleting a Table in the ADOX Library

As mentioned already, the ADOX library handles tables through its Catalog class that is equipped with the Tables collection property. The Tables collection is equipped with a method named Delete that takes one argument as the index or the name of the table to be deleted. Therefore, to delete a table, call the ADX.Catalog.Tables.Delete() method and pass the name (or the index, in the Tables collection) of the table. Here is an example:

Private Sub cmdDeleteTable_Click()
    Dim catStudents As ADOX.Catalog
    
    Set catStudents = New ADOX.Catalog
    catStudents.ActiveConnection = CurrentProject.Connection
    catStudents.Tables.Delete ("Students")
    
    MsgBox "The table named Students has been deleted."
    Set catStudents = Nothing
End Sub

The Columns of a Table in ADOX

Creating a Column in ADOX

In our introduction to tables, we saw how to start a table:

Private Sub cmdTableCreation_Click()
    Dim catStudents As ADOX.Catalog
    Dim tblStudents As ADOX.Table
    
    Set catStudents = New ADOX.Catalog
    catStudents.ActiveConnection = CurrentProject.Connection
    
    Set tblStudents = New ADOX.Table
    tblStudents.Name = "Students"
    
    . . . Continue creating the table
    
    catStudents.Tables.Append tblStudents
    catStudents.Tables.Refresh
    Application.RefreshDatabaseWindow
    
    Set tblStudents = Nothing
    Set catStudents = Nothing
End Sub

A table must have at least one column. To support columns, the ADOX library is equipped with a class called Column. Therefore, to start a column, you can declare a variable of type ADOX.Column. To initialize it and allocate memory for it, use the New operator. Here is an example:

Private Sub cmdTableCreation_Click()
    Dim catStudents As ADOX.Catalog
    Dim tblStudents As ADOX.Table
    Dim colFirstName As ADOX.Column
    
    Set catStudents = New ADOX.Catalog
    catStudents.ActiveConnection = CurrentProject.Connection
    
    Set tblStudents = New ADOX.Table
    tblStudents.Name = "Students"
    
    Set colFirstName = New ADOX.Column
    
    catStudents.Tables.Append tblStudents
    catStudents.Tables.Refresh
    Application.RefreshDatabaseWindow

    Set colFirstName = Nothing
    Set tblStudents = Nothing
    Set catStudents = Nothing
End Sub

As you are probably aware now, the primary piece of information you must provide for a column is its name. To support this, the ADOX.Column is equipped with a property named Name. To specify the name of a column, assign a string to this property. Here is an example:

Private Sub cmdTableCreation_Click()
    Dim catStudents As ADOX.Catalog
    Dim tblStudents As ADOX.Table
    Dim colFirstName As ADOX.Column
    
    Set catStudents = New ADOX.Catalog
    catStudents.ActiveConnection = CurrentProject.Connection
    
    Set tblStudents = New ADOX.Table
    tblStudents.Name = "Students"
    
    Set colFirstName = New ADOX.Column
    colFirstName.Name = "FirstName"
    
    catStudents.Tables.Append tblStudents
    catStudents.Tables.Refresh
    Application.RefreshDatabaseWindow
    
    MsgBox "A table named Students has been created."

    Set colFirstName = Nothing
    Set tblStudents = Nothing
    Set catStudents = Nothing
End Sub

After creating a column, to add it to its table, call the Append() method of the Columns collection. This method takes various arguments but only the first is required. This first argument is the ADOX.Table object to be added. Here is an example:

Private Sub cmdTableCreation_Click()
    Dim catStudents As ADOX.Catalog
    Dim tblStudents As ADOX.Table
    Dim colFirstName As ADOX.Column
    
    Set catStudents = New ADOX.Catalog
    catStudents.ActiveConnection = CurrentProject.Connection
    
    Set tblStudents = New ADOX.Table
    tblStudents.Name = "Students"
    
    Set colFirstName = New ADOX.Column
    colFirstName.Name = "FirstName"
    
    tblStudents.Columns.Append colFirstName
    
    catStudents.Tables.Append tblStudents
    catStudents.Tables.Refresh
    Application.RefreshDatabaseWindow
    
    Set colFirstName = Nothing
    Set tblStudents = Nothing
    Set catStudents = Nothing
End Sub

Creating Text-Based Fields in the ADOX Library

The ADOX library supports the text-based columns with a data type named adVarWChar. This is equivalent to the Text data type of Microsoft Access. To support data types in ADOX, the Column class of the ADOX library is equipped with a property named Type. Therefore, to specify a text type for a column, access its Type property and assign adWChar or adVarWChar to it. Here is an example:

Private Sub cmdTableCreation_Click()
    Dim catStudents As ADOX.Catalog
    Dim tblStudents As ADOX.Table
    Dim colFirstName As ADOX.Column
    
    Set catStudents = New ADOX.Catalog
    catStudents.ActiveConnection = CurrentProject.Connection
    
    Set tblStudents = New ADOX.Table
    tblStudents.Name = "Students"
    
    Set colFirstName = New ADOX.Column
    colFirstName.Name = "FirstName"
    colFirstName.Type = adVarWChar
    tblStudents.Columns.Append colFirstName
    
    catStudents.Tables.Append tblStudents
    catStudents.Tables.Refresh
    Application.RefreshDatabaseWindow
    
    MsgBox "A table named Students has been created."

    Set colFirstName = Nothing
    Set tblStudents = Nothing
    Set catStudents = Nothing
End Sub

Setting the Field Size in the ADOX Library

To support the size of a column, the Column class of the ADOX library is equipped with a property named DefinedSize. To specify the size of a column, assign the desired integral value to this property. Here is an example:

Private Sub cmdTableCreation_Click()
    Dim catStudents As ADOX.Catalog
    Dim tblStudents As ADOX.Table
    Dim colFirstName As ADOX.Column
    
    Set catStudents = New ADOX.Catalog
    catStudents.ActiveConnection = CurrentProject.Connection
    
    Set tblStudents = New ADOX.Table
    tblStudents.Name = "Students"
    
    Set colFirstName = New ADOX.Column
    colFirstName.Name = "FirstName"
    colFirstName.Type = adVarWChar
    colFirstName.DefinedSize = 40
    tblStudents.Columns.Append colFirstName
    
    catStudents.Tables.Append tblStudents
    catStudents.Tables.Refresh
    Application.RefreshDatabaseWindow
    
    MsgBox "A table named Students has been created."

    Set colFirstName = Nothing
    Set tblStudents = Nothing
    Set catStudents = Nothing
End Sub

Creating a Memo Field in ADOX

To create a memo field in ADOX, apply the adLongVarWChar data type to the column. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim catCountries As ADOX.Catalog
    Dim tblCountries As ADOX.Table
    Dim colName As ADOX.Column
    Dim colDescription As ADOX.Column
    
    Set catCountries = New ADOX.Catalog
    catCountries.ActiveConnection = CurrentProject.Connection
    
    Set tblCountries = New ADOX.Table
    tblCountries.Name = "Countries"
    
    Set colName = New ADOX.Column
    colName.Name = "FirstName"
    colName.Type = adWChar
    tblCountries.Columns.Append colName
    
    Set colDescription = New ADOX.Column
    colDescription.Name = "Description"
    colDescription.Type = adLongVarWChar
    tblCountries.Columns.Append colDescription
    
    catCountries.Tables.Append tblCountries
    catCountries.Tables.Refresh
    Application.RefreshDatabaseWindow
    
    MsgBox "A table named Countries has been created."

    Set colName = Nothing
    Set colDescription = Nothing
    Set tblCountries = Nothing
    Set catCountries = Nothing
End Sub

Binary Fields in ADOX

To create a binary column in ADOX, use the adBinary, the adVarBinary, the adLongVarBinary data type.

Creating a Boolean Field in the ADOX Library

To support Boolean fields, access the Type property of the Column class and assign adBoolean to it. Here is an example:

Private Sub cmdTableCreation_Click()
    Dim catEmployees As ADOX.Catalog
    Dim tblEmployees As ADOX.Table
    Dim colFullName As ADOX.Column
    Dim colIsMarried As ADOX.Column
    
    Set catEmployees = New ADOX.Catalog
    catEmployees.ActiveConnection = CurrentProject.Connection
    
    Set tblEmployees = New ADOX.Table
    tblEmployees.Name = "Employees10"
    
    Set colFullName = New ADOX.Column
    colFullName.Name = "FullName"
    colFullName.Type = adVarWChar
    colFullName.DefinedSize = 40
    tblEmployees.Columns.Append colFullName
    
    Set colIsMarried = New ADOX.Column
    colIsMarried.Name = "Is Married?"
    colIsMarried.Type = adBoolean
    tblEmployees.Columns.Append colIsMarried
    
    catEmployees.Tables.Append tblEmployees
    catEmployees.Tables.Refresh
    Application.RefreshDatabaseWindow
    
    MsgBox "A table named Employees has been created."

    Set colFullName = Nothing
    Set colIsMarried = Nothing
    Set tblEmployees = Nothing
    Set catEmployees = Nothing
End Sub

Columns Maintenance

Adding a New Column

To append a column to a table using ADOX, you can first declare a variable of type ADOX.Column. Then, define the column as necessary. Before adding the new column, get a reference to the table it will belong to. To add the new column, call the Append() method of the Columns collection of the table. Here is an example:

Private Sub cmdAddColumn_Click()
    Dim catStudents As ADOX.Catalog
    Dim tblStudents As ADOX.Table
    Dim colEmailAddress As ADOX.Column
    
    Set catStudents = New ADOX.Catalog
    catStudents.ActiveConnection = CurrentProject.Connection
    
    Set tblStudents = catStudents.Tables("Students")
    
    Set colEmailAddress = New ADOX.Column
    colEmailAddress.Name = "EmailAddress"

    tblStudents.Columns.Append colEmailAddress

    Set colEmailAddress = Nothing
    Set tblStudents = Nothing
    Set catStudents = Nothing
End Sub

Deleting a Column

To support the ability to delete a column from a table, the Columns collection is equipped with a method named Delete. To call this method, identify the table in which the column exists, access its Columns property, call its Delete() method and pass the name of the table as its index. Here is an example:

Private Sub cmdDeleteColumn_Click()
    Dim catStudents As ADOX.Catalog
    Dim tblStudents As ADOX.Table
    
    Set catStudents = New ADOX.Catalog
    catStudents.ActiveConnection = CurrentProject.Connection
    
    catStudents.Tables("Students").Columns.Delete "EmailAddress"
    
    MsgBox "A column named EmailAddress has been removedd from the Students table."

    Set tblStudents = Nothing
    Set catStudents = Nothing
End Sub

Creating Integral Fields in ADOX

The ADOX library supports integral values of various types. To create a field that will support long integers, use the adBigInt data type.