Microsoft Access DAO Object Model: Create an Index, Create Relationship between Fields, Create and Exceute a Query

 

 

Microsoft Access: Data Access Objects Library (DAO), Connect with Access Databases from Excel using VBA.

Part 2 of 3

 

---

Microsoft Access: Data Access Objects Library (DAO), Connect with Access Databases from Excel using VBA:

1. Connecting to Microsoft Access Database from Excel VBA, using DAO Object Model.

2. Microsoft Access DAO Object Model: Create an Index, Create Relationship between Fields, Create and Execute a Query.

3. Microsoft Access DAO Object Model: Import or Export Data from Access to Excel.

 

----------------

Also Read:

Microsoft Access: ActiveX Data Objects (ADO), Connect with Access Databases from Excel using VBA.

---

 

---------------------------------------------------------------------------------------------------------

Contents:

Create an Index using DAO

Create Relationship between Fields of Database Tables, using DAO

Create and Exceute a Query, including Action & Parameter Query, using DAO

---------------------------------------------------------------------------------------------------------

 

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. You can relate multiple tables by creating a relationship between key fields of each. SQL statements are referred to as queries. You may create a Recordset based on a stored query or a parameter query.

 

 

Create an Index using DAO

 

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 random 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.

 

Steps to create an Index in a database table:

Step 1: Create an Index object, using the DAO TableDef.CreateIndex Method.

Step 2: Set Index properties viz. set index name, set as primary key index, allow / disallow null value in index field, and so on.

Step 3: Define Index Field(s) - Create Field object(s) for the Index object (using its CreateField method) and Append these to the fields collection of the Index object.

Step 4: Append the Index object to the TableDef's Indexes collection (using the Indexes.Append Method).

 

 

Create an Index object

 

Use the TableDef.CreateIndex Method to Create an Index in a Database Table. Syntax: TableDefObject.CreateIndex(Name). The Name argument (optional) is a string value specifying the name (maximum length of 64 characters) of the new Index. If you do not specify the Name in the CreateIndex method, you can set it later by assigning the name to the Index object before appending the new Index object to a collection.

 

 

DAO Indexes Collection

 

Indexes Collection refers to all Index objects of a Table.

 

 

DAO Indexes Collection  Methods and Properties:

 

DAO Indexes.Append Method. Use the Append method to add a new index to the Indexes collection: TableDefObject.Indexes.Append(IndexObject).

 

DAO Indexes.Delete Method. To Delete an Index object, use the Delete method: TableDefObject.Indexes.Delete(IndexName).

 

DAO Indexes.Refresh Method: The Refresh method is particularly required to be used in a multi-user environment wherein different users might be making changes in the database, in which case only those objects are contained when you have referred to the collection initially without reflecting any subsequent changes made by other users, and the collection will get updated only on using the Refresh method. To refresh the indexes collection: TableDefObject.Indexes.Refresh.

 

 

DAO Indexes.Count Property: The Count property of the Indexes collection determines the number of Index objects in the collection. When there are no objects in the collection, the Count property value will be 0.

 

 

 

DAO Index Object

 

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.

 

 

DAO Index Object  Methods and Properties:

Some methods & properties of an Index object are briefly discussed here.

 

DAO Index.CreateField Method

 

To create a new field for an Index object, use the Index.CreateField Method. Syntax: IndexObject.CreateField(Name, Type, Size). All arguments are optional to specify. The Name argument specifies a name for the new Field. The Type and Size arguments are not supported for an Index object, and are ignored in this case. If the Name argument is not specified in the method, you can set the Name property thereafter but before the Field is appended to the Fields collection of the Index object.

 

To delete a field from the Fields Collection, use the DAO Fields.Delete Method. Note that once an index referencing a field has been created, that Field cannot be deleted from a Fields collection of a TableDef object.

 

 

DAO Index.Name Property

 

Use the Name property to set or return the name (a string value with maximum length of 64 characters) of an Index object. The property is read/write before the Index object is appended to a collection and read-only after appending. Syntax: IndexObject.Name.

 

 

DAO Index.Primary Property

 

Use the Index.Primary Property to determine whether the Index object is as a primary key index for a database table. Use the Syntax IndexObject.Primary = True to set the Index object as a primary key index. Default value is False. The Unique and Required properties are automatically set to True for a primary key index. A primary key index will identify all records of a table uniquely in a predefined order. If an Index is not primary, it is a secondary index (ie. when the Primary property of the Index is set to False). The Primary property is read/write before the Index object is appended to the Indexes collection and read-only for an appended Index object. Note that the Primary property is read/write also in the case when the Index object is appended to the TableDef object which itself is not appended to the TableDefs collection.

 

 

DAO Index.Unique Property

 

Use the Index.Unique Property to determine whether values in an Index should be unique or not. Setting the property to True will identify all records uniquely in the table. For a single-field index this means that all values within the field should be unique, whereas for a multi-field index this means that though duplicate values are allowed within a particular field but each set of values combined from all indexed fields should be unique. Syntax: IndexObject .Unique. The property is read/write before the Index object is appended to a collection and read-only after appending.

 

 

DAO Index.Required Property

 

