Extended ADO - Lesson 56: ADO Extended (ADOX)

	ADO Extended (ADOX)

ADOX Fundamentals

Introduction

Microsoft ADOX: Microsoft ActiveX Data Object Extensions for Data Definition Language and Security, also called ADOX, is an addition to ADO. Besides many of the ADO operations it can perform, you can use it for additional assignments such as creating a database.

The ADO library by itself has some limitations in the areas of security or the ability to create a database. In fact, and as you may know already, you cannot create a table in ADO. To address the limiting issues of ADO, 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. 6.0 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 allocate 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 and screen 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 <= 2003 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

If you are dealing with a Microsoft Access >= 2007 database, 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.

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 2013 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

Opening a Database

To open a database in ADOX, you create a connection to that database. This means that you must specify a connection to the database. To support this, the ADOX.Catalog class is equipped with a property named ActiveConnection. The easiest connection is one you create on the current database. To create it, you can simply assign CurrentProject.Connection to the ADOX.Catalog.ActiveConnection object. You can create a connection directly on an ADOX.Catalog object. Here is an example:

Private Sub cmdOpenADOXDatabase_Click()
    Dim catEmployees As ADOX.Catalog
    
    Set catEmployees = New ADOX.Catalog

    catEmployees.ActiveConnection = CurrentProject.Connection
    
    . . . Use the database

    Set catEmployees = Nothing
End Sub

You can also create a connection to a database on another folder. You must assign a string to the ADOX.Catalog.ActiveConnection property. The string must contain a connection string that follows the same description given for ADO.Here is an example that creates a connection to a database that exists in a certain folder:

Private Sub cmdOpenADOXDatabase_Click()
    Dim catEmployees As ADOX.Catalog

    Set catEmployees = New ADOX.Catalog
    catEmployees.ActiveConnection = "Provider='Microsoft.ACE.OLEDB.15.0';Data Source='C:\Databases\Exercise1.accdb';"
    
    . . . Use the database

    Set catEmployees = Nothing
End Sub

ADOX and Tables

Creating a Table

The ADOX library supports tables through a class named Table. 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
End Sub

Eventually, after using the ADOX.Table object, to reclaim the memory it was using, assign Nothing to it. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim tblStudents As ADOX.Table
    
    Set tblStudents = New ADOX.Table
    
    . . . Use the table . . .

    Set tblStudents = 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. To start a table, assign a string 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 named Column. 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 tblStudents = Nothing
    Set catStudents = Nothing
End Sub

After using an ADOX.Column variable, to get the memory it was using, assign Nothing to it. 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 class 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

Introduction to Data Types in ADOX

The second most important aspect of a column is the type of value it can hold. To support data types, the ADOX library provides the DataTypeEnum enumeration. 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 the desired type to this property.

Text-Based Fields

The ADOX library supports the text-based columns with the following data types:

adVarWChar: This is equivalent to the Text data type of Microsoft Access. Here is an example of applying it to a new column being created:

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

adWChar: This is another type used for strings

Setting the Field Size of a String-Based Column

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 Long Text Field

To create a long text 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

Integer-Based Columns

The ADOX library supports natural numeric values as follows:

adInteger: This is used for relative large numbers that can fit in 4 bytes. Here is an example of applying this type to a column:

Private Sub cmdCreateTable_Click()
    Dim catStudents As ADOX.Catalog
    Dim tblStudents As ADOX.Table
    Dim colStudent As ADOX.Column
    
    Set catStudents = New ADOX.Catalog
    catStudents.ActiveConnection = CurrentProject.Connection
    
    Set tblStudents = New ADOX.Table
    tblStudents.Name = "Students"
    
    Set colStudent = New ADOX.Column
    colStudent.Name = "StudentNumber"
    colStudent.Type = adInteger
    tblStudents.Columns.Append colStudent
    
    Set colStudent = New ADOX.Column
    colStudent.Name = "FirstName"
    colStudent.DefinedSize = 25
    colStudent.Type = adWChar
    tblStudents.Columns.Append colStudent
    
    Set colStudent = New ADOX.Column
    colStudent.Name = "LastName"
    colStudent.DefinedSize = 25
    colStudent.Type = adWChar
    tblStudents.Columns.Append colStudent
    
    catStudents.Tables.Append tblStudents
    catStudents.Tables.Refresh
    Application.RefreshDatabaseWindow
    
    MsgBox "A table named Students has been created."

    Set colStudent = Nothing
    Set tblStudents = Nothing
    Set catStudents = Nothing
End Sub

adBigInt: This is used for large integers that can fit in 8 bytes
If the column will hold small numbers, apply a data type named adSmallInt

