ADOX (ADO Extensions for DDL and Security): ADOX refers to ‘ActiveX Data Objects Extensions for Data Definition Language and Security’, and it is an extension to the ADO library in which additional objects, for creating and modifying database tables, and for security, are exposed. To use ADOX in your VBA project, you must add a reference to the ADOX Object Library in Excel (your host application) by clicking Tools-References in VBE, and then choose an appropriate version viz. Microsoft ADO Ext. x.x for DDL and Security. Note that ADO does not by itself support creating databases & tables, which is actually done with ADOX. However, you can create a database table in ADO using SQL. The ADOX Library gives access to objects, properties and methods to create, modify, and view the database and tables structure.

ADOX is best used for creating and manipulating database structure and tables, though ADO can also be used for this in limited ways (usually with SQL DDL queries). ADO is best used to access your data and for data manipulation ie. to add / edit / delete records.

Create an Access Database using ADOX

The Catalog Object is the top object (also referred as the root object) in the ADOX Library and it contains all objects of Tables (includes fields, indexes and keys), Views, Users, Groups and stored Procedures in respect of a Database. You can add, delete or modify objects contained in the Catalog object. You can create a new Database with ADOX, using the Create method of the Catalog object.

ADOX Create Method. Syntax: Catalog.Create ConnectString. With this method, a new ADO Connection is opened to the data source, as specified in the ConnectString. The ConnectString argument is a string value (a series of values) which contains information for connecting to a data source. The “Provider” keyword specifies the OLE DB provider name to be used for the connection; the “Data Source” keyword specifies the path and name of the database to which you are connecting.

Set the ActiveConnection property to an Open Connection to Access all Catalog Objects:

The Catalog ActiveConnection property indicates the connection to the database or the catalog. To enable access to all the catalog objects, set the Catalog ActiveConnection property to a valid & open connection, which can either be a ‘connection object’, or it can be a ‘connection string’ which is a string value containing information for connecting to a data source.

Example 1: Create a New Access Database, using ADOX. This code creates a new Access file (.accdb), but no Database Tables are added.

Sub adoxAutomateAccess_1()
‘Create a New Access Database, with ADOX, using the Create method of the Catalog object.

‘To use ADOX in your VBA project, you must add a reference to the ADOX Object Library in Excel (your host application) by clicking Tools-References in VBE, and then choose an appropriate version viz. Microsoft ADO Ext. x.x for DDL and Security.

Dim strMyPath As String, strDBName As String, strDB As String
Dim strSQL As String

‘instantiate an ADOX Catalog object using Dim with the New keyword:
Dim adoxCat As New ADOX.catalog

‘set the Access File name – the MS Access Database Name:
strDBName = “SalesReportNew.accdb”
‘set path / location of the database, to be in the same location as the host workbook:
strMyPath = ThisWorkbook.Path
‘set the string variable to the Database:
strDB = strMyPath & “\” & strDBName

‘Create a new Database with ADOX, using the Create method of the Catalog object.
‘Connect to a data source:
‘For pre – MS Access 2007, .mdb files (viz. MS Access 97 up to MS Access 2003), use the Jet provider: “Microsoft.Jet.OLEDB.4.0”. For Access 2007 (.accdb database) use the ACE Provider: “Microsoft.ACE.OLEDB.12.0”. The ACE Provider can be used for both the Access .mdb & .accdb files.
adoxCat.Create ConnectString:=“Provider = Microsoft.ACE.OLEDB.12.0; data source=” & strDB

‘destroy the object variable:
Set adoxCat = Nothing

End Sub

ADOX Table Object & Tables Collection – Properties & Methods

ADOX Table Object

A Table object is a database table. A Table contains columns, indexes (an Index specifies the order of accessing records and is created on one or multiple table columns), and keys (Key is a means to identify records in a table, wherein data can be related by using one or more columns as key columns).

ADOX Table object Properties:

Not all properties of the Table object might be supported by your data provider. Commonly used properties for the Table object are explained below.

ADOX Name Property – read/write property and becomes read-only after table is appended. Name property is a string value and sets or returns the Table name. Names need not be unique in the Tables collection, and default value is an empty string.

The ADOX Table Type Property is a read-only property, set automatically by ADOX. It is a string value and returns the Table’s type (ex. “TABLE”, “SYSTEM TABLE”, …). The Table type “TABLE” indicates a standard table – it is the table type when you (ie. user) create a new table.

The ADOX DateCreated Property returns the date of creation of a Table. The ADOX DateModified Property returns the date when the Table was last modified. Note that a null value is returned if these properties are not supported by the provider.

The ADOX ParentCatalog Property (of a table object) is used to specify the parent catalog of the Table by which the provider-specific properties can be accessed. This has been discussed in detail later for the Column object. Also discussed in separate sections later is the Indexes Collection (which refers to all Indexes of a Table) and the Keys Collection (which refers to all Key objects of a Table).

Additional Table properties ie. provider-specific properties, can be specified when creating tables

ADO objects have two types of properties, both standard ADO properties and provider-specific properties. Properties exposed by the OLE DB provider itself are called provider-specific and these are not otherwise exposed as standard ADO properties. These provider-specific properties are exposed in the Properties collection (which is made up of Property objects where each provider-specific property appears as a Property object) of the respective ADO object. Here we refer to the Properties Collection of the Table Object. We are not discussing these properties in detail here, however the ‘the Properties Collection of the Column Object’ has been discussed under the Column object properties section.

ADOX Tables Collection

Tables collection refers to all Table objects (ie. all types of tables) in the database, including linked tables and system tables.

ADOX Tables Collection Properties and Methods:

ADOX Tables Collection Properties and Methods are explained below. Except the ADOX Tables Append Method which is specific to ADOX, other properties and methods are same as applicable to ADO Collections.

To add or append a new Table to the Tables Collection, use the ADOX Tables Append Method. Syntax: Tables.Append Table. Table argument refers to the Table object or Table name.

Use the ADO Count property to determine the number of Tables (or any objects) in a collection, , wherein numbering for members of a collection begins with zero.

You can access a specific Table (or any object) in a collection by the ADO Item property by refering to the table name or its position, viz. collection.Item(index), where index is the Table Name or its ordinal position. Note that the Item property is the default property for all collections, so omitting the Item keyword will have no effect and you can also use the syntax: collection (index).

To delete a database table (or any object) use the ADOX Collection Delete Method. Syntax: Collection.Delete Name. Name is the object’s name or ordinal position.

Use the ADO Refresh Method to update Tables (or any objects) in a collection viz. collection.Refresh, to reflect objects specific to the provider.

ADOX Column Object & Columns Collection – Properties & Methods

A Column Object is a column in a database table (or a column in an index or key) and Columns Collection contains all the column objects. A Field object corresponds to a Recordset column wherein the column data is of the same data type.

ADOX Columns Append Method

To add or append a new column to the Columns Collection, use the ADOX Columns Append Method. Syntax: ColumnsCollection.Append Column, Type, DefinedSize. The Column argument is mandatory and you can specify the column name therein. Type argument indicates the field’s data type and equates to the Column’s Type property. DefinedSize sets or returns a long value specifying the maximum column size / length in characters. Both Type and DefinedSize arguments are optional.

ADOX Column Properties

Not all properties of the Column object might be supported by your data provider. Commonly used properties for the column object are explained below.

ADOX Name Property – read/write property. Name property is a string value, and sets or returns the Column name. Names need not be unique in the Columns collection, and default value is an empty string.

ADOX Type Property – read/write property and becomes read-only after column is appended. Sets the column’s data type.

Examples of data type enumerated constants that are supported by ADOX, for the Type property, include:

adBoolean (Boolean Value – Yes/No); adChar (String Value); adCurrency (Currency Value); adDate (date value – number of days since December 30, 1899 + the fraction of a day); adDBDate (date value – yyyymmdd); adDecimal (an exact numeric value with a fixed precision and scale); adDouble (a double-precision floating-point value); adGUID (FieldSize = Replication ID); adInteger (a four-byte signed integer); adLongVarChar (Long String Value); adNumeric (an exact numeric value with a fixed precision and scale); adSingle (a single-precision floating-point value); adVarWChar (a null-terminated Unicode character string); …

Default data type of a column is a value of 202 (adVarWChar). Data Types of adGUID and adInteger can be used for AutoNumber also.