The Index.Required Property determines whether an index field can accept null values. Setting the Required Property to False will allow null values in the indexed field. Between an Index object and a Field object, set the Required property for the Field object because its validation for the Field object precedes that of an Index object. Default setting is False. Syntax: IndexObject.Required.

 

 

DAO Index.IgnoreNulls Property

 

Syntax: IndexObject.IgnoreNulls. This property is read/write before the Index object is appended to a collection and read-only after appending. Default setting is False. The IgnoreNulls Property determines whether a record having a null value in its index field will be included in the index (ie. will have an index entry or not). The IgnoreNulls property and Required property are used together to determine an index entry for records having null index values. When the Required property for an Index is False: (i) records are allowed to have a null value in the Index field and if IgnoreNulls property is True, an index entry WILL NOT be added for the record; and (ii) records are allowed to have a null value in the Index field and if IgnoreNulls property is False, an index entry WILL be added for the record. When the Required property for an Index is True, records are NOT allowed to have a null value in the Index field, and in this case the record with a non-null value in its Index field will have an index entry. If you want to allow null values in the Index field and expect a large number of null values therein, setting the IgnoreNulls property to True will reduce the storage space used by the Index. Refer Example 8b which explains this property.

 

 

Index property of a Recordset Object: DAO Recordset.Index Property

 

Index is created for a database Table and appended to the Indexes collection of the underlying TableDef object. An Index does not change the order in which records are actually stored in a base table, but only affects the order in which they are returned or are accessed in a Recordset when it is opened or when a particular Index is selected. For a table-type Recordset object you can set the order of the records only with an index which is already defined for the underlying TableDef object. Use the Recordset.Index property in a table-type Recordset object to set or return the name of the current Index object - Syntax: RecordsetObject.Index. Note that you can use an SQL statement ORDER BY clause in a dynaset-type or snapshot-type or forward-only-type Recordset to sort records in an order other than in a defined Index of a table.

 

 

 

Example 8a: Create Index Keys (Primary Index, Single-Field Index & Multi-Field Index) for Fields in a Database.

Refer Images 8a, 8b, 8c & 8d as mentioned in the code.

 

 

 

 

 

Sub AccessDAO_CreateIndex_8a()
'create Index Keys (Primary Index, Single-Field Index & Multi-Field Index) for Fields in a database, using DAO
'refer Image 8a to view the existing SalesManager Table in MS Access file "SalesReport.accdb".

'To use DAO in your Excel VBA project, you must add a reference to the DAO Object Library in Excel (your host application) by clicking Tools-References in VBE.

Dim strMyPath As String, strDBName As String, strDB As String
Dim daoDB As DAO.Database
Dim daoTblDef As DAO.TableDef
Dim daoIndex As DAO.Index
Dim daoFld As DAO.Field

'---------------

'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

'open a database object: assign the database reference to an object variable:
Set daoDB = DBEngine.OpenDatabase(strDB)

'refer to a TableDef object by its name: reference the table named SalesManager
Set daoTblDef = daoDB.TableDefs("SalesManager")

'----------------
'Steps to create an Index:
'Step 1: Create an Index object, using the DAO TableDef.CreateIndex Method.
'Step 2: Define Index properties viz. set index name, set as primary key index, allow / disallow null value in index field, and so on.
'Step 3: Define Index Field(s) - Create Field object(s) for the Index object (using its CreateField method) and Append these to the fields collection of the Index object.
'Step 4: Append the Index object to the TableDef's Indexes collection.

'----------------
'CREATE A PRIMARY KEY INDEX:
'refer Image 8b 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. The Unique and Required properties are automatically set to True for a primary key index:

Set daoIndex = daoTblDef.CreateIndex("piEmpId")

'define index properties:

With daoIndex

'set index as a primary key index, that uniquely identifies all records in a table:

.Primary = True

'define the Index field:

.Fields.Append .CreateField("EmployeeId")

End With

'append index to collection:
daoTblDef.Indexes.Append daoIndex

'destroy the index object variable:
Set daoIndex = Nothing

'delete index by reference to its name:
daoTblDef.Indexes.Delete "piEmpId"

'----------------
'CREATE A SINGLE-FIELD INDEX WITH UNIQUE VALUES:
'refer Image 8c showing the "SalesManager" Table where the Index has been created on "Surname" column whose values appear in ascending order and the column cannot have duplicate values.

Set daoIndex = daoTblDef.CreateIndex

'define index properties:

With daoIndex

.Name = "iSurNm"

'null values not allowed in the indexed field:

.Required = True

'identify all records uniquely in the table. For a single-field index this means that all values within the field should be unique, whereas for a multi-field index this means that though duplicate values are allowed within a

particular field but each set of values combined from all indexed fields should be unique.

.Unique = True

.Fields.Append .CreateField("Surname")

End With

daoTblDef.Indexes.Append daoIndex

Set daoIndex = Nothing

'delete index by reference to its name:
daoTblDef.Indexes.Delete ("iSurNm")