Money-Based Columns

The ADOX library supports currency values with a data type named adCurrency. Heer is an example of applying it:

Private Sub cmdCreateTable_Click()
    Dim catContractors As ADOX.Catalog
    Dim tblContractors As ADOX.Table
    Dim colContractor As ADOX.Column
    
    Set catContractors = New ADOX.Catalog
    catContractors.ActiveConnection = CurrentProject.Connection
    
    Set tblContractors = New ADOX.Table
    tblContractors.Name = "Contractors"
    
    Set colContractor = New ADOX.Column
    colContractor.Name = "ContractorCode"
    colContractor.Type = adWChar
    colContractor.DefinedSize = 10
    tblContractors.Columns.Append colContractor
    
    Set colContractor = New ADOX.Column
    colContractor.Name = "FullName"
    colContractor.Type = adVarWChar
    colContractor.DefinedSize = 50
    tblContractors.Columns.Append colContractor
    
    Set colContractor = New ADOX.Column
    colContractor.Name = "HourlySalary"
    colContractor.Type = adCurrency
    tblContractors.Columns.Append colContractor
    
    catContractors.Tables.Append tblContractors
    catContractors.Tables.Refresh
    Application.RefreshDatabaseWindow
    
    MsgBox "A table named Contractors has been created."

    Set colContractor = Nothing
    Set tblContractors = Nothing
    Set catContractors = 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

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

Date-Based Columns

To support date-based values, the ADOX library provides the adDate data type. It is used for dates starting from 30 December 1899. Here is an example of applying it: :

Private Sub cmdCreateTable_Click()
    Dim catEmployees As ADOX.Catalog
    Dim tblEmployees As ADOX.Table
    Dim colEmployee As ADOX.Column
    
    Set catEmployees = New ADOX.Catalog
    catEmployees.ActiveConnection = CurrentProject.Connection
    
    Set tblEmployees = New ADOX.Table
    tblEmployees.Name = "Employees"
    
    Set colEmployee = New ADOX.Column
    colEmployee.Name = "EmployeeNumber"
    colEmployee.Type = adWChar
    colEmployee.DefinedSize = 10
    tblEmployees.Columns.Append colEmployee
    
    Set colEmployee = New ADOX.Column
    colEmployee.Name = "DateHired"
    colEmployee.Type = adDate
    tblEmployees.Columns.Append colEmployee
    
    Set colEmployee = New ADOX.Column
    colEmployee.Name = "FullName"
    colEmployee.Type = adVarWChar
    colEmployee.DefinedSize = 50
    tblEmployees.Columns.Append colEmployee
    
    Set colEmployee = New ADOX.Column
    colEmployee.Name = "HourlySalary"
    colEmployee.Type = adCurrency
    tblEmployees.Columns.Append colEmployee
    
    catEmployees.Tables.Append tblEmployees
    catEmployees.Tables.Refresh
    Application.RefreshDatabaseWindow
    
    MsgBox "A table named Employees has been created."

    Set colEmployee = Nothing
    Set tblEmployees = Nothing
    Set catEmployees = Nothing
End Sub

Floating-Point Columns

To support simple decimal values, the ADOX library provides the adSingle type. If the values of the colum will require more precision, apply a data type named adDouble.

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 to which it will belong. 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

Performing Database Operations

Data Entry

As you may be aware already, one of the limitations of ADO is that it cannot perform data entry. ADOX has the same limitation. As a result, you must use another means. You have two main options. You can use SQL. Here are examples that use the DoCmd.RunSQL() method:

Private Sub cmdCreateTable_Click()
    Dim catCourses As ADOX.Catalog
    Dim tblCourses As ADOX.Table
    Dim colCourse As ADOX.Column
    
    Set catCourses = New ADOX.Catalog
    catCourses.ActiveConnection = CurrentProject.Connection
    
    Set tblCourses = New ADOX.Table
    tblCourses.Name = "UndergraduateCourses"
    
    Set colCourse = New ADOX.Column
    colCourse.Name = "CourseCode"
    colCourse.Type = adWChar
    colCourse.DefinedSize = 8
    tblCourses.Columns.Append colCourse
    
    Set colCourse = New ADOX.Column
    colCourse.Name = "CourseName"
    colCourse.Type = adVarWChar
    colCourse.DefinedSize = 60
    tblCourses.Columns.Append colCourse
    
    Set colCourse = New ADOX.Column
    colCourse.Name = "Credits"
    colCourse.Type = adInteger
    tblCourses.Columns.Append colCourse
    
    Set colCourse = New ADOX.Column
    colCourse.Name = "StartingOn"
    colCourse.Type = adDate
    tblCourses.Columns.Append colCourse
    
    Set colCourse = New ADOX.Column
    colCourse.Name = "AvailableOnline"
    colCourse.Type = adBoolean
    tblCourses.Columns.Append colCourse
    
    catCourses.Tables.Append tblCourses
    catCourses.Tables.Refresh
    Application.RefreshDatabaseWindow
    
    MsgBox "A table named UndergraduateCourses has been created.", _
    vbOKOnly Or vbInformation, "Monson University"

    Set colCourse = Nothing
    Set tblCourses = Nothing
    Set catCourses = Nothing