ADOX Attributes Property – value of 1 (adColFixed) indicates a fixed-length column and value of 2 (adColNullable) allows null values (ie. empty string) for a column. Default value is 0 ie. neither adColFixed or adColNullable. If case of a fixed-length column, the entered text, even if shorter, will be set as equal to the field’s maximum length (the field becomes padded with spaces upto the maximum length). To set a text field as adColFixed, use adWChar data type instead of the variable length version adVarWChar.

ADOX DefinedSize Property – read/write property and becomes read-only after column is appended. Sets or returns the maximum column size or length in characters, specified as a long value.

ADOX NumericScale Property – read/write property and becomes read-only after column is appended. Works only for column data types of adNumeric or adDecimal. Sets or returns a Byte value indicating the scale of a column’s numeric value. It determines the number of digits to the right of the decimal point for a numeric value (this is what a scale of a number refers to). The default value is 0 (zero).

ADOX Precision Property – read/write property and becomes read-only after column is appended. Works only for numeric type columns. Sets or returns a Long value indicating the maximum precision of column’s data values ie. the maximum number of digits for column values. The default value is 0 (zero).

ADOX RelatedColumn Property – read/write property and becomes read-only after column is appended. Sets or returns a String value specifying the name of the related column in the table which is being related, default being an empty string.

ADOX SortOrder Property – Sets or returns the indexed column’s sort order. Value of 1 (adSortAscending) indicates the column is sorted in an ascending sort order, and is also the default. In this case an ascending index is appended to the Country column and the records are displayed in ascending order. The value of 2 (adSortDescending) sets a descending sort order.

ADOX ParentCatalog Property – Use the ADOX ParentCatalog Property (of a column object) to specify the parent catalog of the Column by which the provider-specific properties can be accessed. The ParentCatalog property can be set on the Column object only before it is appended to the columns collection. Note that the ADOX ParentCatalog Property can also be used to specify the parent catalog of a Table or User object, like it is used for a Column object.

Additional Column properties ie. provider-specific properties

To use additional column properties (ie. provider-specific properties), the Column object must be associated with a provider. You can specify additional column property values at the time of creation (viz. when appending a column to its Catalog collection), by using the ADOX ParentCatalog Property. Examples of these properties are mentioned below.

Autoincrement Property – a Boolean value to determine whether the field’s value will increment automatically when a new record is added.

Seed Property – It specifies the number from which an AutoNumber field will start. Setting the Seed property to 10 and Increment property to 2 will start the AutoNumber from 10, followed by 12, and so on. You are required to use the vba CLng function while specifying the number, which converts the value to a Long Integer ie. CLng(n) where n is the specified number.

Increment Property – It specifies an invremental value by which your AutoNumber field will increase when a new record is added. Setting the Seed property to 10 and Increment property to 2 will start the AutoNumber from 10, followed by 12, and so on. You are required to use the vba CLng function while specifying the incremental value, which converts the value to a Long Integer ie. CLng(n) where n is the incremental value.

Nullable Property – a Boolean value to allow or disallow a field to accept null values.

Default Property – specifies a default value for a field (string values should be enclosed within double quotes).

Description Property – specifies a field’s description. Use this optional property to help describing a field. When you view the Table in Access, in Design View, this is the Description field next to the Field Name and Data Type.

Fixed Length Property – a Boolean value which determines whether a field is fixed or variable length. If the field is fixed-length, the entered text even if shorter will be set as equal to the field’s maximum length (the field becomes padded with spaces upto the maximum length). In a variable-length field the field length of a Text field will be limited to the length of the entered text if shorter than the field’s maximum length.

Jet OLEDB:Allow Zero Length – a Boolean value, this property determines whether zero-length values for string data types are allowed in a field. For Jet database engine this property is distinct from a Null value.

Jet OLEDB:AutoGenerate – a Boolean value, this property determines automatic generation of a globally unique identifier (GUID) in a field whenever a new record is added.

Jet OLEDB:Column Validation Rule – it validates a field’s value, before it is set, with a specified rule or condition. The property specifies the rule or condition as a string value.

Jet OLEDB:Column Validation Text – specifies the error message (a string value) which gets displayed if the field’s value does not conform to the specified rule or condition.

Jet OLEDB:Hyperlink – a boolean value which determines whether the field will store hyperlinks. The field’s data type should be adLongVarWChar ie. a Memo field.

Example 2: Add a new table and add new columns in a MS Access database, using ADOX.

Refer to Image 1, as mentioned in the code.

Sub adoxAutomateAccess_2()
‘add a new table and add new columns in a MS Access database, using ADOX.
‘refer Image 1 which shows the new table named “SalesManager” and all its columns in the Access database.

‘To use ADOX in your VBA project, you must add a reference to the ADOX Object Library in Excel.

‘To use ADO in your VBA project, you must add a reference to the ADO Object Library in Excel.

Dim strMyPath As String, strDBName As String, strDB As String
Dim adoxTbl As ADOX.Table
Dim adoxColumn As ADOX.Column
Dim adoConn As ADODB.Connection
Dim i As Long

‘instantiate an ADOX Catalog object using Dim with the New keyword:
Dim catAdox As New ADOX.catalog

‘your data source with which to establish connection – ENTER the MS Access Database Name:
strDBName = “SalesReportNew.accdb”
‘get path / location of the database, presumed to be in the same location as the host workbook:
strMyPath = ThisWorkbook.Path
‘set the string variable to the Database:
strDB = strMyPath & “\” & strDBName

‘—————
‘set the ActiveConnection property to a connection string:
‘For pre – MS Access 2007, .mdb files (viz. MS Access 97 up to MS Access 2003), use the Jet provider: “Microsoft.Jet.OLEDB.4.0”. For Access 2007 (.accdb database) use the ACE Provider: “Microsoft.ACE.OLEDB.12.0”. The ACE Provider can be used for both the Access .mdb & .accdb files.
catAdox.ActiveConnection = “Provider = Microsoft.ACE.OLEDB.12.0; data source=” & strDB

‘search tables collection for an existing table named “SalesManager”, and delete if found:

For Each adoxTbl In catAdox.Tables

If adoxTbl.Name = “SalesManager” Then

catAdox.Tables.Delete adoxTbl.Name

End If

‘instantiate an ADOX Table object using Dim with the New keyword:
Dim adoxTable As New ADOX.Table

‘set name of the new table you are going to add:
adoxTable.Name = “SalesManager”

‘—————
‘add columns of various data types, using ADOX:

With adoxTable.Columns

.Append “SNo”, adInteger
.Append “EmployeeId”, adInteger
.Append “FirstName”, adWChar
.Append “LastName”, adVarWChar, 25
.Append “JoinDate”, adDate
.Append “Sales”, adNumeric

.Append “Rating”, adLongVarWChar

End With

‘set properties for “SNo” field:

With adoxTable.Columns.Item(“SNo”)

‘Set the ParentCatalog property before you set field properties. This will enable access to the provider-specific properties of AutoIncrement, Seed and Increment. These three properties define “SNo” field as an AutoNumber type.
Set .ParentCatalog = catAdox
‘the field’s value will increment automatically when a new record is added:
.Properties(“AutoIncrement”) = True
‘specify description for a field:
.Properties(“Description”) = “Serial number is an AutoNumber, and is auto-incremented for each record.”
‘Specify the number from which an AutoNumber field will start. You are required to use the vba CLng function while specifying the number, which converts the value to a Long Integer.
.Properties(“Seed”) = CLng(10)
‘Specify an invremental value by which your AutoNumber field will increase when a new record is added. You are required to use the vba CLng function while specifying the number, which converts the value to a Long Integer.

.Properties(“Increment”) = CLng(1)

End With

‘set properties for “FirstName” field:

With adoxTable.Columns.Item(“FirstName”)

‘Set the ParentCatalog property before you set field properties. This will enable access to the provider-specific properties.
Set .ParentCatalog = catAdox
‘allow field not to accept null values – a value will be required to be entered in the field:
.Properties(“Nullable”) = False
‘disallow zero-length values for a field with string data type.
.Properties(“Jet OLEDB:Allow Zero Length”) = False
‘set the field as fixed-length – the entered text even if shorter will be set as equal to the field’s maximum length:

.Properties(“Fixed Length”) = True

End With

‘set properties for “LastName” field:

With adoxTable.Columns.Item(“LastName”)

‘Set the ParentCatalog property before you set field properties. This will enable access to the provider-specific properties.
Set .ParentCatalog = catAdox
‘allow null values (ie. empty string) for the column:

.Attributes = adColNullable

End With