'----------------
'CREATE A MULTI-FIELD INDEX:
'refer Image 8d showing the "SalesManager" Table where the Index has been created on 2 columns, "FirstName" and "Surname", wherein values appear in ascending order based on both columns - note that "Jim Davis" which was below "Jim Mason" originally now precedes it.

Set daoIndex = daoTblDef.CreateIndex("iFullNm")

'define index properties:

With daoIndex

.Required = True

.Fields.Append .CreateField("FirstName")

.Fields.Append .CreateField("Surname")

End With

daoTblDef.Indexes.Append daoIndex

Set daoIndex = Nothing

'refresh display (order) of Indexes collection:
daoTblDef.Indexes.Refresh

'enumerate all Indexes collection and Fields collection of each Index:
For Each daoIndex In daoTblDef.Indexes

For Each daoFld In daoIndex.Fields

MsgBox "Index Name: " & daoIndex.Name & "; " & "Field Name: " & daoFld.Name

Next daoFld

Next daoIndex

'delete index by reference to its name:
daoTblDef.Indexes.Delete "iFullNm"

'---------------
'close the objects:
daoDB.Close

'destroy the variables:
Set daoDB = Nothing
Set daoTblDef = Nothing
Set daoIndex = Nothing

Set daoFld = Nothing

End Sub

 

 

 

 

Example 8b: When the Required property setting is False for an Index object, records with Null value have an index entry depending on the Index.IgnoreNulls property setting.

Refer Images 8a & 8e as mentioned in the code.

 

 

Sub AccessDAO_CreateIndex_8b()
'When the Required property setting is False for an Index object: records with Null value have an index entry if IgnoreNulls = False; records with Null value do NOT have an index entry if IgnoreNulls = True;
'refer Image 8a to view the existing SalesManager Table in MS Access file "SalesReport.accdb"; refer Image 8e after running below code.

'To use DAO in your Excel VBA project, you must add a reference to the DAO Object Library in Excel (your host application) by clicking Tools-References in VBE.

Dim strMyPath As String, strDBName As String, strDB As String
Dim daoDB As DAO.Database
Dim daoTblDef As DAO.TableDef
Dim daoIndex As DAO.Index
Dim recSet As DAO.Recordset

'---------------
'your data source with which to establish connection - ENTER the MS Access Database Name:
strDBName = "SalesReport4.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

'open a database object: assign the database reference to an object variable:
Set daoDB = DBEngine.OpenDatabase(strDB)

'refer to a TableDef object by its name: reference the table named SalesManager
Set daoTblDef = daoDB.TableDefs("SalesManager")

'----------------
Set daoIndex = daoTblDef.CreateIndex

'define index properties:

With daoIndex

.Name = "iSurNm"

'determine if a record with null value in the index field will be included in the index:

.IgnoreNulls = False

.Unique = True

.Fields.Append .CreateField("Surname")

End With

daoTblDef.Indexes.Append daoIndex

'open table-type recordset:

Set recSet = daoDB.OpenRecordset("SalesManager", dbOpenTable)

With recSet

'add new record with a null value for the "Surname" index field:
.AddNew
.Fields("EmployeeId") = 55
.Fields("FirstName") = "Albert"
.Update
'set index for the recordset using the Recordset.Index Property:
.Index = daoIndex.Name

.MoveFirst

 'refer Image 8e showing "SalesManager" table in which new record is added, which will be included in the index depending on the IgnoreNulls property setting when the Required property setting is False (default).
'If IgnoreNulls = False, record with Null value has an index entry and messages returned are: Albert Null, Jim Davis, Sam Green, David Kelly, Jim Mason, Tracy Murray.

'If IgnoreNulls = True, record with Null value does not have an index entry and messages returned are: Jim Davis, Sam Green, David Kelly, Jim Mason, Tracy Murray.

Do While Not .EOF

If IsNull(.Fields("Surname")) Then

MsgBox "First Name: " & .Fields("FirstName") & ", " & "Surname: Null"

Else

MsgBox "First Name: " & .Fields("FirstName") & ", " & "Surname: " & .Fields("Surname")

End If

.MoveNext

Loop

End With

'delete index by reference to its name:
'daoTblDef.Indexes.Delete ("iSurNm")

'close the objects:
recSet.Close
daoDB.Close

'destroy the variables:
Set daoDB = Nothing
Set daoTblDef = Nothing
Set daoIndex = Nothing

Set daoFld = Nothing

End Sub

 

 

 

Recordset.Seek Method

Use the Seek method for a table-type recordset, to locate a record meeting the criteria specified for the current index. If the Seek method finds the record, the located record becomes the current record and the NoMatch property is set to False. However if a record is not found, the current record is undefined and method sets the NoMatch property to True. Syntax: RecordsetObject.Seek(ComparisonExpression, Key1, Key2, ... Key13). Both the arguments of ComparisonExpression and Keys are required to be specified. The ComparisonExpression can be any of the 5 expressions: >, =, >=, <, <=. The Key argument is a value evaluated against the current Index field and you can specify a maximum of 13 keys, wherein Key1 must be of the same data type as that of the current index field.

 