End Sub

Private Sub cmdCreateCourses_Click()
    DoCmd.RunSQL "INSERT INTO UndergraduateCourses VALUES('LBRS 100', 'Library and Research', 1, #01/05/2014#, True);"
    DoCmd.RunSQL "INSERT INTO UndergraduateCourses VALUES('WRTG 201', 'Introduction to Narrative', 3, #05/25/2014#, False);"
    DoCmd.RunSQL "INSERT INTO UndergraduateCourses VALUES('BMGT 304', 'Managing E-Commerce in Organizations', 3, #01/12/2014#, Yes);"
    DoCmd.RunSQL "INSERT INTO UndergraduateCourses VALUES('CHEM 424', 'Instrumental Methods of Analysis', 4, #09/06/2014#, No);"
End Sub

Another option is to use a record set. You have various options. You can use a Microsoft Access Object Library recordset. Here is an example:

Private Sub cmdAddCourses_Click()
    Dim dbExercise As Database
    Dim rsCourses As Recordset
    
    Set dbExercise = CurrentDb
    Set rsCourses = dbExercise.OpenRecordset("UndergraduateCourses")
    
    rsCourses.AddNew
    rsCourses("CourseCode").Value = "CJLE 201"
    rsCourses("CourseName").Value = "Introduction to Investigative Forensics"
    rsCourses("Credits").Value = 3
    rsCourses("StartingOn").Value = #1/8/2014#
    rsCourses("AvailableOnline").Value = True
    rsCourses.Update
    
    MsgBox "A new course has been created.", _
            vbOKOnly Or vbInformation, "Monson University"
    
    Set rsCourses = Nothing
    Set dbExercise = Nothing
End Sub

You can also use DAO or an ADO recordset. In this case, make sure you reference both libraries:

References

Here is an example:

Private Sub cmdNewCourse_Click()
    Dim rsCourses As ADODB.Recordset
    
    Set rsCourses = New ADODB.Recordset
    
    With rsCourses
        .Open "UndergraduateCourses", _
              CurrentProject.Connection, _
              adOpenStatic, _
              adLockOptimistic
    
        If .Supports(adAddNew) Then
            .AddNew
            rsCourses("CourseCode").Value = "MATH 246"
rsCourses("CourseName").Value = "Differential Equations for Scientists and Engineers"
            rsCourses("Credits").Value = 4
            rsCourses("StartingOn").Value = #1/4/2014#
            rsCourses("AvailableOnline").Value = False
            .Update
        End If
    End With
    
    MsgBox "A new course has been added.", _
            vbOKOnly Or vbInformation, "Monson University"
    
    rsCourses.Close
    Set rsCourses = Nothing
End Sub

Data Analysis

Like ADO, ADOX does not have its own means of performing data analysis. This means that you must use something else such as SQL. Here is an example:

Private Sub cmdOnlineCourses_Click()
    RecordSource = "SELECT CourseCode, " & _
                   "       CourseName, " & _
                   "       Credits " & _
                   "FROM UndergraduateCourses " & _
                   "WHERE AvailableOnline = True"
End Sub

In the same way, you can use SQL through the DoCmd.RunSQL() method or a record set from Microsoft Access Object Library, DAO, or ADO. Here is an example:

Private Sub cmdOnlineCourses_Click()
    Dim rsCourses As ADODB.Recordset
    
    Set rsCourses = New ADODB.Recordset
    rsCourses.Open "SELECT CourseCode, " & _
                   "       CourseName, " & _
                   "       Credits " & _
                   "FROM UndergraduateCourses " & _
                   "WHERE AvailableOnline = True", _
                   CurrentProject.Connection, _
                   adOpenStatic, adLockOptimistic
    
    Set Me.Recordset = rsCourses
    
    txtTotalCourses = CStr(rsCourses.RecordCount)
    
    rsCourses.Close
    Set rsCourses = Nothing
End Sub

Undergraduate Courses


Previous	Copyright © 2000-2022, FunctionX, Inc.	Next