‘set properties for “JoinDate” field:

With adoxTable.Columns.Item(“JoinDate”)

Set .ParentCatalog = catAdox
‘specify a default value for the field:
.Properties(“Default”) = “#04/01/2010#”
‘validate the field’s value, before it is set, with a specified rule or condition:
.Properties(“Jet OLEDB:Column Validation Rule”) = “>=#04/01/2010# and <=date()”
‘specify the error message which gets displayed if the field’s value does not conform to the specified rule or condition:

.Properties(“Jet OLEDB:Column Validation Text”) = “JoinDate should be on or after 04/01/2010 but within current date”

End With

‘set properties for “Sales” field:

With adoxTable.Columns.Item(“Sales”)

‘set maximum number of digits for column values:
.Precision = 6
‘determine the number of digits to the right of the decimal point:

.NumericScale = 1

End With

‘set properties for “Rating” field:

With adoxTable.Columns.Item(“Rating”)

Set .ParentCatalog = catAdox
‘enable the field to store hyperlinks:

.Properties(“Jet OLEDB:Hyperlink”) = True

End With

‘save the new table by appending to the catalog tables collection:
catAdox.Tables.Append adoxTable

‘—————
‘ADOX is best used for creating and manipulating database structure and tables, though ADO can also be used for this in limited ways (usually with SQL DDL queries). ADO is best used to access your data and for data manipulation ie. to add / edit/ delete records.

‘set an ADO connection to database:
Set adoConn = catAdox.ActiveConnection