Note that a current Index must be set (using the Index property) for the Recordset before using the Seek method, and in case the index field is not unique (ie. the field has duplicate values) then the first record which satisfies the criteria is located. For comparison expressions of greater than (>), equal (=), or greater than or equal (>=), the search is started at the beginning of the index and moves forward. For comparison expressions of less than (<) or less than or equal (<=), the search is started at the index end and moves backward and if the index end has duplicate entries then search starts randomly from any of these duplicate entries and moves backward. As mentioned earlier, to search through Dynaset-type or Snapshot-type recordsets, you will need to use the Find method (which is slower the Seek method) which is supported by these recordsets.

 

 

Example 9: Use the Seek method for a table-type recordset, to locate a record.

 

Sub AccessDAO_SeekMethod_9()
'Use the Seek method for a table-type recordset, to locate a record meeting the criteria specified for the current index.
'If the Seek method finds the record, the located record becomes the current record and the NoMatch property is set to False. However if a record is not found, the current record is undefined and method sets the NoMatch property to True.

'To use DAO in your Excel VBA project, you must add a reference to the DAO Object Library in Excel (your host application) by clicking Tools-References in VBE.

Dim strMyPath As String, strDBName As String, strDB As String, strSQL As String
Dim daoDB As DAO.Database
Dim daoTblDef As DAO.TableDef
Dim daoIndex As DAO.Index
Dim recBookMark As String
Dim intSeek As Integer

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

'open a database object: assign the database reference to an object variable:
Set daoDB = DBEngine.OpenDatabase(strDB)

'refer to a TableDef object by its name: reference the table named SalesManager
Set daoTblDef = daoDB.TableDefs("SalesManager")

'----------------
'CREATE A PRIMARY KEY INDEX:
Set daoIndex = daoTblDef.CreateIndex("piEmpId")

With daoIndex

.Primary = True

.Required = True

.Fields.Append .CreateField("EmployeeId")

End With

daoTblDef.Indexes.Append daoIndex

'----------------

'Open a table-type recordset to use the Seek method:
Set recSet = daoDB.OpenRecordset("SalesManager", dbOpenTable)

With recSet

'specify the index name wherein to search with the Seek method - set index for the recordset using the Recordset.Index Property:

.Index = "piEmpId"
'save position of current record to which you might want to return later:

recBookMark = .Bookmark

MsgBox recSet.Fields("FirstName")

'specify search value:

intSeek = InputBox("Enter EmployeeID to locate")

'run the Seek meth0d:

.Seek "=", intSeek

'check whether matching record found or not by the Seek method:
If .NoMatch Then

'return the record whose position was saved, if no match is found by the Seek method:

.Bookmark = recBookMark

MsgBox "Not found EmployeeID: " & intSeek & ", reverted to initial record: " & recSet.Fields("FirstName")

Else

'return the matching record found by the Seek method:

MsgBox "Found EmployeeID: " & intSeek & ", whose First Name is: " & recSet.Fields("FirstName")

End If

'close recordset:

.Close

End With

'---------------
'delete index by reference to its name:
daoTblDef.Indexes.Delete "piEmpId"

'---------------
'close the objects:
daoDB.Close

'destroy the variables:
Set daoDB = Nothing
Set recSet = Nothing
Set daoTblDef = Nothing

Set daoIndex = Nothing

End Sub

 

 

 

 

Create Relationship between Fields of Database Tables, using DAO

 

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.

 

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.

 

 

Relation Object

 

A Relation object shows the relationship between fields in tables of a database. Use a Relation object to relate tables by creating a relationship between fields or return existing relationships in a database. A new Relation object is created using the Database.CreateRelation Method. Within the Relations collection of a Database, you can refer a Relation object by its ordinal position or Name property - to reference a Relation named "RelationName", which is the first relation object in the database, you can use any of the following:- DatabaseObject.Relations("RelationName") or DatabaseObject![RelationName] or DatabaseObject.Relations(0).

 

 

Steps to Create a Relation between Key Fields of Tables:

Step 1: Reference the Primary table and Foreign table - assign to object variables (TableDef).

Step 2: Create Index for the Primary table - the Indexed field will be part of the relationship.

Step 3: Create a new Relation object (using the Database.CreateRelation Method), specifying names of the Primary & Foreign tables to be related, and also specifying the Attributes for the relationship type.

Step 4: Create  a new Field object (using the Relation.CreateField Method) for the Relation object; specify Field names of the Primary & Foreign table which will be part of the relationship; and append the new Field object to the Fields collection of the Relation object.

Step 5: Append the new Relation object to the Relations collection of the Database.

 

DAO Database.CreateRelation Method

 

Use the Database.CreateRelation Method to create a new Relation object. Syntax: DatabaseObject.CreateRelation(Name, Table, ForeignTable, Attributes). The Name argument  names the Relation object; the Table argument names the Primary table to be related; The ForeignTable argument names the Foreign table to be related. Note that both the Table and ForeignTable should be existing tables when using this method.

 

The Attributes argument sets the constants (RelationAttributeEnum Enumeration) describing the relationship type viz. dbRelationUnique (value 1) - describes one-to-one relationship; dbRelationUpdateCascade (value 256) - Updates will cascade; dbRelationDeleteCascade (value 4096) - Deletions will cascade; dbRelationLeft (value 16777216) & dbRelationRight (value 33554432) - to determine whether the relationship between fields in the table is left join or right join; and so on. While specifying the Attributes, use the sum of the RelationAttributeEnum Enumeration values when you want to include multiple values viz. dbRelationUpdateCascade + dbRelationDeleteCascade.

 

Use the Delete method on the Relations collection to delete the Relation object - Syntax: DatabaseObject.Relations.Delete "RelationName".

 

 

DAO Relation.CreateField Method

 

To add a field to a Relation object, use the DAO Relation.CreateField Method, Syntax: RelationObject.CreateField(Name, Type, Size). All arguments are optional to specify. The Name argument specifies a name for the new Field. The type and size arguments are not supported for a Relation object, and are ignored in this case. If the Name argument is not specified in the method, you can set the Name property thereafter but before the Field is appended to the Fields collection of the Relation object.

 

To delete a field from the Fields Collection, use the DAO Fields.Delete Method. Note that once an index referencing a field has been created, that Field cannot be deleted from a Fields collection of a TableDef object.

 

 

DAO Relation.Name Property

 

Use the Name property to set or return the name (a string value with maximum length of 64 characters) of a Relation object. The property is read/write before the Relation object is appended to a collection and read-only after appending. Syntax: RelationObject.Name.

 

 

DAO Relation.ForeignTable Property

 

A Relation object shows the relationship between fields of database tables, referred to as Primary Table & Foreign Table. The Relation.ForeignTable Property is used to set (or return) the Foreign table's name in the relationship. The property is read/write before the Relation object is appended to a collection and read-only after it is appended. Syntax: RelationObject .ForeignTable. The Foreign table is referred to by the TableDef object's name property viz. TableDefObject.Name.

 

 

DAO Field.ForeignName Property

 

A Relation object shows the relationship between fields of database tables, referred to as Primary Table & Foreign Table. You create  a new Field object (using the Relation.CreateField Method) for the Relation object; specify Field names of the Primary & Foreign table which will be part of the relationship; and append the new Field object to the Fields collection of the Relation object. The Field.ForeignName Property is used to set (or return) the Field name in the Foreign table and the Field.Name Property is used to set the corresponding Field name in the Primary table of the relationship. Syntax of ForeignName property: FieldObject.ForeignName.

 

 

Example 10: Create a Relation between Two Tables using the Database.CreateRelation Method.

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

 

 

 

 

Sub AccessDAO_TablesCreateRelation_10()
'Create a Relation between Two Tables, using DAO - relationship between fields in two TableDef (or QueryDef) objects using the Database.CreateRelation Method.
'refer Image 10a to view the existing SalesManager Table in MS Access file "SalesReport.accdb".
'refer Image 10b to view the existing Performance Table in MS Access file "SalesReport.accdb".
'refer Image 10c to view the SalesManager Table, after Creating Relation by running below code.

'To use DAO in your Excel VBA project, you must add a reference to the DAO Object Library in Excel (your host application) by clicking Tools-References in VBE.

Dim strMyPath As String, strDBName As String, strDB As String
Dim daoDB As DAO.Database
Dim tblDefSM As DAO.TableDef, tblDefP As DAO.TableDef
Dim daoIndx As DAO.Index
Dim daoRel As DAO.Relation
Dim daoFld As DAO.Field

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

'open a database object: assign the database reference to an object variable:
Set daoDB = DBEngine.OpenDatabase(strDB)

'----------------
'Steps to Create a Relation between Key Fields of Tables:

'Step 1: Reference the Primary table and Foreign table - assign to object variables (TableDef).
'Step 2: Create Index for the Primary table - the Indexed field will be part of the relationship.
'Step 3: Create a new Relation object (using the Database.CreateRelation Method), specifying names of the Primary & Foreign tables to be related, and also specifying the Attributes for the relationship type.
'Step 4: Create  a new Field object (using the Relation.CreateField Method) for the Relation object; specify Field names of the Primary & Foreign table which will be part of the relationship; and append the new Field object to the Fields collection of the Relation object.

'Step 5: Append the new Relation object to the Relations collection of the Database.

'specify and reference "SalesManager" as the primary table:
Set tblDefSM = daoDB.TableDefs("SalesManager")
'specify and reference "Performance" as the foreign table:
Set tblDefP = daoDB.TableDefs("Performance")

'Create Index for the primary table "SalesManager". The indexed field will create relationship with the foreign table's field:
Set daoIndx = tblDefSM.CreateIndex("indxEmpId")

With daoIndx

.Required = True

.Unique = True

.Fields.Append .CreateField("EmployeeId")

End With

tblDefSM.Indexes.Append daoIndx

'Create Relation object named relSalesPerf, specifying names of the primary & foreign tables to be related, and also specifying the attributes for the relationship type.
Set daoRel = daoDB.CreateRelation("relSalesPerf", tblDefSM.Name, tblDefP.Name, dbRelationUpdateCascade)

'create field object for the Relation object daoRel and append to its fields collection.

With daoRel

'specify field name of the primary table which has been indexed and will be a part of the relationship:

Set daoFld = .CreateField("EmployeeId")