‘Populate the table using SQL INSERT INTO statements, with the Execute method on the ADO connection object:
adoConn.Execute “INSERT INTO SalesManager (EmployeeId, FirstName, LastName, JoinDate, Sales, Rating) VALUES (256, ‘Mary’, ‘Lange’, ‘7/24/2011’, 15678.58, ‘#http://www.google.com#’)”
adoConn.Execute “INSERT INTO SalesManager (EmployeeId, FirstName, LastName, Sales, Rating) VALUES (587, ‘Harry’, ‘Davis’, 14673.26, ‘#http://www.google.com#’)”
adoConn.Execute “INSERT INTO SalesManager (EmployeeId, FirstName, LastName, JoinDate, Sales, Rating) VALUES (01, ‘James’, ‘Bond’, ‘2/11/2013’, 12589, ‘#http://www.yahoo.com#’)”
‘note that null values are allowed in the “LastName” field:
adoConn.Execute “INSERT INTO SalesManager (EmployeeId, FirstName, Sales, Rating) VALUES (445, ‘John’, 12432.20, ‘#http://www.yahoo.com#’)”
adoConn.Execute “INSERT INTO SalesManager (EmployeeId, FirstName, LastName, JoinDate, Sales, Rating) VALUES (25, ‘Dane’, ‘Large’, ’10/3/2012′, 9876.5, ‘#http://www.google.com#’)”

‘—————
‘close the object:
adoConn.Close

‘destroy the object variables:
Set adoxTable = Nothing
Set catAdox = Nothing
Set adoConn = Nothing

End Sub

Example 3: Reference & Delete Database Tables and Columns; Reference & Edit Field Properties; using ADOX.

Refer to Image 2, as mentioned in the code. In this example we show how to:

Refer to a Database Table by Name and Return all its Columns

Return all Tables and Columns in Database

Refer/List Field Properties; Edit Field Properties

Rename a Database Table; Rename a Field

Delete a Database Table; Delete a Field

Sub adoxAutomateAccess_3()
‘Reference & Delete Database Tables and Columns; Reference & Edit Field Properties; using ADOX.
‘Refer Image 2 which shows the Access Database named “SalesReport.accdb” which has 2 tables named “SalesManager” and “Performance”, on which the following codes have been executed.

‘To use ADOX in your VBA project, you must add a reference to the ADOX Object Library in Excel.

‘—————
‘DIM STATEMENTS, INSTANTIATE CATALOG OBJECT, SET ACTIVE CONNECTION TO A DATA SOURCE:

Dim strMyPath As String, strDBName As String, strDB As String
Dim i As Long, n As Long
Dim adoxTable As ADOX.Table
Dim adoxColumn As ADOX.Column
Dim adoxProperty As ADOX.Property

‘instantiate an ADOX Catalog object using Dim with the New keyword:
Dim catAdox As New ADOX.catalog

‘your data source with which to establish connection – ENTER the MS Access Database Name:
strDBName = “SalesReport.accdb”
‘get path / location of the database, presumed to be in the same location as the host workbook:
strMyPath = ThisWorkbook.Path
‘set the string variable to the Database:
strDB = strMyPath & “\” & strDBName

‘set the ActiveConnection property to a connection string:
‘For pre – MS Access 2007, .mdb files (viz. MS Access 97 up to MS Access 2003), use the Jet provider: “Microsoft.Jet.OLEDB.4.0”. For Access 2007 (.accdb database) use the ACE Provider: “Microsoft.ACE.OLEDB.12.0”. The ACE Provider can be used for both the Access .mdb & .accdb files.
catAdox.ActiveConnection = “Provider = Microsoft.ACE.OLEDB.12.0; data source=” & strDB

‘—————
‘REFER TO A DATABASE TABLE BY NAME AND RETURN ALL ITS COLUMNS:

‘refer to a database table by name:
‘Set adoxTable = catAdox.Tables.Item(“Performance”)
‘alternate:
Set adoxTable = catAdox.Tables(“Performance”)

‘return name of the table:
MsgBox adoxTable.Name

‘count number of columns in the table:
MsgBox adoxTable.Columns.count

‘return names of all columns from the table:
For Each adoxColumn In adoxTable.Columns
MsgBox “Column Name: ” & adoxColumn.Name
Next

Set adoxTable = Nothing

‘—
‘RETURN ALL TABLES AND COLUMNS IN DATABASE:

‘return count of tables of all types, including linked tables and system tables:
MsgBox catAdox.Tables.count

‘return all tables and columns in database – search only standard tables ie. table type “TABLE”:
i = 0

For Each adoxTable In catAdox.Tables

If adoxTable.Type = “TABLE” Then

i = i + 1

‘return all columns from each table:

For n = 0 To adoxTable.Columns.count – 1

MsgBox “Column Name: ” & adoxTable.Columns(n).Name & “, in Table: ” & adoxTable.Name

Next n

End If

‘return total number of standard tables:
MsgBox “Total number of standard tables is: ” & i

Set adoxTable = Nothing

‘—
‘REFER/LIST FIELD PROPERTIES; EDIT FIELD PROPERTIES:

‘Refer / List Field Properties:

‘refer to a database table by name:
Set adoxTable = catAdox.Tables.Item(“SalesManager”)

‘list details of all properties of each column in the “SalesManager” Table:

For Each adoxColumn In adoxTable.Columns

For Each adoxProperty In adoxColumn.Properties

MsgBox “Column Name: ” & adoxColumn & “; Property Name: ” & adoxProperty.Name & “, Property Attributes: ” & adoxProperty.Attributes & “, Property Type: ” & adoxProperty.Type & “, Property Value: ” & adoxProperty.Value

‘set column in the “SalesManager” Table:
‘Set adoxColumn = adoxTable.Columns(“FirstName”)
‘alternate:
Set adoxColumn = adoxTable.Columns.Item(“FirstName”)

‘refer properties of a specific column (“FirstName”) in the “SalesManager” Table.
‘return name of the column:
MsgBox adoxColumn.Name
‘default data type of a column is a value of 202 (adVarWChar).
MsgBox adoxColumn.Type
‘returns the maximum column length in characters:
MsgBox adoxColumn.DefinedSize
‘returned value is 2 for adColNullable (column can have null values), and 1 for adColFixed (column is fixed length):
MsgBox adoxColumn.Attributes

‘Edit Field Properties:

‘Edit Column Properties – defined size and type properties are read-only after column is appended, and hence cannot be edited later.
‘There seems to be a problem in editing of “Nullable” & “Attributes” properties with ADOX and it is best to use DDL SQL statements for nullable or fixed length column.

‘Edit “Allow Zero Length” Property value of “FirstName” column:
Set adoxProperty = adoxColumn.Properties(“Jet OLEDB:Allow Zero Length”)
MsgBox “Property Name: ” & adoxProperty.Name & “, Value: ” & adoxProperty.Value
adoxProperty.Value = False
MsgBox adoxProperty.Value

‘Edit “Description” property of “FirstName” column:
adoxColumn.Properties(“Description”) = “Enter First Name of the Employee.”
‘Edit Validation Rule, Validation Text & Default value properties of “JoinDate” column:
adoxTable.Columns(“JoinDate”).Properties(“Jet OLEDB:Column Validation Rule”) = “>=#04/01/2010# and <=date()”
adoxTable.Columns(“JoinDate”).Properties(“Jet OLEDB:Column Validation Text”) = “JoinDate should be on or after 04/01/2010 but within current date”
adoxTable.Columns(“JoinDate”).Properties(“Default”) = “#4/1/2010#”

Set adoxTable = Nothing
Set adoxColumn = Nothing

‘—
‘RENAME A DATABASE TABLE, RENAME A FIELD:

‘refer to a database table by name:
Set adoxTable = catAdox.Tables.Item(“SalesManager”)
‘set column in the “SalesManager” Table:
Set adoxColumn = adoxTable.Columns(“FirstName”)

‘change column name from “FirstName” to “FName”:
adoxColumn.Name = “FName”
MsgBox adoxColumn.Name

‘rename a database table, using ADOX:
adoxTable.Name = “SlsMgr”
MsgBox adoxTable.Name
‘check the table modified date:
MsgBox adoxTable.DateModified

Set adoxTable = Nothing
Set adoxColumn = Nothing

‘—
‘DELETE A DATABASE TABLE, DELETE A FIELD:

‘refer to a database table by name:
Set adoxTable = catAdox.Tables.Item(“SlsMgr”)

‘Delete the “FName” field from the “SlsMgr” Table. Note that the table name was changed from “SalesManager” to “SlsMgr” and column name was changed from “FirstName” to “FName”.
adoxTable.Columns.Delete (“FName”)

Set adoxTable = Nothing

‘delete the database table named “SlsMgr”:
catAdox.Tables.Delete “SlsMgr”

‘search tables collection for an existing table named “Performance”, and delete if found:

For Each adoxTable In catAdox.Tables

If adoxTable.Name = “Performance” Then

catAdox.Tables.Delete adoxTable.Name

End If

‘————
‘destroy the Catalog object variable:
Set catAdox = Nothing

End Sub

Create an Index using ADOX

An Index specifies the order of accessing records and can disallow duplicate values in a field. Index enables quick search of records in a table, and it can be created on one or multiple table columns. Key is a means to identify records in a table, wherein data can be related by using one or more columns as key columns. Primary key can be specified as one or more columns having unique values, and cannot be Null. There can be only one Primary key in a Table.

ADOX Indexes Collection:

Indexes Collection refers to all Index objects of a Table.

ADOX Indexes Collection Properties and Methods:

To add or append a new Index to the Indexes collection, use the ADOX Indexes Collection Append Method. Syntax: Indexes.Append Index, Columns. Index argument is a reference to the Index object. Columns argument refers to the column name and is an optional argument.

Except the ADOX Indexes Append Method which is specific to ADOX, properties and methods which are same as applicable to ADO Collections include: Count property, Item property, Delete method and Refresh method.

ADOX Index Object

Index object is an Index in a Table. Not all properties of the Index object might be supported by your data provider. Commonly used properties for the Index object are explained below.

ADOX Index Object Properties:

ADOX Name Property – read/write property. Name property is a string value, and sets or returns the Index name. Names need not be unique in the Index collection, and default value is an empty string.

ADOX Unique Property – read/write property and becomes read-only after Index is appended. Unique property sets or returns a Boolean value which determines if the Index keys are unique ie. duplicate values are not allowed. Default value is False.

ADOX PrimaryKey Property – read/write property and becomes read-only after Index is appended. PrimaryKey property sets or returns a Boolean value which determines if the Index is the table’s primary key. Default value is False. Primary key can be specified as one or more columns having unique values, and cannot be Null. There can be only one Primary key in a Table.

ADOX IndexNulls Property – read/write property and becomes read-only after Index is appended. Using IndexNulls Property can determine if you want to index records which have null values. Value of 0 (adIndexNullsAllow) allows indexing a null value if entered in a key column. Value of 1 (adIndexNullsDisallow), is the Default, does not allow a null value in a key column, entering which will give an error. Value of 2 (adIndexNullsIgnore) ignores a null value entered in a key column and the entry is not indexed. Value of 4 (adIndexNullsIgnoreAny) ignores a null value entered in any column for an index having a multi-column key, and the entry is not indexed.

ADOX Clustered Property – read/write property and becomes read-only after Index is appended. Clustered property sets or returns a Boolean value which determines if the Index is clustered. Default value is False. This property is not supported by Microsoft Jet databases.

Note that you can use the ADOX Columns Collection Append Method, Delete Method, Refresh Method, Count Property & Item Property to access the columns of the Index object.

Steps to Create an Index:

Step 1: Connect to the database by setting the Catalog ActiveConnection property to an open connection;

Step 2: Instantiate the ADOX index object;

Step 3: Set Index properties to define the index;

Step 4: Append column(s) to the Columns collection of the Index (Note: All column objects of a Index are contained in a Columns collection);

Step 5: Append index to the Indexes collection of the Table.

Example 4: Create Index Keys for columns in a database, using ADOX.

Refer to Images 3a, 3b, 3c & 3d, as mentioned in the code. In this example we show how to:

Create a Single-Column Index

Create a Primary Key Index

Create a Multi-Column Index

Sub adoxAutomateAccess_4()
‘create Index Keys for columns in a database, using ADOX
‘Refer Image 3a which shows the Access Database named “SalesReport.accdb” which has 2 tables named “SalesManager” and “Performance”, on which the following codes have been executed.

‘To use ADOX in your VBA project, you must add a reference to the ADOX Object Library in Excel.
‘To use ADO in your VBA project, you must add a reference to the ADO Object Library in Excel.

‘—————
‘DIM STATEMENTS, INSTANTIATE CATALOG OBJECT, SET ACTIVE CONNECTION TO A DATA SOURCE:

Dim strMyPath As String, strDBName As String, strDB As String
Dim adoxTable As ADOX.Table
Dim adoxColumn As ADOX.Column
Dim adoxIndex As ADOX.Index

‘instantiate an ADO Connection object using Dim with the New keyword:
Dim adoConn As New ADODB.Connection

‘Connect to a data source:
‘For pre – MS Access 2007, .mdb files (viz. MS Access 97 up to MS Access 2003), use the Jet provider: “Microsoft.Jet.OLEDB.4.0”. For Access 2007 (.accdb database) use the ACE Provider: “Microsoft.ACE.OLEDB.12.0”. The ACE Provider can be used for both the Access .mdb & .accdb files.
adoConn.Open ConnectionString:=“Provider = Microsoft.ACE.OLEDB.12.0; data source=” & strDB

‘instantiate an ADOX Catalog object using Dim with the New keyword:
Dim catAdox As New ADOX.catalog

‘set the ActiveConnection property to the ADO Connection object:
catAdox.ActiveConnection = adoConn

‘refer to a database table by name:
Set adoxTable = catAdox.Tables.Item(“SalesManager”)

‘————-
‘CREATE A SINGLE-COLUMN INDEX WITH UNIQUE VALUES:
‘refer Image 3b showing the “SalesManager” Table where the Index has been created on “LastName” column whose values appear in ascending order and the column cannot have duplicate values.

‘instantiate the ADOX index object:
Set adoxIndex = New ADOX.Index
‘set index name:
adoxIndex.Name = “un_LN”
‘Set index key to be unique ie. duplicate values are not allowed. Default value of Unique Property is False:
adoxIndex.Unique = True
‘Append column to the Columns collection of the Index:
adoxIndex.Columns.Append “LastName”
‘Append index to the Indexes collection of the Table:
adoxTable.Indexes.Append adoxIndex
‘destroy the index object variable:
Set adoxIndex = Nothing
‘delete the index named “un_LN” from the table:
adoxTable.Indexes.Delete “un_LN”

‘—
‘CREATE A PRIMARY KEY INDEX:
‘refer Image 3c showing the “SalesManager” Table where the Primary Index has been created on “EmployeeID” column whose values appear in ascending order and the column cannot have duplicate values.

‘search for an existing Primary Key index in the Table and delete if found:

For Each adoxIndex In adoxTable.Indexes

If adoxIndex.PrimaryKey Then

adoxTable.Indexes.Delete adoxIndex.Name

End If

Next adoxIndex

‘instantiate the ADOX index object:
Set adoxIndex = New ADOX.Index
‘set index name:
adoxIndex.Name = “pk_EI”
‘Specify index to be the primary key. Default value for the PrimaryKey Property is False.
adoxIndex.PrimaryKey = True
‘Append column to the Columns collection of the Index:
adoxIndex.Columns.Append “EmployeeID”
‘Append index to the Indexes collection of the Table:
adoxTable.Indexes.Append adoxIndex
‘destroy the index object variable:
Set adoxIndex = Nothing
‘delete the index named “pk_EI” from the table:
adoxTable.Indexes.Delete “pk_EI”

‘—
‘CREATE A MULTI-COLUMN INDEX:
‘refer Image 3d showing the “SalesManager” Table where the Index has been created on 2 columns, “FirstName” and “LastName”, wherein values appear in ascending order based on both columns – note that “Jim Davis” which was below “Jim Mason” originally now precedes it.

‘instantiate the ADOX index object:
Set adoxIndex = New ADOX.Index
‘set index name:
adoxIndex.Name = “mc_FNLN”
‘adIndexNullsAllow allows indexing a null value; default value is adIndexNullsDisallow, which disallows null values in the index field:
adoxIndex.IndexNulls = adIndexNullsAllow
‘Append columns to the Columns collection of the Index:
adoxIndex.Columns.Append “FirstName”
adoxIndex.Columns.Append “LastName”
‘Append index to the Indexes collection of the Table:
adoxTable.Indexes.Append adoxIndex
‘destroy the index object variable:
Set adoxIndex = Nothing
‘delete the index named “mc_FNLN” from the table:
adoxTable.Indexes.Delete “mc_FNLN”

‘————-
‘close the object
adoConn.Close

‘destroy the object variables:
Set adoxTable = Nothing
Set adoxColumn = Nothing
Set catAdox = Nothing

End Sub

Create Relationship between Tables, using ADOX

An Index specifies the order of accessing records and can disallow duplicate values in a field. Index enables quick search of records in a table, and it can be created on one or multiple table columns. An index speedily points directly to the row containing a given value in the indexed column(s), instead of searching every row inserted in a randon order in the table. Key is a means to identify records in a table, wherein data can be related by using one or more columns as key columns. Primary key can be specified as one or more columns having unique values, and cannot be Null. There can be only one Primary key in a Table.

A relational database has multiple tables, each table comprising of columns (fields) and rows wherein data can be grouped using common attributes, and from which data can be easily retrieved. The multiple tables relate to and work with each other through certain key fields (like unique “ID Nos”) viz. a separate table is created for vendor-details and another table for purchase-orders wherein vendor details are not repeated each time a purchase order is made and they are related with each other through a unique “Vendor ID” column which is present in both tables.

You can relate multiple tables (we consider 2 tables here) by creating a relationship between key fields (usually the ‘primary key’ fields) of each. We refer one table as the ‘primary table’, for which a unique index is required for the key field so that this key field contains unique values. The other table is referred as ‘foreign table’ and its key field as the ‘foreign key’. The unique index field of the primary table is related to the ‘foreign key’. Note that though it is not required that the foreign key field contain unique values, it is preferable to do so otherwise relating multiple options from the foreign key to a single/unique value in the key field of the primary table might become meaningless.

ADOX Keys Collection:

Keys Collection refers to all Key objects of a Table.

ADOX Keys Collection Properties and Methods:

To add or append a new Key to the Keys collection, use the ADOX Keys Collection Append Method. Syntax: Keys.Append Key, KeyType, Column, RelatedTable, RelatedColumn. All arguments except the Key argument, are optional. The Key argument refers to the Key object. The KeyType specifies the type of key as referenced in the ADOX Key Type Property. Column parameter is a string value which specifies the column name which is to be indexed. RelatedTable parameter is a string value which specifies the name of the Related Table for a foreign key. RelatedColumn parameter is a string value which specifies the Related Column name in the Related Table.

Except the ADOX Keys Append Method which is specific to ADOX, properties and methods which are same as applicable to ADO Collections include: Count property, Item property, Delete method and Refresh method.

ADOX Key Object

Key object is a Primary, Unique or Foreign key field in a Table. Not all properties of the Key object might be supported by your data provider. Commonly used properties for the Key object are explained below.

ADOX Key Object Properties:

ADOX Name Property – read/write property and becomes read-only after Key is appended. Name property is a string value and sets or returns the Key name. Names need not be unique in the Keys collection, and default value is an empty string.

ADOX Key Type Property – read/write property and becomes read-only after Key is appended. Type property sets or returns the type of Key. A primary key is the default value, defined by the constant adKeyPrimary (value of 1). Foreign key is set as adKeyForeign (value of 2). Unique key is set as adKeyUnique (value of 3).

ADOX RelatedTable Property – sets or returns the name of the related table for a foreign key, default value being an empty string.

ADOX DeleteRule Property / ADOX UpdateRule Property – read/write properties and become read-only after Key is appended. These properties set or return the action taken on deletion / updation of a key. Default value is 0 (adRINone) where no action is taken. Value of 1 (adRICascade) indicates Cascade changes, value of 2 (adRISetNull) sets the foreign key value to Null, and value of 3 (adRISetDefault) sets the foreign key value to default.

Note that you can use the ADOX Columns Collection Append Method, Delete Method, Refresh Method, Count Property & Item Property to access the columns of the key object.

Steps to Create a Foreign Key and Relate it to a unique Field of the Primary Table:

Step 1: Connect to the database by setting the Catalog ActiveConnection property to an open connection.

Step 2: Instantiate an ADOX Key object for the foreign key.

Step 3: A unique index is required for the referenced field of the primary table (can be indexed as a ‘primary key’ field), which is being related to the ‘foreign key’.

Step 4: Define the foreign key – specify key as a foreign key, set its name, add the foreign key field to columns collection, specify name of related table, specify name of related column in the related table.

Step 5: Append the foreign key to the keys collection of the foreign table.

Example 5: Create Relationship between Tables, using ADOX – relate a Foreign Key to a unique Field of the Primary Table.

Refer to Images 4a, 4b & 4c, as mentioned in the code.

Sub adoxAutomateAccess_5()
‘Create Relationship between Tables, using ADOX
‘Refer Image 4a which shows the “SalesManager” Table, Image 4b which shows the “Performance” Table, in the Access Database named “SalesReport.accdb”. Image 4c shows the relationship created between the 2 tables, after executing below code.

‘To use ADOX in your VBA project, you must add a reference to the ADOX Object Library in Excel.

‘—————
‘DIM STATEMENTS, INSTANTIATE CATALOG OBJECT, SET ACTIVE CONNECTION TO A DATA SOURCE:

Dim strMyPath As String, strDBName As String, strDB As String
Dim adoxTable As ADOX.Table
Dim adoxKey As ADOX.Key

‘instantiate an ADOX Catalog object using Dim with the New keyword:
Dim catAdox As New ADOX.catalog

‘————-
‘INDEX AS A PRIMARY KEY FIELD:

‘the “SM_Id” field of the primary table “SalesManager” is being indexed as a ‘primary key’ field.
Set adoxTable = catAdox.Tables.Item(“SalesManager”)

‘Create a primary key index
‘instantiate the ADOX index object:
Dim adoxIndex As New ADOX.Index
‘set index name:
adoxIndex.Name = “pk_SM”
‘Specify index to be the primary key. Default value for the PrimaryKey Property is False.
adoxIndex.PrimaryKey = True
‘Append column to the Columns collection of the Index
adoxIndex.Columns.Append “SM_Id”
‘Append index to the Indexes collection of the Table:
adoxTable.Indexes.Append adoxIndex
‘destroy the object variables:
Set adoxIndex = Nothing
Set adoxTable = Nothing

‘————-
‘CREATE, DEFINE AND APPEND THE FOREIGN KEY:
‘Create foreign key and relate it to the “SM_Id” field of primary table “SalesManager”
‘The foreign key field is “EmployeeId” in the foreign table “Performance”

‘set table named “Performance” as the foreign table:
Set adoxTable = catAdox.Tables(“Performance”)

‘instantiate an ADOX Key object for the foreign key:
Set adoxKey = New ADOX.Key

‘define the foreign key as below:

With adoxKey

‘specifies key as a foreign key. Default value is adKeyPrimary (primary key).
.Type = adKeyForeign
‘specify name for the foreign key:
.Name = “relate_Id”
‘specify primary table name being related to the foreign key:
.RelatedTable = “SalesManager”
‘the ‘foreign key field’ is added to the Columns collection.
.Columns.Append “EmployeeId”
‘specify the name of the corresponding column in the primary table, which is being related to the ‘foreign key field’.
.Columns(“EmployeeId”).RelatedColumn = “SM_Id”
‘the foreign key value is set to null when the key is deleted (adRISetNull).

.DeleteRule = adRISetNull

End With

‘the foreign key is appended to the keys collection of foreign table “Performance”.
adoxTable.Keys.Append adoxKey

‘delete the foreign key and cancel relationship between tables:
adoxTable.Keys.Delete “relate_Id”

‘————-
‘destroy the object variables:
Set catAdox.ActiveConnection = Nothing
Set adoxKey = Nothing
Set adoxTable = Nothing
Set catAdox = Nothing

End Sub

ADO Command Object, ADOX View Object, Parameter Queries, Create & Execute Stored Queries / Action Queries with ADOX

ADO Command Object

A Command Object refers to a specific command executed on a database. The Command Object is used to execute a query on a database to manipulate records or manipulate the database structure. A Command Object is specifically useful in a parameter query, wherein the command is specified in CommandText and compiled before execution, is saved in memory, and re-used when called again, and different values can be passed for the parameter every time it is called. To reuse the Command Text (ie. to reuse a stored query or a stored procedure) or to use parameter queries, you need to use a Command object. However, a Command object is not required for: (i) the Execute method on a Connection object to execute the SQL query passed in the CommandText argument; or (ii) the Open Method to open a Recordset object for passing an SQL query in the Source argument.

The ADO CommandText property of the Command object is used to set or return a text of a command which is supported or recognized by the provider. This is generally an SQL statement, and can also be a stored procedure call, default value being an empty string.

The ADO Command Object’s Execute Method is used to execute the SQL statement or query or the stored procedure which is specified in the CommandText property of the Command object. Syntax: command.Execute( RecordsAffected, Parameters, Options). All arguments are optional. RecordsAffected argument applies only to action queries or stored procedures indicating the number of records affected by the operation. The Parameters argument is used for parameter values in association with the text specified in the Command Text property of the Command object. The parameter values are passed as a Variant array. The Options agument specifies how should the Command Text property of the Command object be evaluated by the provider.

ADOX View Object

A View object is a virtual Table containing a set of records filtered from another database Table or View. You cannot instantiate a View object but you create a View by using the ADO Command Object which also enables you to modify or delete a view in a Database. The Command object is used to specify the attributes of the new View you want to create in the database. A new View is created by using the ADOX Views Append Method (Syntax: Views.Append Name, Command) which appends a view to the views collection where the Name argument is the name of the view you are creating, and the Command argument is the Command object. The Command object executes the procedure or SQL statement or query specified in its Command Text (ie. CommandText Property). In this manner the View object is used to save a Query object in the database, using ADOX.

To retrieve and access field information of a View object, we use the ADO Recordset. The ADO Recordset Source Property is used to specify a data source for a Recordset object, and is accordingly used to set a Command object reference. To set the new View you have created as the Recordset source, use the ADOX View Object’s Command property to specify the Command Object (which executes the procedure or SQL query).

Steps to Create a New View in a Database and to Retrieve Field information:

Step 1: Connect to the database by setting the Catalog ActiveConnection property to an open connection.

Step 2: Create the command, assigning the SQL statement to the CommandText property of the Command object.

Step 3: Create a new view by specifying the View’s name and appending the command object to the catalog’s Views collection – use ADOX Views Append Method.

Step 4: Instantiate the ADO Recordset object.

Step 5: Set the new View as the Recordset source by using the ADOX View Object’s Command property to specify the Command Object (which executes the procedure or SQL query).

Step 6: Retrieve Field information of the new View.

Example 6: Create a new View in the Database, using ADOX and ADO Command Object.

Refer to Images 5a and 5b, as mentioned in the code.

Sub adoxAutomateAccess_6()
‘Create a new View in the Database, using ADOX and ADO Command Object
‘Refer Image 5a which shows the Access Database named “SalesReport.accdb” which has 2 tables named “SalesManager” and “Performance”. Image 5b shows the View object created from the “SalesManager” table, after executing below code.

‘To use ADOX in your VBA project, you must add a reference to the ADOX Object Library in Excel.
‘To use ADO in your VBA project, you must add a reference to the ADO Object Library in Excel.

‘—————
‘DIM STATEMENTS, INSTANTIATE CATALOG OBJECT, SET ACTIVE CONNECTION TO A DATA SOURCE:

Dim strMyPath As String, strDBName As String, strDB As String, strSQL As String
Dim adoxTable As ADOX.Table
Dim adoxView As ADOX.View
Dim adoCmd As New ADODB.Command
Dim adoRecSet As ADODB.Recordset
Dim adoField As ADODB.Field

‘instantiate an ADOX Catalog object using Dim with the New keyword:
Dim catAdox As New ADOX.catalog

‘refer to a database table by name:
Set adoxTable = catAdox.Tables(“SalesManager”)

‘————-
‘DELETE A VIEW OBJECT IN THE DATA SOURCE

‘search views collection for an existing view named “viewFullNames”, and delete if found:

For Each adoxView In catAdox.Views

If adoxView.Name = “viewFullNames” Then

catAdox.Views.Delete “viewFullNames”

End If

‘————-
‘CREATE A NEW VIEW IN THE DATA SOURCE, USING ADOX AND COMMAND OBJECT

‘SQL query to be assigned to the command:
strSQL = “SELECT FirstName, LastName FROM SalesManager”

‘Create the command assigning the SQL query to the CommandText property of the Command object:
adoCmd.CommandText = strSQL

‘Create a new view (and save a query in the database) named “viewFullNames” in the data source, by appending the command object to the catalog Views collection:
catAdox.Views.Append “viewFullNames”, adoCmd
Set adoCmd = Nothing

‘————-
‘GET FIELD INFORMATION IN THE NEW VIEW RECORDSET

‘Set the ADO Recordset object using the New keyword:
Set adoRecSet = New ADODB.Recordset

‘Set the new View as the Recordset source by using the ADOX View Object’s Command property to specify the Command Object (which executes the procedure or SQL query):
Set adoRecSet.Source = catAdox.Views(“viewFullNames”).Command

‘get names of all fields in the recordset (ie. in the new view):
adoRecSet.Fields.Refresh
For Each adoField In adoRecSet.Fields

MsgBox adoField.Name & “, ” & adoField.Type

‘————-
‘destroy the object variables:
Set catAdox.ActiveConnection = Nothing
Set adoCmd = Nothing
Set adoRecSet = Nothing
Set adoField = Nothing
Set adoxTable = Nothing
Set catAdox = Nothing

End Sub

Parameter Queries

A parameter query is one in which the user is prompted to enter some information, such as selection criteria for records or a field value to be inserted. The value of the parameter is passed when the parameter query is called. The Data Type of a parameter should match to that of the corresponding field. The parameter must be defined by using a Parameters declaration in the beginning of the SQL statement. The Parameters declaration must mention the parameter name, and can optionally mention the parameter type.

Creating a Parameter Query:

Consider the SQL query: CommandText = “DELETE FROM SalesManager WHERE FirstName = ‘Jim’“. In this query, the record whose FirstName field contains Jim is being deleted from the SalesManager table.

Using a Parameter Query for above: CommandText = “PARAMETERS fname Text; DELETE FROM SalesManager WHERE FirstName = fname”. In this SQL query, fname is the parameter variable name of Text data type to which the FirstName value is to be passed. The fixed value (Jim) has been replaced by the parameter (fname).

To save the SQL query in the database, the Command object must be Appended to the ADOX Procedures collection.

Create and Execute Stored Queries and Action Queries with ADOX

Microsoft Access can execute Action Queries which are queries that add, modify or delete records from a database, using SQL commands of INSERT, UPDATE AND DELETE. One way to run these queries is by specifying SQL statements each time the query is run. Alternatively, a faster and more efficient way is to execute a stored query in which the SQL statement is already compiled and specifying the SQL string is not necessary to be specified each time the query is run. It is particularly useful to use parameters with these Stored Queries, wherein you can pass new values to these parameters in the SQL statement each time you want to run it. When parameters are used with a Query, it is referred to as a Parameter Query. To use a Stored Query, firstly you Create and Save an Action Query with parameters, and then assign values to one or more parameters each time you want to execute the Stored Query.

Steps to Create and Execute Stored Queries using Parameters:

Create an SQL Parametrized Query:

Create an Action query using SQL commands.
Identify the parameters which can be used in the SQL Action query – this will be a dynamic criteria for which you will want to enter new values each time the query is run viz. if you want to delete records basis First Name of an employee, you might want to parameterize the First Name so that each time you can pass the First Name of the record you want deleted.
Create the command assigning the SQL Parameterized query to the CommandText property of the Command object.
Connect to the database by setting the Catalog ActiveConnection property to an open connection.
To save the SQL query in the database, the Command object must be Appended to the ADOX Procedures collection.

Execute an SQL Parametrized Query:

Connect to the database by setting the Catalog ActiveConnection property to an open connection.
Retrieve the Command object for the Stored query (identified by its name) from the Procedures collection. Use the ADOX Procedure Object’s Command property to specify the Command Object for the Stored query.
Provide the parameter value(s) into the Stored Query.
The Stored Parameterized Query is executed by using the Execute method of the Command object.

Example 7a: Create a Stored Parameter Query (single parameter) in the Database, using ADOX and ADO Command Object.

Example 7b: Retrieve and Execute a Stored Parameter Query in the Data Source.

Refer to Images 6a and 6b, as mentioned in the code.

Sub adoxAutomateAccess_7a()
‘Create a Stored Parameter Query (single parameter) in the Database, using ADOX and ADO Command Object
‘Refer Image 6a which shows the Access Database named “SalesReport.accdb” which has 2 tables named “SalesManager” and “Performance”, and the Stored Query named “procedureDelete”, after executing below code.

‘To use ADOX in your VBA project, you must add a reference to the ADOX Object Library in Excel.
‘To use ADO in your VBA project, you must add a reference to the ADO Object Library in Excel.

‘—————
‘DIM STATEMENTS, INSTANTIATE CATALOG OBJECT, SET ACTIVE CONNECTION TO A DATA SOURCE:

Dim strMyPath As String, strDBName As String, strDB As String, strSQL As String
Dim adoxTable As ADOX.Table
Dim adoxProc As ADOX.Procedure
Dim adoCmd As New ADODB.Command

‘instantiate an ADOX Catalog object using Dim with the New keyword:
Dim catAdox As New ADOX.catalog

‘set the ActiveConnection property to a connection string:
catAdox.ActiveConnection = “Provider = Microsoft.ACE.OLEDB.12.0; data source=” & strDB

‘refer to a database table by name:
Set adoxTable = catAdox.Tables(“SalesManager”)

‘————-
‘DELETE A STORED QUERY/PROCEDURE IN THE DATA SOURCE

‘search Procedures collection for an existing procedure named “procedureDelete”, and delete if found using the Procedures Delete Method:

For Each adoxProc In catAdox.Procedures

If adoxProc.Name = “procedureDelete” Then

catAdox.Procedures.Delete “procedureDelete”

End If

‘————-
‘CREATE A PARAMETER QUERY / PROCEDURE IN THE DATA SOURCE, USING ADOX AND ADO COMMAND OBJECT

‘A parameter query is one in which the user is prompted to enter some information, such as selection criteria for records or a field value to be inserted.
‘SQL Parameterized query to be assigned to the command. The SQL query is constructed with parameters.
strSQL = “PARAMETERS fname Text; DELETE FROM SalesManager WHERE FirstName = fname”

‘Create the command assigning the SQL Parameterized query to the CommandText property of the Command object:
adoCmd.CommandText = strSQL

‘Create/Add a new procedure (ie. save a query) by appending the command to the catalog’s Procedures collection:
catAdox.Procedures.Append “procedureDelete”, adoCmd

‘————-
‘RETURN NAMES OF ALL PROCEDURES AND OF ALL PARAMETERS OF A PROCEDURE, IN THE DATA SOURCE

‘return number of procedures:
MsgBox catAdox.Procedures.count

‘return names of all procedures:

For Each adoxProc In catAdox.Procedures

MsgBox adoxProc.Name

‘return names of all parameters of a procedure:
Set adoCmd = catAdox.Procedures(“procedureDelete”).Command

Dim adoParam As ADODB.Parameter

For Each adoParam In adoCmd.Parameters

MsgBox adoParam.Name

‘————-
‘destroy the object variables:
Set catAdox.ActiveConnection = Nothing
Set adoCmd = Nothing
Set adoxProc = Nothing
Set adoxTable = Nothing
Set catAdox = Nothing

End Sub

Sub adoxAutomateAccess_7b()
‘Retrieve and Execute a Stored Parameter Query in the Data Source.
‘Refer Image 6b which shows the record with FirstName “Jim” deleted in the Access Database “SalesReport.accdb”, after running below code.

‘To use ADOX in your VBA project, you must add a reference to the ADOX Object Library in Excel.
‘To use ADO in your VBA project, you must add a reference to the ADO Object Library in Excel.

‘—————
‘DIM STATEMENTS, INSTANTIATE CATALOG OBJECT, SET ACTIVE CONNECTION TO A DATA SOURCE:

Dim strMyPath As String, strDBName As String, strDB As String
Dim adoxTable As ADOX.Table
Dim adoCmd As New ADODB.Command

‘instantiate an ADOX Catalog object using Dim with the New keyword:
Dim catAdox As New ADOX.catalog

‘set the ActiveConnection property to a connection string:
catAdox.ActiveConnection = “Provider = Microsoft.ACE.OLEDB.12.0; data source=” & strDB

‘refer to a database table by name:
Set adoxTable = catAdox.Tables(“SalesManager”)

‘————-
‘EXECUTE A STORED PARAMETER QUERY IN THE DATA SOURCE

‘Retrieve the Command object for the Stored query (identified by its name) from the Procedures collection. Use the ADOX Procedure Object’s Command property to specify the Command Object for the Stored query.
Set adoCmd = catAdox.Procedures(“procedureDelete”).Command

‘execute stored parameter query:
adoCmd.Parameters(“fname”) = “Jim”
adoCmd.Execute

‘alternate – execute stored parameter query:
‘adoCmd.Execute Parameters:=“Jim”

‘alternate – execute stored parameter query:
‘adoCmd.Execute , “Jim”

‘alternate – execute stored parameter query:
‘Dim strFiNa As String
‘strFiNa = InputBox(“Please enter the First Name which you wish to Delete!”)
‘adoCmd.Parameters(“fname”) = strFiNa
‘adoCmd.Execute

‘————-
‘destroy the object variables:
Set catAdox.ActiveConnection = Nothing
Set adoCmd = Nothing
Set adoxTable = Nothing
Set catAdox = Nothing

End Sub

Example 8a: Create a Stored Parameter Query (multiple pameters) in the Database, using ADOX and ADO Command Object.

Example 8b: Retrieve and Execute a Stored Parameter Query in the Data Source.

Refer to Images 6a and 7, as mentioned in the code.

Sub adoxAutomateAccess_8a()
‘Create a Stored Parameter Query (multiple pameters) in the Database, using ADOX and ADO Command Object
‘Refer Image 6a which shows the Access Database named “SalesReport.accdb” which has 2 tables named “SalesManager” and “Performance”, and the Stored Query named “procedureDelete”, after executing below code.

‘To use ADOX in your VBA project, you must add a reference to the ADOX Object Library in Excel.
‘To use ADO in your VBA project, you must add a reference to the ADO Object Library in Excel.

‘—————
‘DIM STATEMENTS, INSTANTIATE CATALOG OBJECT, SET ACTIVE CONNECTION TO A DATA SOURCE:

Dim strMyPath As String, strDBName As String, strDB As String, strSQL As String
Dim adoxTable As ADOX.Table
Dim adoxProc As ADOX.Procedure
Dim adoCmd As New ADODB.Command

‘instantiate an ADOX Catalog object using Dim with the New keyword:
Dim catAdox As New ADOX.catalog

‘set the ActiveConnection property to a connection string:
catAdox.ActiveConnection = “Provider = Microsoft.ACE.OLEDB.12.0; data source=” & strDB

‘refer to a database table by name:
Set adoxTable = catAdox.Tables(“SalesManager”)

‘————-
‘DELETE A PROCEDURE IN THE DATA SOURCE

‘search Procedures collection for an existing procedure named “procedureDelete”, and delete if found using the Procedures Delete Method:

For Each adoxProc In catAdox.Procedures

If adoxProc.Name = “procedureDelete” Then

catAdox.Procedures.Delete “procedureDelete”

End If

‘————-
‘CREATE A PARAMETER QUERY / PROCEDURE IN THE DATA SOURCE, USING ADOX AND ADO COMMAND OBJECT

‘A parameter query is one in which the user is prompted to enter some information, such as selection criteria for records or a field value to be inserted.
‘SQL Parameterized query to be assigned to the command. The SQL query is constructed with parameters.
strSQL = “PARAMETERS fname Text, lname Text; DELETE FROM SalesManager WHERE FirstName = fname or LastName = lname”

‘Create the command assigning the SQL Parameterized query to the CommandText property of the Command object:
adoCmd.CommandText = strSQL

‘Create/Add a new procedure (ie. save a query) by appending the command to the catalog’s Procedures collection:
catAdox.Procedures.Append “procedureDelete”, adoCmd

‘————-
‘destroy the object variables:
Set catAdox.ActiveConnection = Nothing
Set adoCmd = Nothing
Set adoxProc = Nothing
Set adoxTable = Nothing
Set catAdox = Nothing

End Sub

Sub adoxAutomateAccess_8b()
‘Retrieve and Execute a Stored Parameter Query in the Data Source.
‘Refer Image 7 which shows the records with FirstName “Jim” or LastName “Green” deleted in the Access Database “SalesReport.accdb”, after running below code.

‘To use ADOX in your VBA project, you must add a reference to the ADOX Object Library in Excel.
‘To use ADO in your VBA project, you must add a reference to the ADO Object Library in Excel.

‘—————
‘DIM STATEMENTS, INSTANTIATE CATALOG OBJECT, SET ACTIVE CONNECTION TO A DATA SOURCE:

Dim strMyPath As String, strDBName As String, strDB As String
Dim adoxTable As ADOX.Table
Dim adoCmd As New ADODB.Command

‘instantiate an ADOX Catalog object using Dim with the New keyword:
Dim catAdox As New ADOX.catalog

‘set the ActiveConnection property to a connection string:
catAdox.ActiveConnection = “Provider = Microsoft.ACE.OLEDB.12.0; data source=” & strDB

‘refer to a database table by name:
Set adoxTable = catAdox.Tables(“SalesManager”)

‘————-
‘EXECUTE A STORED PARAMETER QUERY IN THE DATA SOURCE

‘execute stored parameter query:
adoCmd.Execute , Parameters:=Array(“Jim”, “Green”)

‘————-
‘destroy the object variables:
Set catAdox.ActiveConnection = Nothing
Set adoCmd = Nothing
Set adoxTable = Nothing
Set catAdox = Nothing

End Sub

Example 9a: Create a Stored Parameter Query (paremeters are 2 dates) in the Database, using ADOX and ADO Command Object.

Example 9b: Retrieve and Execute a Stored Parameter Query in the Data Source.

Refer to Images 6a and 8, as mentioned in the code.

Sub adoxAutomateAccess_9a()
‘Create a Stored Parameter Query (parameters are 2 dates) in the Database, using ADOX and ADO Command Object
‘Refer Image 6a which shows the Access Database named “SalesReport.accdb” which has 2 tables named “SalesManager” and “Performance”, and the Stored Query named “procedureDelete”, after executing below code.

‘To use ADOX in your VBA project, you must add a reference to the ADOX Object Library in Excel.
‘To use ADO in your VBA project, you must add a reference to the ADO Object Library in Excel.

‘—————
‘DIM STATEMENTS, INSTANTIATE CATALOG OBJECT, SET ACTIVE CONNECTION TO A DATA SOURCE:

Dim strMyPath As String, strDBName As String, strDB As String, strSQL As String
Dim adoxTable As ADOX.Table
Dim adoxProc As ADOX.Procedure
Dim adoCmd As New ADODB.Command

‘instantiate an ADOX Catalog object using Dim with the New keyword:
Dim catAdox As New ADOX.catalog

‘set the ActiveConnection property to a connection string:
catAdox.ActiveConnection = “Provider = Microsoft.ACE.OLEDB.12.0; data source=” & strDB

‘refer to a database table by name:
Set adoxTable = catAdox.Tables(“SalesManager”)

‘————-
‘DELETE A PROCEDURE IN THE DATA SOURCE

‘search Procedures collection for an existing procedure named “procedureDelete”, and delete if found using the Procedures Delete Method:

For Each adoxProc In catAdox.Procedures

If adoxProc.Name = “procedureDelete” Then

catAdox.Procedures.Delete “procedureDelete”

End If

‘————-
‘CREATE A PARAMETER QUERY / PROCEDURE IN THE DATA SOURCE, USING ADOX AND ADO COMMAND OBJECT

‘A parameter query is one in which the user is prompted to enter some information, such as selection criteria for records or a field value to be inserted.
‘SQL Parameterized query to be assigned to the command. The SQL query is constructed with parameters.
strSQL = “PARAMETERS startDate DateTime, endDate DateTime; DELETE FROM SalesManager WHERE JoinDate between startDate and endDate”

‘Create the command assigning the SQL Parameterized query to the CommandText property of the Command object:
adoCmd.CommandText = strSQL

‘Create/Add a new procedure (ie. save a query) by appending the command to the catalog’s Procedures collection:
catAdox.Procedures.Append “procedureDelete”, adoCmd

‘————-
‘destroy the object variables:
Set catAdox.ActiveConnection = Nothing
Set adoCmd = Nothing
Set adoxProc = Nothing
Set adoxTable = Nothing
Set catAdox = Nothing

End Sub

Sub adoxAutomateAccess_9b()
‘Retrieve and Execute a Stored Parameter Query in the Data Source.
‘Refer Image 8 which shows the records having “JoinDate” between the dates “04/01/2011” and “12/31/2012” deleted in the Access Database “SalesReport.accdb”, after running below code.

‘To use ADOX in your VBA project, you must add a reference to the ADOX Object Library in Excel.
‘To use ADO in your VBA project, you must add a reference to the ADO Object Library in Excel.

‘—————
‘DIM STATEMENTS, INSTANTIATE CATALOG OBJECT, SET ACTIVE CONNECTION TO A DATA SOURCE:

Dim strMyPath As String, strDBName As String, strDB As String
Dim adoxTable As ADOX.Table
Dim adoCmd As New ADODB.Command

‘instantiate an ADOX Catalog object using Dim with the New keyword:
Dim catAdox As New ADOX.catalog

‘set the ActiveConnection property to a connection string:
catAdox.ActiveConnection = “Provider = Microsoft.ACE.OLEDB.12.0; data source=” & strDB

‘refer to a database table by name:
Set adoxTable = catAdox.Tables(“SalesManager”)

‘————-
‘EXECUTE A STORED PARAMETER QUERY IN THE DATA SOURCE

‘execute stored parameter query:
adoCmd.Parameters(“startDate”) = “04/01/2011”
adoCmd.Parameters(“endDate”) = “12/31/2012”
adoCmd.Execute
Set adoCmd = Nothing

‘alternate – execute stored parameter query:
‘Dim date1 As Date, date2 As Date
‘date1 = InputBox(“Please enter start date!”)
‘adoCmd.Parameters(“startDate”) = date1
‘date2 = InputBox(“Please enter end date!”)
‘adoCmd.Parameters(“endDate”) = date2
‘adoCmd.Execute
‘Set adoCmd = Nothing

‘alternate – execute stored parameter query:
‘set value for startDate parameter:
‘adoCmd.Parameters(0) = “04/01/2011”
‘set value for endDate parameter:
‘adoCmd.Parameters(1) = “12/31/2012”
‘adoCmd.Execute
‘Set adoCmd = Nothing

‘alternate – execute stored parameter query:
‘adoCmd.Execute , Parameters:=Array(#4/1/2011#, #12/31/2012#)
‘Set adoCmd = Nothing

‘————-
‘destroy the object variables:
Set catAdox.ActiveConnection = Nothing
Set adoxTable = Nothing
Set catAdox = Nothing

End Sub

Microsoft Access: ActiveX Data Objects Extensions (ADOX)

Microsoft Access: ActiveX Data Objects Extensions (ADOX)

Microsoft Access: ActiveX Data Objects Extensions (ADOX)

VBA TUTORIAL LOGIN

Cond Statements in VBA Test

Text Strings in VBA

Custom Number Formats

Excel VBA Built-in Events

ActiveX & Form Controls

Application.OnTime Method

MsgBox & InputBox in VBA

Leave a Reply Cancel reply