'specify field name of the foreign table which will be a part of the relationship:

daoFld.ForeignName = "EmployeeId"

'Append the new field object to fields collection of the relation object:

daoRel.Fields.Append daoFld

End With

 

'append the new relation object daoRel to the Relations collection of the Database.

daoDB.Relations.Append daoRel

'delete relation:
'daoDB.Relations.Delete "relSalesPerf"
'delete Index:
'tblDefSM.Indexes.Delete "indxEmpId"

'---------------
'close the objects:
daoDB.Close

'destroy the variables:
Set daoDB = Nothing
Set tblDefSM = Nothing
Set tblDefP = Nothing
Set daoFld = Nothing
Set daoRel = Nothing

Set daoIndx = Nothing

End Sub

 

 

 

 

Create and Exceute a Query, including Action & Parameter Query, using DAO

 

SQL statements are referred to as queries. A QueryDef object is a predefined query which is defined, saved and stored in a Microsoft Access database for later use. The CreateQueryDef method is used to create a new QueryDef object - Syntax of DAO Database.CreateQueryDef Method: DatabaseObject.CreateQueryDef(QueryName, SQLText). Both QueryName & SQLText arguments are Optional. This method automatically appends the QueryDef object to the QueryDefs collection if a valid name is specified. A temporary QueryDef object which is not automatically appended to the collection, can be created by using a zero-length string ("") for the QueryName argument. The SQLText argument specifies the SQL statement.

 

If the QueryName and SQLText are not specified while creating the QueryDef object, these can be set later using the object's Name & SQL properties - DAO QueryDef.Name Property & DAO QueryDef.SQL Property. The Name property sets or returns the name of the QueryDef object, which cannot exceed 64 characters. The SQL Property of the QueryDef object sets or returns the SQL statement by which a query is defined for the QueryDef object. To delete the QueryDef object from a QueryDefs collection, use the QueryDefs.Delete Method, Syntax: QueryDefs.Delete(Name).

 

 

To create a Recordset based on a stored query or a parameter query, use the DAO QueryDef.OpenRecordset Method, Syntax: QueryDefObject.OpenRecordset(Type, Options, LockEdit). In this method you first get a reference to the QueryDef object and then use the Set operator to return the recordset. Refer Example 11b in which this method is used.

 

 

Example 11a: Create and Save a Query (QueryDef object).

Refer Image 11 as mentioned in the code.

 

 

Sub AccessDAO_CreateSaveQuery_11a()
'create and save a query (QueryDef object).
'refer Image 11 which shows the SalesManager Table in MS Access file "SalesReport.accdb", and the query "sqlQuery" that is created after running below code.

'To use DAO in your Excel VBA project, you must add a reference to the DAO Object Library in Excel (your host application) by clicking Tools-References in VBE.

Dim strMyPath As String, strDBName As String, strDB As String, strSQL As String
Dim daoDB As DAO.Database
Dim qryD As DAO.QueryDef

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

'If you open a database object without specifying a workspace, it will exist within the default workspace. First assign the database reference to an object variable:
Set daoDB = DBEngine.OpenDatabase(strDB)

'SQL statement assigned to the variable (strSQL).
strSQL = "SELECT * FROM SalesManager WHERE EmployeeId > 18"

'Specify QueryName and SQLText while creating the QueryDef object. The QueryDef object is automatically appended to the QueryDefs collection.
Set qryD = daoDB.CreateQueryDef("sqlQuery", strSQL)

'---------------
'close the objects:
daoDB.Close

'destroy the variables:
Set daoDB = Nothing

Set qryD = Nothing

End Sub

 

 

 

Example 11b: Open Recordset based on a saved Query; Delete the QueryDef object.

Refer Image 11 as mentioned in the code.

 

Sub AccessDAO_OpenRecordSetSavedQuery_11b()
'Open Recordset based on a saved Query; return the first two fields of each record; delete the QueryDef object.

'To use DAO in your Excel VBA project, you must add a reference to the DAO Object Library in Excel (your host application) by clicking Tools-References in VBE.

Dim strMyPath As String, strDBName As String, strDB As String
Dim daoDB As DAO.Database
Dim recSet As DAO.Recordset
Dim qryD As DAO.QueryDef

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

'---------------
'Assign the database reference to an object variable:
Set daoDB = DBEngine.OpenDatabase(strDB)

'Open snapshot-type recordset based on a saved query:
Set recSet = daoDB.OpenRecordset("sqlQuery", dbOpenSnapshot)
'alternate code - creates recordset using QueryDef.OpenRecordset Method:
'Set qryD = daoDB.QueryDefs("sqlQuery")
'Set recSet = qryD.OpenRecordset(dbOpenDynaset)

'return the first two fields of each record of the Recordset:

'Three records are returned, with reference to Image 11: EmployeeId - 21, FirstName - Jim; EmployeeId - 35, FirstName - David; EmployeeId - 56, FirstName - Sam.

Do While Not recSet.EOF

MsgBox recSet.Fields(0).Name & " - " & recSet.Fields(0)

MsgBox recSet.Fields(1).Name & " - " & recSet.Fields(1)

recSet.MoveNext

Loop

'delete the QueryDef object:
daoDB.QueryDefs.Delete ("sqlQuery")

'---------------
'close the objects:
recSet.Close
daoDB.Close

'destroy the variables:
Set daoDB = Nothing
Set qryD = Nothing

Set recSet = Nothing

End Sub

 

 

 

Example 12a: Create and Save a QueryDef object, with a Parameter Query.

Refer Image 12 as mentioned in the code.

 

 

Sub AccessDAO_CreateParameterQuery_12a()
'create and save a QueryDef object, with a parameter query.
'refer Image 12 which shows the SalesManager Table in MS Access file "SalesReport.accdb", and the parameter query "ParamQuery" that is created after running below code.

'To use DAO in your Excel VBA project, you must add a reference to the DAO Object Library in Excel (your host application) by clicking Tools-References in VBE.

Dim strMyPath As String, strDBName As String, strDB As String, strSQL As String
Dim daoDB As DAO.Database
Dim qryD As DAO.QueryDef

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

'---------------
'assign the database reference to an object variable:
Set daoDB = DBEngine.Workspaces(0).OpenDatabase(strDB)

'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, is constructed with parameters, and assigned to the variable (strSQL).
strSQL = "PARAMETERS startDate DateTime, endDate DateTime; SELECT * FROM SalesManager WHERE JoinDate between startDate and endDate"

'Create a QueryDef object, with the parameter query name "ParamQuery". The QueryDef object is automatically appended to the QueryDefs collection.
Set qryD = daoDB.CreateQueryDef("ParamQuery")

'Because the SQLText was not specified while creating the QueryDef object, it is being set later using the object's property:
qryD.Sql = strSQL

'alternatively, specify QueryName and SQLText while creating the QueryDef object:
'Set qryD = daoDB.CreateQueryDef("ParamQuery", strSQL)

'---------------
'close the objects:
daoDB.Close

'destroy the variables:
Set daoDB = Nothing

Set qryD = Nothing

End Sub

 

 

 

Example 12b: Open Recordset based on a saved Parameter Query; Delete the QueryDef object.

Refer Image 12 as mentioned in the code.

 

Sub AccessDAO_OpenRecordSetParameterQuery_12b()
'Open Recordset based on a saved parameter Query; return the first two fields of each record; delete the QueryDef object.

'To use DAO in your Excel VBA project, you must add a reference to the DAO Object Library in Excel (your host application) by clicking Tools-References in VBE.

Dim strMyPath As String, strDBName As String, strDB As String, strSQL As String
Dim daoDB As DAO.Database
Dim recSet As DAO.Recordset
Dim qryD As DAO.QueryDef

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

'---------------
'assign the database reference to an object variable:
Set daoDB = DBEngine.Workspaces(0).OpenDatabase(strDB)

'set the parameter query:
Set qryD = daoDB.QueryDefs("ParamQuery")

'set value for parameters:
qryD.Parameters("startDate") = "04/01/2011"
qryD.Parameters("endDate") = Now

'Open Snapshot-type Recordset based on a parameter query:
Set recSet = qryD.OpenRecordset(dbOpenSnapshot)

'return the first two fields of each record of the Recordset:

'Two records are returned, with reference to Image 12: EmployeeId - 18, FirstName - Tracy; EmployeeId - 56, FirstName - Sam.

Do While Not recSet.EOF

MsgBox recSet.Fields(0).Name & " - " & recSet.Fields(0)

MsgBox recSet.Fields(1).Name & " - " & recSet.Fields(1)

recSet.MoveNext

Loop

'delete the QueryDef object:
daoDB.QueryDefs.Delete ("ParamQuery")

'---------------
'close the objects:
recSet.Close
daoDB.Close

'destroy the variables:
Set daoDB = Nothing
Set qryD = Nothing

Set recSet = Nothing

End Sub

 

 

 

Run an Action Query or Execute SQL Statement

The Database.Execute Method is used to run an action query or execute an SQL statement. Syntax: DatabaseObject.Execute(Query, Options). The Query argument is necessary to specify, it is a string value representing the name of the action query you want to run or an SQL statement you want to execute. The Options argument is optional, and can be any of the specified constants viz. dbInconsistent (Default - Executes inconsistent updates),  dbDenyWrite (write permission denied to other users),  dbFailOnError (updates are rolled back on accurence of an error), and so on. Note that you can run only action queries using the Execute method.

 

You can run the Execute method both from a QueryDef object (QueryDef.Execute Method) or a Database object (Database.Execute Method). Syntax for QueryDef.Execute Method: QueryDefObject.Execute(Options).

 

 

Example 13: Use the DAO Execute Method to run an action query, from a QueryDef object or a Database object.

Refer Images 13a & 13b, as mentioned in the code.

 

 

 

Sub AccessDAO_ExecuteActionQuery_13()
'Use the DAO Execute Method to run an action query, from a QueryDef object or a Database object.
'refer Image 13a to view the existing SalesManager Table in MS Access file "SalesReport.accdb".
'refer Image 13b to view the SalesManager Table, after running below code.

'To use DAO in your Excel VBA project, you must add a reference to the DAO Object Library in Excel (your host application) by clicking Tools-References in VBE.

Dim strMyPath As String, strDBName As String, strDB As String, strSQL As String
Dim daoDB As DAO.Database
Dim recSet As DAO.Recordset
Dim qryD As DAO.QueryDef

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

'assign the database reference to an object variable:
Set daoDB = DBEngine.Workspaces(0).OpenDatabase(strDB)

'---------------
'SQL statement assigned to the variable (strSQL). The sql will change the FirstName from John to Johnny in the SalesManager table.
strSQL = "UPDATE SalesManager SET FirstName = 'Johnny' WHERE FirstName = 'John'"

'Specify QueryName and SQLText while creating the QueryDef object. The QueryDef object is automatically appended to the QueryDefs collection.
Set qryD = daoDB.CreateQueryDef("sqlQuery", strSQL)
MsgBox "query created"

'run an action query using the Database.Execute Method:
daoDB.Execute "sqlQuery"
'alternate code to run the action query:
'qryD.Execute
MsgBox "query executed"

'----
'set recordset:
Set recSet = daoDB.OpenRecordset("SalesManager")

'return the FirstName field of each record in the Recordset:

Do While Not recSet.EOF

MsgBox recSet.Fields("FirstName")

recSet.MoveNext

Loop

'delete the QueryDef object:
'daoDB.QueryDefs.Delete ("sqlQuery")

'---------------
'close the objects:
recSet.Close
daoDB.Close

'destroy the variables:
Set daoDB = Nothing
Set qryD = Nothing

Set recSet = Nothing

End Sub

 

 

 

Example 14: Use the DAO Database.Execute Method to execute an SQL statement, from a Database object.

Refer Images 13a & 14, as mentioned in the code.

 

 

Sub AccessDAO_ExecuteSqlStatement_14()
'Use the DAO Database.Execute Method to execute an SQL statement, from a Database object.
'refer Image 13a to view the existing SalesManager Table in MS Access file "SalesReport.accdb".
'refer Image 14 to view the SalesManager Table, after running below code.

'To use DAO in your Excel VBA project, you must add a reference to the DAO Object Library in Excel (your host application) by clicking Tools-References in VBE.

Dim strMyPath As String, strDBName As String, strDB As String, strSQL As String
Dim daoDB As DAO.Database
Dim recSet As DAO.Recordset

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

'assign the database reference to an object variable:
Set daoDB = DBEngine.Workspaces(0).OpenDatabase(strDB)

'---------------
'Execute an SQL Statement (action sql) using the Database.Execute Method:
'The sql statement will change the FirstName from John to Johnny in the SalesManager table.
daoDB.Execute "UPDATE SalesManager SET FirstName = 'Johnny' WHERE FirstName = 'John'"
MsgBox "sql executed"

'----
'set recordset:
Set recSet = daoDB.OpenRecordset("SalesManager")

'return the FirstName field of each record in the Recordset:

Do While Not recSet.EOF

MsgBox recSet.Fields("FirstName")

recSet.MoveNext

Loop

'---------------
'close the objects:
recSet.Close
daoDB.Close

'destroy the variables:
Set daoDB = Nothing

Set recSet = Nothing

End Sub

 

 

 

Example 15: Use the DAO QueryDef.Execute Method to run a Parameter Action Query, from a QueryDef object.

Refer Images 13a & 15, as mentioned in the code.

 

 

Sub AccessDAO_ExecuteParameterQuery_15()
'Use the DAO QueryDef.Execute Method to run a parameter action query, from a QueryDef object.
'refer Image 13a to view the existing SalesManager Table in MS Access file "SalesReport.accdb".
'refer Image 15 to view the SalesManager Table, after running below code.

'To use DAO in your Excel VBA project, you must add a reference to the DAO Object Library in Excel (your host application) by clicking Tools-References in VBE.

Dim strMyPath As String, strDBName As String, strDB As String, strSQL As String
Dim daoDB As DAO.Database
Dim recSet As DAO.Recordset
Dim qryD As DAO.QueryDef

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

'---------------
'assign the database reference to an object variable:
Set daoDB = DBEngine.Workspaces(0).OpenDatabase(strDB)

'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, is constructed with parameters, and assigned to the variable (strSQL).
strSQL = "PARAMETERS startDate DateTime, endDate DateTime; DELETE * FROM SalesManager WHERE JoinDate between startDate and endDate"

'Create a QueryDef object, with the parameter query name "ParamQuery". The QueryDef object is automatically appended to the QueryDefs collection.
Set qryD = daoDB.CreateQueryDef("ParamQuery", strSQL)
MsgBox "query created"

'enter parameters for the parameter query and run query using the Execute Method:
qryD.Parameters("[startDate]") = "01/01/2009"
qryD.Parameters("[endDate]") = "12/31/2010"
qryD.Execute
MsgBox "query executed"

'delete the QueryDef object:
'daoDB.QueryDefs.Delete ("ParamQuery")

'---------------
'close the objects:
daoDB.Close

'destroy the variables:
Set daoDB = Nothing

Set qryD = Nothing

End Sub