0% found this document useful (0 votes)

413 views

Microsoft Access - ActiveX Data Objects Library (ADO)

This document provides an overview of connecting to Microsoft Access databases from Excel using ActiveX Data Objects (ADO). It discusses ADO objects and the programming model, connecting to an Access data source using the ADO Connection object, and accessing records and fields from database tables. It also covers using ADO to execute SQL statements, import/export data between Access and Excel, and using ADOX extensions to create and manage database objects like tables and indexes.

Uploaded by

Yamini Shinde

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as DOCX, PDF, TXT or read online on Scribd

0% found this document useful (0 votes)

413 views

Microsoft Access - ActiveX Data Objects Library (ADO)

Uploaded by

Yamini Shinde

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as DOCX, PDF, TXT or read online on Scribd

You are on page 1/ 136

1 Microsoft Access: ActiveX Data Objects (ADO), Connect with Access Databases from Excel................2
1.1 Microsoft Access: ActiveX Data Objects Library (ADO)..........................................................................2
A) Connect with Databases using DAO, RDO and ADO Objects................................................................2
B) ADO Objects & Programming Model:...................................................................................................... 3
C) Connecting to a Data Source, viz. Microsoft Access, using the ADO Connection Open Method............4
D) Access records from a database table.................................................................................................... 8
E) Access fields in a Recordset................................................................................................................. 10
1.2 Microsoft Access: Use ADO to Execute SQL statements.....................................................................15
A) Use ADO to Execute SQL statements to Manage your Database........................................................15
B) SQL Commands explained................................................................................................................... 15
C) Using the ADO connection Execute method to execute the specified query and SQL statements.......16
D) Use the OpenSchema Method to access information about database tables and columns..................20
E) Create a database table using ADO, with SQL statements...................................................................22
F) ADO Find Method - Find or Locate a specific Record...........................................................................25
G) ADO Filter Property to FilterRecords..................................................................................................... 27
1.3 Import or Export Data from Access to Excel using ADO.......................................................................32
A) Use ADO to Import Data from Microsoft Access Database to Excel.....................................................32
B) Using ADO to Import data from Access Table to Excel worksheet (your host application)...................32
C) Using ADO to Export data from worksheet (your host application) to Access Table.............................36
1.4 Microsoft Access: ActiveX Data Objects Extensions (ADOX)...............................................................39
A) Create an Access Database using ADOX.............................................................................................39
B) ADOX Table Object & Tables Collection - Properties & Methods.........................................................40
C) ADOX Column Object & Columns Collection - Properties & Methods...................................................41
D) Create an Index using ADOX................................................................................................................ 51
E) Create Relationship between Tables, using ADOX...............................................................................55
F) ADOX View Object, Parameter, Create & Execute Stored Queries / Action Queries with ADOX.........58
2 Connecting to Microsoft Access Database from Excel VBA, using DAO Object Model.........................72
2.1 Data Access Objects Library (DAO), Connect with Access Databases from Excel...............................72
A) Connect with Databases using DAO, RDO and ADO Objects..............................................................72
B) DAO Objects & Programming model..................................................................................................... 73
C) The DBEngine object & workspace object............................................................................................74
D) Tables of a DAO Database................................................................................................................... 81
E) Fields / Columns of a Table.................................................................................................................. 82
F) Recordset & Records of a DAO Database Table..................................................................................90
2.2 Create Index/Relationship between Fields, Create and Exceute a Query...........................................106
A) Create an Index using DAO................................................................................................................ 106
B) Create Relationship between Fields of Database Tables, using DAO................................................116
C) Create and Exceute a Query, including Action & Parameter Query, using DAO.................................120
2.3 Microsoft Access DAO Object Model: Import or Export Data from Access to Excel............................130
1 Microsoft Access: ActiveX Data Objects (ADO), Connect with Access Databases from Excel

Objects also have event procedures attached to them. Events are actions performed, or occurences, which
trigger a VBA code or macro. An event procedure (ie. a vba code) is triggered when an event occurs such

To connect with other databases, when working in VBA, you can use either DAO (Data Access Objects),
RDO (Remote Data Objects) or ADO (ActiveX Data Objects). After connecting to a database, you can
manipulate its data. DAO, RDO and ADO are data access interfaces ie. they are object and programming
models used to access data. Earlier, DAO was used to interface with local databases (viz. MS Access),
RDO was used to interface with large databases such as Oracle and SQL Server. ADO was their
replacement to interface with all types of data sources. Both DAO and ADO are commonly used while
connecting to Microsoft Access Database.

1.1 Microsoft Access: ActiveX Data Objects Library (ADO).

A) Connect with Databases using DAO, RDO and ADO Objects

To connect with other databases, when working in VBA, you can use either DAO (Data Access Objects),
RDO (Remote Data Objects) or ADO (ActiveX Data Objects). After connecting to a database, you can
manipulate its data.

DAO, RDO and ADO are data access interfaces ie. they are object and programming models used to
access data. Earlier, DAO was used to interface with local databases (viz. MS Access), RDO was used to
interface with large databases such as Oracle and SQL Server. ADO was their replacement to interface with
all types of data sources. Both DAO and ADO are commonly used while connecting to Microsoft Access
Database. DAO is native to Access, the DAO object library is the default reference in Access 2007 and the
library will be existing when you use Access (ADO object library was the default reference in Access 2000
and 2002, whereas DAO returned as the default object library with Access 2003 after being the default in
Access 97 earlier). DAO integrates well with Access databases and provides faster access. ADO provides
access to a wider variety of data sources than DAO, besides Access. ADO has succeeded DAO and is the
latest data access technology, is simpler and more flexible, and interfaces with Microsoft's powerful data
access technology of OLE DB. In ADO the objects are less than in DAO, and it contains more properties,
methods and events. ADO/OLE DB is recommended for new projects but it might not be worthwhile to
convert DAO code to ADO for existing projects.

ADO creates a reference to the database using the Connection object, to connect to the data source. You
use the Open and Close methods to open and close a Connection object. DAO creates a reference to the
database using the database object, to connect to the data source.

In Microsoft Access, Recordset objects are used to access and manipulate data in a database. A Recordset
object represents a set of records in a database table, or a set of records returned from running a query.
Both DAO and ADO libraries have a Recordset object, though the methods, properties, and options of the
respective object is different. A Record object is one row of data in a Recordset. A Recordset object has a
Fields collection which contains all the Field objects, where each Field object represents a column in the
Recordset. In other words, each record represents a row of data and contains many fields, and each field
corresponds to a column in the database table.

In your VBA code, you should ideally precede the object name by its program ID (ProgID) prefix, which in
ADO is "ADODB" and in DAO is "DAO". Many objects, for example the Recordset object, have similar
names in both DAO and ADO and it is advisable to have explicit references in your project. This becomes a
must if you have included references to both the DAO and ADO libraries in your VBA project, else the object
library mentioned first in the References list will prevail, resulting in confusion in the vba code.

While instantiating the Recordset object, you should use:

Dim daoRecSet As DAO.Recordset
Dim adoRecSet As ADODB.Recordset
instead of:
Dim RecSet As Recordset

B) ADO Objects & Programming Model:

ADO stands for ActiveX Data Objects, and it enables an application (viz. Excel) to access and manipulate
data from a database (viz. Access), interfacing with an OLE DB provider. ADO is used with many
programming languages, including Microsoft Visual Basic, and an ADO version is installed with Microsoft
Office.

OLE DB is a data access technology of Microsoft, providing access to a data source using a COM
component. The COM component is a binary file (supporting the COM standard) which is the software that
exposes data to an ADO application, and is called a data provider, also referred to as an OLE DB provider.
COM (Component Object Model) is a binary-interface standard used to enable interaction between objects.
A wide variety of data sources can be accessed with OLE DB providers.

A database engine is the underlying software component of a database used to manipulate its data. Jet
(Joint Engine Technology) is used by Microsoft Access as its database engine. The Microsoft Jet 4.0 OLE
DB Provider is used to work with the Jet database engine which opens and provides access to an Access
database, hence the Provider property of the ADO Connection object should specify the Jet 4.0 OLE DB
provider before opening the connection. ADO uses the Connection object to connect to the data source, to
open an Access Database. For Access 2007 (.accdb database), you will need to change the Provider to
"Microsoft.ACE.OLEDB.12.0", ACE (Access Connectivity Engine) being Jet's successor.

Image 1

To use ADO in your VBA project, you must add a reference to the ADO Object Library in Excel (your host
application) by clicking Tools-References in VBE, and then choose an appropriate version (mostly, you
should choose the highest version number) of Microsoft ActiveX Data Objects x.x Library from the list. The
ADO version depends on your Windows operating system, viz. Microsoft ActiveX Data Objects 6.1 Library
for Windows 7 or later, Microsoft ActiveX Data Objects 6.0 Library for Windows Vista, and Microsoft ActiveX
Data Objects 2.8 Library for Windows XP. In Windows Vista or in later versions of Windows only one of the
ADO version 6.0 or version 6.1 will be available in the Reference list at one time. Refer Image 1 for available
libraries, wherein 'Microsoft ActiveX Data Objects 6.1 Library' has been selected.

ADOX: 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 database
tables in ADO using SQL.

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.

C) Connecting to a Data Source, viz. Microsoft Access, using the ADO Connection Open Method

Use the Open Method (ADO Connection) to physically establish a connection to a data source.
Syntax: ConnectionObject.Open ConnectionString, UserID, Password, Options. ConnectionString
argument is a string value which contains information for connecting to a data source. All four arguments of
the Open Method are optional, and it is common to only use the ConnectionString argument to establish a
connection (specifying values for Provider and the Data Source in the ConnectionString).

The ConnectionString argument has a series of values which contain information for connecting to a data
source. Each value statement is in the format of "keyword=value" and is separated by semicolons, within the
quoted ConnectionString. 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. It may be noted that Keywords are not case sensitive, and spaces between "keyword=value"
pairs are ignored.

Specify the provider name to be used for the connection, in the ConnectionString as,
"Microsoft.Jet.OLEDB.4.0". For Access 2007 (.accdb database), you will need to change the Provider to
"Microsoft.ACE.OLEDB.12.0". This driver, provided by Microsoft, was released with Office 2007 and can
connect to data sources including Access 2007, Excel 2007 and Access / Excel 97-2003.
Microsoft.Jet.OLEDB.4.0 is not available in 64 bit. Jet has been installed in and works with all windows 32-
bit systems, and its successor ACE is available in both 32 bit and 64 bit versions and compatible with both
the Access mdb & accdb files. To download the "Microsoft.ACE.OLEDB.12.0" driver, click
http://www.microsoft.com/en-us/download/details.aspx?id=13255.

The ACE driver is supported by the operating systems: Windows 8; Windows 7; Windows Server 2003 R2
(32-Bit x86); Windows Server 2003 R2 x64 editions; Windows Server 2008 R2; Windows Server 2008
Service Pack 2; Windows Vista Service Pack 1; Windows XP Service Pack 3. Only the 32-bit Access
Database Engine may be used on Windows XP Service Pack 3.

Use the Mode property of the Connection object to specify the mode for opening a database, before calling
the open method, because this property can be set only on a closed Connection object.

The default Mode property value for a Connection is adModeUnknown indicating that the permissions have
not yet been set or cannot be determined. This is the default value used by the ADO connection object when
not specified by the user, however the ADO provider named in the connection string will determine the
access mode. The Microsoft.Jet.OLEDB.4.0 and Microsoft.ACE.OLEDB.12.0 providers both, by Default,
open a database connection in shared-access mode ie. adModeShareDenyNone.

To open a database in shared-access mode where neither read nor write access can be denied to others,
set the value as adModeShareDenyNone. To open a database in shared read-only mode, set the value as
adModeRead, and to prevent others from opening a connection set the value as adModeShareExclusive.
Other values are: Write-only (adModeWrite), Read/Write (adModeReadWrite), Prevents others from opening
in read mode (adModeShareDenyRead), Prevents others from opening in write mode
(adModeShareDenyWrite), Prevents others from opening in read/write mode
(adModeShareDenyExclusive).

While using Jet/ACE database engine to access data in file formats other than MS Access database, viz.
Excel, "Extended Properties" specifies a string containing connection information which is provider-
specific, that cannot be specified through standard ADO properties.

When to add the optional HDR= setting to the Extended Properties of the connection string: if the first row
contains column names and not data, use the setting HDR=YES, which is also the default. Specifying
HDR=No will be a must if you do not have column headings.

The Extended Properties parameter should also refer to the appropriate Excel version.

The Connection Object

A two-line code to instantiate an ADO object

'declare variable as a specific object type:
Dim connDB As ADODB.Connection
'set the object variable to create a new instance of the connection object - a new object reference is created
by using the New keyword:
Set connDB = New ADODB.Connection

A single-line code to instantiate an ADO object

'this uses Dim (to declare) with the New keyword (to instantiate):
Dim connDB As New ADODB.Connection

Instantiate an object using Late Binding - you need not add a reference to the ADO library in Excel
(your host application)

'declare variable as Object type, which can be a reference to any object:
Dim connDB As Object
'set the object variable to create a new instance of the connection object, using the CreateObject method:
Set connDB = CreateObject("ADODB.Connection")

Establish Connection to a Data Source

Note: In the below examples, connDB is the connection object, instantiated as shown above.

1. With a single-line code you can establish a connection to a data source calling the Connection object's
Open method and specifying connection values for the ConnectionString argument. Each value statement is
separated by semicolons within the quoted ConnectionString, as shown below.

In a single-line, specify the Connection object arguments, with values for the ConnectionString argument.

(i) opening a connection with a Jet provider (pre - MS Access 2007 databases)

connDB.Open ConnectionString:="Provider=Microsoft.Jet.OLEDB.4.0; data
source=C:\Users\Documents\SalesReport.accdb; User ID=john, Password=123"

(ii) connecting with MS Access 2007 databases (.accdb files):

---------------------
'connect to database with the Mode value adModeShareExclusive ie. prevent others from opening a
connection:
connDB.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0; data
source=C:\Users\Documents\SalesReport.accdb; Mode =" & adModeShareExclusive

---------------------
Dim strMyPath As String, strDBName As String, strDB As String

'your data source with which to establish connection - 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

'instantiate an ADO object:

Dim connDB As New ADODB.Connection

'connect to database:
connDB.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0; data source=" & strDB

---------------------
'open a password protected file:
connDB.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0; data
source=C:\Users\Documents\SalesReport.accdb; Jet OLEDB:Database Password=123"

2. Another way to establish a connection to a data source is that before opening the Connection object
you can set its ConnectionString property. The ConnectionString property contains information for
connecting to a data source and is a series of value statements separated by semicolons. The
ConnectionString property values are set before calling the Open method, as shown below.

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

'your data source with which to establish connection - 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

'instantiate an ADO object

Dim connDB As New ADODB.Connection

'connect to database
With connDB
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Mode = adModeShareDenyNone
.Open strDB
End With

To connect to Excel files:

Use the Office 2007 driver Microsoft.ACE.OLEDB.12.0 to connect to Excel 2007 (.xlsx) files:
connDB.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data
Source=C:\Users\Documents\SampleWB.xlsx;Extended Properties='Excel 12.0;HDR = YES'"

Use the Office 2007 driver Microsoft.ACE.OLEDB.12.0 to connect to older 97-2003 (.xls) Excel files:
connDB.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data
Source=C:\Users\Documents\SampleWB.xls;Extended Properties='Excel 8.0;HDR = YES'"

D) Access records from a database table

After connecting to a database, you can manipulate its data. In Microsoft Access, Recordset objects are
used to access and manipulate data in a database. A Recordset object represents a set of records in a
database table, or a set of records returned from running a query. Both DAO and ADO libraries have a
Recordset object, though the methods, properties, and options of the respective object is different. A Record
object is one row of data in a Recordset. A Recordset object has a Fields collection which contains all the
Field objects, where each Field object represents a column in the Recordset. In other words, each record
represents a row of data and contains many fields, and each field corresponds to a column in the database
table.

The ADO Recordset Open Method, to open an ADO Recordset object

Use the Open Method (ADO Recordset) to access data / records from a table - it opens a cursor (ie. a
pointer) to a record, or a range of records, within the Recordset. Syntax: recordset.Open Source,
ActiveConnection, CursorType, LockType, Options. All arguments are optional. On opening a
Recordset, the first record is the current record.

Source argument refers to the source of the record(s), and Source often mentions an SQL statement, or it
mentions a table name containing a recordset, while using the ADO Recordset Open Method. Source can
also be a valid Command object, a stored procedure call, a URL or a file name. ActiveConnection is a valid
connection object variable (or a string of ConnectionString values) specifying the connection in which to
open the Recordset object.

The default CursorType used by the provider when opening the Recordset, is adOpenForwardOnly which
allows only forward movement. Cursor type adOpenStatic allows all movement (forward or backward)
through records and changes being made by other users who are working on the Recordset are not visible
or shown, so that all records appear the same way since the Recordset was opened. Cursor type
adOpenDynamic also allows all movement (forward or backward) through records and changes being made
by other users are visible or shown. Cursor type adOpenKeyset is similar to adOpenDynamic, it allows all
movement (forward or backward) through records and data changes made by other users are visible,
however, any new records added or records deleted by other users are not visible. Cursor type
adOpenUnspecified does not specify a cursor.

LockType mentions the type of lock to be placed when records are being edited. Optimistic locking
(adLockOptimistic) locks records only when you are updating a record (ie. when the Update Method is
called), and the same record can be read and edited by other users while it is open. Pessimistic locking
(adLockPessimistic) locks a record as soon as you start editing, and the same record cannot be read or
edited by other users until you either save or cancel changes by using the Update or CancelUpdate methods
respectively. Optimistic batch updates (adLockBatchOptimistic) LockType is used for making multiple
changes (ie. changes to multiple records at the same time) and then writing them to the data source only
when the UpdateBatch method is called, and till then any Recordset changes that were made will not be
updated. Default type is adLockReadOnly ie. read only records, wherein data cannot be edited.

Options is a Long value used to determine how the Source argument is to be evaluated, provided it is not a
Command object. Though this is an optional argument, it is used to evaluate the Source argument optimally,
viz. if you are aware of the Source type (say, it is a table name), then using the adCmdTable value for this
argument will make ADO directly evaluate Source as a table name whose columns are all returned by an
SQL query. Other values are: adCmdUnspecified - does not specify how to evaluate; adCmdText -
evaluates Source as a textual definition of a command or stored procedure call; adCmdStoredProc -
evaluates Source as a stored procedure name; adCmdUnknown (default) - type of command in the Source
argument is not known; adCmdFile - evaluates Source as the file name of a persistently stored Recordset;
adCmdTableDirect - evaluates Source as a table name whose columns are all returned. You will encounter
an error if there is a mis-match between the Options argument and the Source type.

Create a new record using the Recordset Object's AddNew method

Create a new record in a table, using the AddNew method (of the Recordset object). Syntax:
RecordsetObject.AddNew FieldList, Values. Both arguments are optional. FieldList is the Field Name or
ordinal position of the field, and it can be a single name or an array of names. If FieldList is an array of
names, then Values should also be an array of values for the fields, of the same number and in the same
order of field names.

Update Method (ADO Recordset)

After creating a new record using the AddNew method or after changing a field value of a record, you must
use the Update method (of Recordset object) to save changes to the current record. After using the Update
method, the current record will remain the current one.

Close method

You should close a Connection, a Record or a Recordset, by using the Close method, which will free any
associated system resources. Active Recordset objects associated with the connection also get closed when
you close a Connection object. Closing an object is not enough to remove it from memory, for which you
need to set the object variable to Nothing, after closing the object.

To close a Recordset: RecordsetObject.Close
To close a Connection: ConnectionObject.Close
To destroy the Recordset variable: Set RecordsetObject = Nothing
To destroy the Connection variable: Set ConnectionObject = Nothing

Delete Method (ADO Recordset)

Use the ADO Recordset Delete Method, to delete the current record or a group of
records. Syntax: RecordsetObject.Delete AffectRecords. The AffectRecords argument determines how
many records will the delete method affect, with the default value being adAffectCurrent which affects the
current record only. The deleted record remains the current record even after being deleted, and you can
use the Move method to move to a different record.

Moving between Records in a Recordset

Move Method (ADO). Syntax: RecordsetObject.Move NumRecords, Start. This method moves the
position of the current record as per the specified number of records (NumRecords argument) from a
starting location (Start argument value adBookmarkCurrent indicates current record, adBookmarkFirst
indicates first record and adBookmarkLast indicates the last record - defalut value of this optional argument
is adBookmarkCurrent). It is necessary to specify the NumRecords argument, and if this is more than 0 the
current record moves forward towards end of recordset, and if less than 0 then the current record moves
backwards.

MoveFirst, MoveLast, MoveNext, and MovePrevious Methods (ADO): MoveFirst method moves the
current record to the first record. Using the MoveLast method moves the current record to the last record in
the set of records. MoveNext method moves the current record one position forward and MovePrevious
moves the current record one position backward. Syntax: RecordsetObject.MoveFirst,
RecordsetObject.MoveLast, RecordsetObject.MoveNext, RecordsetObject.MovePrevious.

EOF Property (ADO) indicates whether the current record position is after the last record in the set of records, wherein its value will
be TRUE. BOF Property (ADO) indicates whether the current record position is before the first record in the set of records, wherein
its value will be TRUE. Both properties return a Boolean value and are used to determine if the current record is outside the limits of
the Recordset object. There will be no current record if either the EOF Property or BOF Property is True, and if both properties are
True on opening a recordset it will indicate that there are no records. Opening a Recordset having atleast one record makes the first
record as the current record and in this case both the EOF Property and BOF Property will be False. Syntax: RecordsetObject
.EOF, RecordsetObject .BOF.

Count the number of Records in a Recordset

Use the RecordCount Property (ADO) to Count the number of Records in a Recordset. The RecordCount
Property will return -1 if the number of records cannot be determined or if RecordCount is not supported by
the provider or cursor type. CursorType adOpenForwardOnly does not support RecordCount, you can use
adOpenStatic or adOpenKeyset. CursorType adOpenDynamic might return -1 or return the actual count,
depending on the data source. The Execute method of a connection object opens a recordset with
CursorType adOpenForwardOnly which does not support RecordCount, hence use the recordset Open
method, which can use all CursorTypes.

Example of using the RecordCount property:
MsgBox RecordsetObject.RecordCount

E) Access fields in a Recordset

Count the number of Fields

The Count property (adoRecSet.Fields.count) determines the number of fields in a collection (Recordset),
wherein numbering for members of a collection begins with zero. If you have seven fields in a table, using
RecordsetObject.Fields.count will return 7, and RecordsetObject.Fields(0) will return the value of the first
field.

Access Fields by Item property viz. recordset.Fields.Item(index)

You can access fields by the Item property which refers to the field name or its position, viz.
recordset.Fields.Item(index), where index is the Field Name or its ordinal position. Note that the Item
property is the default property of the fields collection, so omitting the Item keyword will have no effect.

Recordset.Fields(0).Name returns the Field name of the first field, and Recordset.Fields(0).Value returns the
content of the first field. The Value property of the Field object is the Default property viz Recordset.Fields(0)
is the same as Recordset.Fields(0).Value and will return the first fields's value.

Examples: To reference a field named "FirstName", which is the second field in the table, you can
use any of the following:-
RecordsetObject.Fields("FirstName")
RecordsetObject.Fields.Item("FirstName")
RecordsetObject.Fields(1)
RecordsetObject.Fields.Item(1)
RecordsetObject![firstName]

Type property sets or returns the Data Type of a Field

Use the Type property to set or return the data type of a Field.

Examples of data type enumerated constants that are supported by ADO, 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.

Return the data type of a Field using the follwing code:
MsgBox RecordsetObject.Fields("FirstName").Type

Example 1: Use ADO to Connect to Access Database, Open Recordset, and Manipulate
Records.
Refer to Images 2a, 2b & 2c as mentioned in the code.

Image 2a

Image 2b

Image 2c

Sub adoAutomateAccess_1()
'In this example we show how to:
'1. Connect to the data source: ADO uses the Connection object to open an Access Database.
'2. Open a Recordset (set of records in a table) using the ADO Recordset Open Method.
'3. Reference all records in each field of a Recordset.
'4. Moving between Records in a Recordset, using MoveNext ...
'5. Create a new record in a table, using the AddNew method.
'6. Edit an ADO record.
'7. Access / Reference fields in a Recordset.
'8. Delete the new ADO record.
'9. Close Recordset and destroy object variable.

'To use ADO in your VBA project, you must add a reference to the ADO Object Library in Excel (your host
application) by clicking Tools-References in VBE, and then choose an appropriate version of Microsoft
ActiveX Data Objects x.x Library from the list.

'--------------
'DIM STATEMENTS

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

Dim adoRecSet As ADODB.Recordset
Dim i As Long

'--------------
'THE CONNECTION OBJECT - ESTABLISH CONNECTION TO A DATA SOURCE

'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

'instantiate an ADO object:

Dim connDB As New ADODB.Connection

'connect to database with the Mode value adModeShareExclusive ie. prevent others from opening a
connection:
'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.
connDB.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0; data
source=C:\Users\Documents\SalesReport.accdb; Mode =" & adModeShareExclusive

'--------------
'OPEN RECORDSET, ACCESS RECORDS AND FIELDS

'Note that a Recordset object represents a set of records, but at any time only a single record within the set
of records is referred by a Recordset object as the current record.

'Use the Open Method (ADO Recordset) to access data from a table - it opens a cursor (ie. a pointer) to a
record, or a range of records, within the Recordset. On opening a Recordset, the first record is the current
record.

'Set the ADO Recordset object:

Set adoRecSet = New ADODB.Recordset

'Opening the table named SalesManager:

strTable = "SalesManager"

adoRecSet.Open Source:=strTable, ActiveConnection:=connDB, CursorType:=adOpenStatic,

LockType:=adLockOptimistic

'refer Image 2a to view the existing SalesManager Table in MS Access file "SalesReport.accdb".
'reference all records in each field of a Recordset:
For i = 0 To adoRecSet.Fields.count - 1
'get field / column name:
MsgBox adoRecSet.Fields(i).Name
adoRecSet.MoveFirst
Do While Not adoRecSet.EOF
'get record value:
MsgBox adoRecSet.Fields(i).Value
adoRecSet.MoveNext
Loop
Next i

'refer Image 2b to view the SalesManager Table after adding the new record.
'Create a new record in a table, using the AddNew method (of the Recordset object).
'NOTE: values of a primary field will need to be unique and cannot be Null.
With adoRecSet
.AddNew
.Fields("EmployeeId") = 40
.Fields("FirstName") = "Lisa"
.Fields("SurName") = "Randall"
.Fields("JoinDate") = "08/11/2007"
.Fields("Telephone") = "224-1357967"
.Fields("City") = "Dallas"
.Fields("ZipCode") = "54650"
'will save only after Update method is run:
'In DAO, after the Update the current record will be the record which had focus before the AddNew method,
whereas in ADO, after calling Update the current record will be the new one.
.Update
End With

'To return a field named "FirstName", which is the second field in the table, you can use any of the
following:-
MsgBox adoRecSet.Fields("FirstName")
'MsgBox adoRecSet.Fields.Item("FirstName")
'MsgBox adoRecSet.Fields(1)
'MsgBox adoRecSet.Fields.Item(1)
'MsgBox adoRecSet![firstName]

'count number of fields - returns 7 fields:

MsgBox adoRecSet.Fields.count
'count number of records in the Recordset - returns 6, a new record is added to the original 5 records:
MsgBox adoRecSet.RecordCount

'Use the Type property to return the data type of a Field.

MsgBox adoRecSet.Fields("FirstName").Type

'refer Image 2c to view the SalesManager Table after editing the new record.
'edit the new record - an ADO recordset is already in edit mode (the Edit method is used only for DAO
recordset):
With adoRecSet
.Fields("FirstName") = "Julia"
.Fields("SurName") = "Green"
'will save only after Update method is run:
.Update
End With

'Delete the new record:

adoRecSet.Delete

'--------------
'CLOSE RECORDSET, DESTROY OBJECT VARIABLES

'close the objects
adoRecSet.Close
connDB.Close

'destroy the object variables

Set adoRecSet = Nothing
Set connDB = Nothing

End Sub
1.2 Microsoft Access: Use ADO to Execute SQL statements.

A) Use ADO to Execute SQL statements to Manage your Database

You can use ADO to execute SQL commands to manipulate data and manage your database. Using SQL
requires that the data should be entered as per the table structure, the columns sequence and their data
type. SQL, stands for Structured Query Language, is a computer language, and SQL statements are used to
store, retrieve and manipulate data in a relational database ex. Access Database.

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. Whereas flat file databases contain a single table of data with fields
representing all parameters viz. a single table will contain details of vendors and purchase orders made, and
with each purchase order the vendor details also get repeated resulting in duplication. Flat file databases
though easier to maintain, are far less robust and flexible.

A Database Management System (DBMS) is a program which manages data in databases, the manner in
which data is created, stored, modified and accessed. A Relational Database Management System
(RDBMS) is the DBMS in respect of a relational database. Microsoft Access is a popular RDBMS which
supports creating relational databases.
Other examples of DBMS are MySQL, Microsoft SQL Server, Oracle, etc.

In SQL, you can use DDL or DML statements. DDL (stands for Data Definition Language) statements are
used to make structural changes, to define the database structure and objects like tables and columns.
Examples of some often used DDL commands are CREATE TABLE, DROP TABLE and ALTER TABLE.
DML statements are used to managing and manipulating data within the database structure and deal with
inserting, updating, deleteing and retrieving database information like records in tables. Examples of often
used DML commands are include INSERT, SELECT, UPDATE and DELETE.

B) SQL Commands explained

The SQL SELECT Statement (the most commonly used SQL command) selects data from a database,
specifying column(s) headings, table(s) from which columns are to be selected, and the criteria for selection.
SELECT * is used to select all columns from a table. The FROM clause in a SELECT statement specifies
the Table from which columns are to be selected. The WHERE clause in a SELECT statement specifies the
criteria for selection. Syntax: SELECT column_name FROM table_name [WHERE clause]. You can also
add other optional clauses, viz. GROUP BY, HAVING and ORDER BY.

Use the INSERT command to insert a new row/record at the end of a table.

DELETE removes a specified row(s) from a table (in the DELETE statement, FROM clause is required and
WHERE clause is optional).

TRUNCATE TABLE deletes all the rows from the table (does not have the WHERE clause), and the table
structure remains.

UPDATE modifies values of records in a table.

CREATE TABLE creates a table with the specified fields.

DROP TABLE deletes all rows and the table structure is removed from the database.

ALTER TABLE is used to add, remove or modify columns in a table.

C) Using the ADO connection Execute method to execute the specified query and SQL statements

Execute Method (ADO Connection): The Execute method on the specified ADO connection object,
executes the SQL statement passed in the CommandText argument. Note that Recordset object returned by
the method is a read-only, forward-only cursor. Syntax: Set recordset = connection.Execute
(CommandText, RecordsAffected, Options).

CommandText is a string value, and is an SQL statement, stored procedure, or provider-specific
text. RecordsAffected is an optional Long variable which indicates the number of records affected by the
method. Options is an optional Long value which determines how the CommandText argument is
evaluated: adCmdUnspecified - indicates that the CommandText property is unspecified; adCmdText -
evaluates CommandText as a textual definition of a command or stored procedure call; adCmdTable -
evaluates CommandText as a table name whose columns are all returned by an SQL query;
adCmdStoredProc - evaluates CommandText as a stored procedure name; adCmdUnknown (default) - type
of command in the CommandText argument is not known; Values of adCmdFile or adCmdTableDirect are
not to be used with the Execute Method.

Example 2: Use ADO with SQL statements.
Refer to Images 3a & 3b, as mentioned in the code.

Image 3a

Image 3b

Sub automateAccessADO_2()
'In this example we show how to use ADO with SQL statements:
'1. Add and delete records using the ADO connection Execute method to execute SQL statements;
'2. Select Records/Fields conditionally, using the ADO Recordset Open Method with SQL statements.

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

Dim strSQL As String

'instantiate an ADO object using Dim with the New keyword:

Dim adoRecSet As New ADODB.Recordset
Dim connDB As New ADODB.Connection

'--------------
'THE CONNECTION OBJECT

'--------------------
'CREATE AND DELETE RECORDS

'Besides using ADO by itself for entering data (viz. using the AddNew method of the Recordset object) as
shown earlier, you can also use ADO with SQL statements.

'Refer Image 3a for SalesManager Table in MS Access file SalesReport.accdb; Image 3b shows the
SalesManager Table after adding new record (before it is deleted).

'add new records in the SalesManager Table using SQL INSERT INTO statements, by specifying both the
column names and the values to be inserted. The Execute method on the specified ADO connection object,
executes the SQL statement passed in the CommandText argument:
connDB.Execute "INSERT INTO SalesManager (EmployeeId, FirstName, Surname, JoinDate) VALUES (2,
'Mary', 'Smith', '6/20/2009')"
'alternatively add a new record by specifying only the column values to be inserted:
'connDB.Execute "INSERT INTO SalesManager VALUES (2, 'Mary', 'Smith', '6/20/2009')"

'delete records in the SalesManager Table:

strSQL = "DELETE FROM SalesManager WHERE FirstName = 'Mary'"
connDB.Execute CommandText:=strSQL

'Value of 0 will be returned which indicates that the recordset is closed, because it was never opened (value
of 1 indicates that the recordset is open):
MsgBox adoRecSet.State

'--------------------
'SELECT RECORDS / FIELDS CONDITIONALLY

'Refer Image 3a for SalesManager Table in MS Access file SalesReport.accdb, for below codes.
'select a specific field (FirstName), basis numeric reference:
strSQL = "SELECT FirstName FROM SalesManager WHERE EmployeeId = 18"
adoRecSet.Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic,
LockType:=adLockOptimistic
'returns "Tracy":
MsgBox adoRecSet.Fields("FirstName")
adoRecSet.Close

'select a specific field (FirstName), basis text reference:

strSQL = "SELECT FirstName FROM SalesManager WHERE SurName = 'Green'"
adoRecSet.Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic,
LockType:=adLockOptimistic
'returns "Sam":
MsgBox adoRecSet.Fields("FirstName")
adoRecSet.Close

'select multiple fields, basis numeric reference:

strSQL = "SELECT FirstName, SurName, JoinDate FROM SalesManager WHERE EmployeeId = 18"
adoRecSet.Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic,
LockType:=adLockOptimistic
'returns "Tracy Murray 7/16/2011":
MsgBox adoRecSet.Fields("FirstName") & " " & adoRecSet.Fields("SurName") & " " &
adoRecSet.Fields("JoinDate")
adoRecSet.Close

'select a specific field, basis multiple criteria (delimit date values by the datetime delimiter "#"):
'to select a range between two dates, the BETWEEN operator has been used in the WHERE clause.
strSQL = "SELECT EmployeeId FROM SalesManager WHERE SurName = 'Green' and JoinDate between
#08/3/2012# and #10/3/2013#"
adoRecSet.Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic,
LockType:=adLockOptimistic
'returns "56":
MsgBox adoRecSet.Fields("EmployeeId")
adoRecSet.Close

'select all fields, basis text reference:

strSQL = "SELECT * FROM SalesManager WHERE SurName = 'Kelly'"
adoRecSet.Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic,
LockType:=adLockOptimistic
'returns "35 David Kelly 1/24/2010":
MsgBox adoRecSet.Fields("EmployeeId") & " " & adoRecSet.Fields("FirstName") & " " &
adoRecSet.Fields("SurName") & " " & adoRecSet.Fields("JoinDate")
adoRecSet.Close

'select all fields, basis criteria specified using the Like operator:
'The Like operator compares two strings, if the value in the string matches the pattern, it returns True. The
percent sign (%) indicates zero or more characters after J ie. the criteria is if the first name starts with J:
'Note that in the WHERE clause of SQL statement, percent (%) is used as a wild character instead of an
asterisk (*), indicating zero or more characters after J. In the WHERE clause of SQL statement, use
underscore (_) to indicate a single character.
strSQL = "SELECT * FROM SalesManager WHERE FirstName Like 'J%'"
adoRecSet.Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic,
LockType:=adLockOptimistic
'return all instances where criteria (WHERE FirstName Like 'J%') is met - "Mason" & "Davis":
Do While Not adoRecSet.EOF
MsgBox adoRecSet.Fields("Surname")
adoRecSet.MoveNext
Loop
adoRecSet.Close

'------------------
'close the objects
connDB.Close

'destroy the variables

Set adoRecSet = Nothing
Set connDB = Nothing

End Sub

Example 3: Edit Records, using ADO with SQL statements.
Refer to Images 4a & 4b, as mentioned in the code.

Image 4a

Image 4b

Sub automateAccessADO_3()
'In this example we show how to Edit Records, using ADO with SQL statements:
'Refer Image 4a for SalesManager Table in MS Access file SalesReport.accdb before edit, and Refer Image
4b after edit.

'--------------
'DIM STATEMENTS

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

Dim strSQL As String

'instantiate an ADO object using Dim with the New keyword:

Dim adoRecSet As New ADODB.Recordset
Dim connDB As New ADODB.Connection

'--------------
'THE CONNECTION OBJECT

'--------------
'SEARCH RECORDS BASIS SPECIFIED CRITERIA AND EDIT THEM

'select all fields, basis JoinDate criteria:

strSQL = "SELECT * FROM SalesManager WHERE JoinDate >= #01/01/2010#"
adoRecSet.Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic,
LockType:=adLockOptimistic
'return all instances where criteria (WHERE JoinDate >= #01/01/2010#) is met, and marks their FirstName &
Surname with "M":
Do While Not adoRecSet.EOF
adoRecSet.Fields("FirstName") = adoRecSet.Fields("FirstName") & " - M"
adoRecSet.Fields("Surname") = adoRecSet.Fields("Surname") & " - M"
adoRecSet.MoveNext
Loop

'------------------
'close the objects
adoRecSet.Close
connDB.Close

'destroy the variables

Set adoRecSet = Nothing
Set connDB = Nothing

End Sub

D) Use the OpenSchema Method to access information about database tables and columns

The OpenSchema Method returns a Recordset object containing schema (structure) information about the
database, like the tables and columns in the database and the supported data types. Syntax: Set recordset
= connection.OpenSchema(QueryType, Criteria, SchemaID).

The first argument in the OpenSchema Method which is a must, is the type of schema required which is a
SchemaEnum value (ie. an enumerated value) which specifies the type of Recordset returned by the
method. Examples include adSchemaTables which returns the tables defined in the catalog, and
adSchemaColumns which returns the columns of tables defined in the catalog.

To limit the results of the schema query, use the Criteria argument (this is the second parameter in the
OpenSchema Method, and is optional) which specifies an array of values (ie. query constraints).
Query constraints (ie. criteria) in the SchemaEnum of adSchemaTables are: TABLE_CATALOG,
TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE.
Query constraints (ie. criteria) in the SchemaEnum of adSchemaColumns are: TABLE_CATALOG,
TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME.

The third argument of SchemaID is used only if the first argument is set to adSchemaProviderSpecific.

Example 4: Use the OpenSchema Method to access information about the database
tables and columns.
Refer to Image 5, as mentioned in the code.

Image 5

Sub automateAccessADO_4()
'Use the OpenSchema Method to access information about the database tables and columns.

'--------------
'DIM STATEMENTS

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

'instantiate an ADO object using Dim with the New keyword:

Dim adoRecSet As New ADODB.Recordset
Dim connDB As New ADODB.Connection

'--------------
'THE CONNECTION OBJECT

'--------------
'ACCESS INFORMATION ABOUT THE DATABASE TABLES AND COLUMNS

'Refer Image 5 for SalesManager Table in MS Access file SalesReport.accdb which also contains another
Table named Performance.

'code to get names of all tables in a database - returns table names 'Performance' & 'SalesManager':
'query constraint of TABLE_TYPE is specified as "TABLE"
Set adoRecSet = connDB.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE"))
Do While Not adoRecSet.EOF
MsgBox adoRecSet.Fields("TABLE_NAME").Value
adoRecSet.MoveNext
Loop
adoRecSet.Close

'alternate code to get names of all tables in a database - returns table names 'Performance' &
'SalesManager':
Set adoRecSet = connDB.OpenSchema(adSchemaTables)
Do While Not adoRecSet.EOF
If adoRecSet.Fields("TABLE_TYPE") = "TABLE" Then
MsgBox adoRecSet.Fields("TABLE_NAME").Value
End If
adoRecSet.MoveNext
Loop
adoRecSet.Close

'to get names of all fields of a specified table ("SalesManager"):

'Field Names returned are: EmployeeId, FirstName, Surname & JoinDate.
Set adoRecSet = connDB.OpenSchema(adSchemaColumns, Array(Empty, Empty, "SalesManager",
Empty))
Do While Not adoRecSet.EOF
MsgBox adoRecSet.Fields("COLUMN_NAME").Value
adoRecSet.MoveNext
Loop
adoRecSet.Close

'--------------
'close the objects
connDB.Close

'destroy the variables

Set adoRecSet = Nothing
Set connDB = Nothing

End Sub

E) Create a database table using ADO, with SQL statements

Note that ADO does not by itself support creating database tables, which is actually done with ADOX.
However, you can create a database table in ADO using SQL.

SQL CREATE TABLE statement creates a table with the specified fields. SQL INSERT INTO statement
inserts a new row/record at the end of the table.

While using the SQL CREATE TABLE statement, you specify the Table Name, Field Names and their Data
Types. Commonly used data types for fields include: TEXT (SIZE) for Text Field; CHAR (SIZE) for Text
Field; SHORT for Numbers-Integer; LONG for Numbers-Long; SINGLE for Numbers-Single; DOUBLE for
Numbers-Double; CURRENCY for Currency; DATE for Date/Time; DATETIME for Date/Time; YESNO for
Boolean values; and so on.

Use the CONSTRAINT clause in SQL statements, to create a constraint on one or more fields:
Specifying the PRIMARY KEY (can be only one in a Table) reserved word designates a field(s) as a primary
field whose values will be unique and cannot be Null.
Specifying NOT NULL for a field will necessitate the new record to have valid data in that field. A table
column, by default, can have a NULL value ie. a field can contain no value.
Specifying the UNIQUE reserved word will not allow a same value in that field, for two records in the table.

Example 5: Create a New Database Table using ADO, with SQL statements.
Refer to Image 6, as mentioned in the code.

Image 6

Sub automateAccessADO_5()
'Create a new database table using ADO with SQL statements.

'--------------
'DIM STATEMENTS

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

'instantiate an ADO object using Dim with the New keyword:

Dim adoRecSet As New ADODB.Recordset
Dim connDB As New ADODB.Connection

'--------------
'THE CONNECTION OBJECT

'--------------
'CHECK IF THE NEW TABLE NAME IS PRE-EXISTING IN DATABASE, AND DELETE IF FOUND

'check if the table named SalesManager exists in database, and delete if found:
Set adoRecSet = connDB.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE"))
Do While Not adoRecSet.EOF
If adoRecSet.Fields("TABLE_NAME").Value = "SalesManager" Then
connDB.Execute "DROP TABLE SalesManager"
End If
adoRecSet.MoveNext
Loop
adoRecSet.Close

'--------------
'CREATE A NEW TABLE IN DATABASE
'Refer Image 6

'Create a table named SalesManager, with field names and their data types (viz. the field named FirstName
is of Text data type and can hold 40 characters).

'add a PRIMARY KEY CONSTRAINT named 'pk_EI' for the column 'EmployeeID' of the 'SalesManager'
table; also add a UNIQUE CONSTRAINT named 'un_FN' for the column 'FirstName' of the 'SalesManager'
table.

connDB.Execute "CREATE TABLE SalesManager(EmployeeId LONG, FirstName Text(40), Surname

Char(50) NOT NULL, JoinDate Date, Sales Double, CONSTRAINT pk_EI PRIMARY KEY (EmployeeID),
CONSTRAINT un_FN UNIQUE (FirstName))"

'Populate the table using SQL INSERT INTO statements, without specifying the column names but only their
values:
connDB.Execute "INSERT INTO SalesManager VALUES (256, 'Mary', 'Lange', '7/24/2008', 15678.58)"
connDB.Execute "INSERT INTO SalesManager VALUES (587, 'Harry', 'Davis', '7/16/2011', 14673.26)"
connDB.Execute "INSERT INTO SalesManager VALUES (01, 'James', 'Bond', '3/11/2009', 12589)"

'Populate the table using SQL INSERT INTO statements, by specifying both the column names and the
values to be inserted:
connDB.Execute "INSERT INTO SalesManager (EmployeeId, FirstName, Surname, JoinDate, Sales)
VALUES (445, 'John', 'Morgan', '1/24/2010', 12432.20)"
connDB.Execute "INSERT INTO SalesManager (EmployeeId, FirstName, Surname, JoinDate, Sales)
VALUES (25, 'Dane', 'Large', '10/3/2012', 9876.5)"

'--------------
'close the objects
connDB.Close

'destroy the variables

Set adoRecSet = Nothing
Set connDB = Nothing

End Sub

F) ADO Find Method - Find or Locate a specific Record

Find Method (ADO) searches for a row, in a set of records, basis specified criteria. Syntax: Find (Criteria,
SkipRows, SearchDirection, Start).

Criteria specifies the criteria for finding the record. This argument is rquired whle all others are optional.
Note that only one criteria is supported by the ADO Find method.

SkipRows specifies the number of records to be skipped, where the deafult value is 0 wherein the current
record is not skipped.

Use the default SearchDirection value of adSearchForward to search forward from the current record, and
if a match is not found the position of the record pointer is at EOF ie. after the last record in the set of
records. To search backward, use the value of adSearchBackward and if a match is not found the position of
the record pointer is at BOF ie. before the first record in the set of records.

Start argument sets a current row position before starting a search. Default value of adBookmarkCurrent
indicates the current record, value of adBookmarkFirst indicates the first record and value of
adBookmarkLast indicates the last record.

Note: On a match being found, the found record becomes the the current row position, and because the
search starts from the current row, you must either skip a record OR move to the next record to do a new
find for the next match.

Example 6: Using the Find method to find or locate a specific record(s).
Refer to Image 7, as mentioned in the code.

Image 7

Sub automateAccessADO_6()
'Using the Find method to find or locate a specific record(s).

'--------------
'DIM STATEMENTS
Dim strMyPath As String, strDBName As String, strDB As String

'instantiate an ADO object using Dim with the New keyword:

Dim adoRecSet As New ADODB.Recordset
Dim connDB As New ADODB.Connection

'--------------
'THE CONNECTION OBJECT

'--------------
'USE FIND METHOD
'Refer Image 7 to view the SalesManager Table in MS Access file "SalesReport.accdb"

'open Recordset ie. SalesManager Table:

adoRecSet.Open Source:="SalesManager", ActiveConnection:=connDB, CursorType:=adOpenKeyset,
LockType:=adLockOptimistic

'note that only one criteria is supported by the ADO Find method:
adoRecSet.Find "EmployeeID > 8", , adSearchForward

Do While Not adoRecSet.EOF

'using an IF statement, for search with an additional criteria of FirstName starting with J within the Search
criteria of EmployeeID > 8:
'Note that the J* indicates all words starting with the letter J followed by zero or more characters in the IF
statement, but in the WHERE clause of an SQL statement it will be used as J% (percent is used as a wild
character instead of an asterisk, indicating zero or more characters after J) viz. "FirstName LIKE 'J%'".
If adoRecSet.Fields("FirstName") Like "J*" Then
'2 records are found using the Find Method: John Mason, Employee Id:12; Jim Davis, Employee Id:21.
MsgBox adoRecSet.Fields("FirstName") & " " & adoRecSet.Fields("Surname") & ", Employee Id:" &
adoRecSet.Fields("EmployeeId")
i=i+1
End If
'on a match being found, the found record becomes the the current row position, and because the search
starts from the current row, you must either skip a record OR move to the next record to do a new find for
the next match:
adoRecSet.MoveNext
adoRecSet.Find "EmployeeID > 8", , adSearchForward
'OR skip 1 row:
'adoRecSet.Find "EmployeeID > 8", 1, adSearchForward
Loop

'2 records are found

MsgBox "Records found: " & i
'--------------
'close the objects
connDB.Close

'destroy the variables

Set adoRecSet = Nothing
Set connDB = Nothing

End Sub

G) ADO Filter Property to FilterRecords

Filter database table records, using the Filter Property (ADO) on a recordset. Use a filter when you want to
screen out selective records in a table. Syntax: recordset.Filter = Criteria.

The Criteria string contains clauses in the format of "FirstName = 'Jim'" where FirstName is the Column or
Field Name, = is the Operator, and Jim is the Field Value. The Criteria string can have multiple conditions or
clauses.

The following operators can be used: <, >, <=, >=, <>, =, or LIKE.

For field values: use single quotes for strings; "#" for date values; you can use wild cards asterisk (*) and
percent (%), provided they are the last character in the string, for the LIKE operator.

Example 7: Filter Records using the ADO Filter Property.
Refer to Image 7, as mentioned in the code.

Sub automateAccessADO_7()
'Filter database table records, using the Filter Property (ADO) on a recordset. Use a filter when you want to
screen out selective records in a table.

'--------------
'DIM STATEMENTS

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

'instantiate an ADO object using Dim with the New keyword:

Dim adoRecSet As New ADODB.Recordset
Dim connDB As New ADODB.Connection

'--------------
'THE CONNECTION OBJECT

'--------------
'FILTER RECORDS
'Refer Image 7 to view the SalesManager Table in MS Access file "SalesReport.accdb", used for below filter
criteria.

'Place the filter BEFORE opening the recordset.

'You can use wild cards asterisk (*) and percent (%), provided they are the last character in the string, for the
LIKE operator.
'Examples of 5 valid filter criteria have been given below.

'adoRecSet.Filter = "FirstName = 'Jim' OR EmployeeID > 35"

'adoRecSet.Filter = "FirstName LIKE 'J*'"
'adoRecSet.Filter = "Surname LIKE '%a%'"
'adoRecSet.Filter = "(FirstName = 'Jim' AND EmployeeID > 18) OR (Surname = 'Green' AND EmployeeID >
35)"
adoRecSet.Filter = "(FirstName = 'Jim') OR (Surname = 'Green' AND EmployeeID > 35)"

'Incorrect use of OR: you are not allowed to group clauses by the OR operator and then group these to
another clause with the AND operator, in the following manner:
'adoRecSet.Filter = "(FirstName = 'Sam') AND (FirstName = 'Jim' OR EmployeeID > 35)"

adoRecSet.Open Source:="SalesManager", ActiveConnection:=connDB, CursorType:=adOpenKeyset,

LockType:=adLockOptimistic

Do While Not adoRecSet.EOF
MsgBox adoRecSet.Fields("FirstName") & " " & adoRecSet.Fields("Surname") & ", Employee Id:" &
adoRecSet.Fields("EmployeeId")
i=i+1
'on a match being found, the found record becomes the the current row position, and because the search
starts from the current row, you must move to the next record to find the next match:
adoRecSet.MoveNext
Loop

MsgBox "Records found: " & i

'--------------
'close the objects
connDB.Close

'destroy the variables

Set adoRecSet = Nothing
Set connDB = Nothing

End Sub

Example 8: Using ADO with SQL Statements to: Add, Delete and Modify Columns in an
Existing Table; Add and Delete Constraints on Columns.
Refer to Images 8a & 8b, as mentioned in the code.

Image 8a

Image 8b

Sub automateAccessADO_8()
'Using ADO with SQL Statements to: Add, Delete and Modify Columns in an Existing Table; Add and Delete
Constraints on Columns.

'--------------
'DIM STATEMENTS

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

Dim strSQL As String

'instantiate an ADO object using Dim with the New keyword:

Dim adoRecSet As New ADODB.Recordset
Dim connDB As New ADODB.Connection

'--------------
'THE CONNECTION OBJECT

strDBName = "SalesReport.accdb"
strMyPath = ThisWorkbook.Path
strDB = strMyPath & "\" & strDBName

'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.
connDB.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0; data source=" & strDB
'--------------
'ADD, DELETE OR MODIFY COLUMNS IN AN EXISTING TABLE USING THE SQL 'ALTER TABLE'
STATEMENT.
'Refer Image 8a to view the SalesManager Table in MS Access file "SalesReport.accdb" before running
below code

'Run the ADO connection Execute method, to ALTER TABLE, without opening the recordset. SQL command
ADD COLUMN adds a new column at the end of the existing columns - in below code we are adding 3
columns named Telephone, City & ZipCode.
strSQL = "ALTER TABLE SalesManager ADD COLUMN Telephone char(15), COLUMN City Text(30),
COLUMN ZipCode char(6)"
connDB.Execute CommandText:=strSQL

'modify/enter column values after opening recordset - refer Image 8b which shows table after entering
values as below:
strSQL = "SELECT * FROM SalesManager WHERE EmployeeId = 12"
adoRecSet.Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic,
LockType:=adLockOptimistic

With adoRecSet
.Fields("Telephone") = "421-1234567"
.Fields("City") = "New York"
.Fields("ZipCode") = "10453"
.Update
End With

adoRecSet.Close

'Run the ADO connection Execute method, to ALTER TABLE, without opening the recordset.

'delete the Telephone column in the SalesManager Table:

strSQL = "ALTER TABLE SalesManager DROP COLUMN Telephone"
connDB.Execute CommandText:=strSQL

'change the data type of the ZipCode column in the SalesManager Table:
strSQL = "ALTER TABLE SalesManager ALTER COLUMN ZipCode Long"
connDB.Execute CommandText:=strSQL

'delete the City & ZipCode columns in the SalesManager Table:

strSQL = "ALTER TABLE SalesManager DROP COLUMN City, ZipCode"
connDB.Execute CommandText:=strSQL

'-------------
'ADD AND DELETE CONSTRAINTS ON COLUMNS

'add a PRIMARY KEY CONSTRAINT named 'pk_EI' for the column 'EmployeeID' of the 'SalesManager'
table:
strSQL = "ALTER TABLE SalesManager ADD CONSTRAINT pk_EI PRIMARY KEY (EmployeeID)"
connDB.Execute CommandText:=strSQL

'drop the existing PRIMARY KEY CONSTRAINT named 'pk_EI' from the 'SalesManager' table:
strSQL = "ALTER TABLE SalesManager DROP CONSTRAINT pk_EI"
connDB.Execute CommandText:=strSQL

'add a UNIQUE CONSTRAINT named 'un_FN' for the column 'FirstName' of the 'SalesManager' table:
strSQL = "ALTER TABLE SalesManager ADD CONSTRAINT un_FN UNIQUE (FirstName)"
connDB.Execute CommandText:=strSQL

'drop the existing CONSTRAINT named 'un_FN' from the 'SalesManager' table:
strSQL = "ALTER TABLE SalesManager DROP CONSTRAINT un_FN"
connDB.Execute CommandText:=strSQL

'--------------
'close the objects
connDB.Close

'destroy the variables

Set adoRecSet = Nothing
Set connDB = Nothing

End Sub
1.3 Import or Export Data from Access to Excel using ADO.

A) Use ADO to Import Data from Microsoft Access Database to Excel

In this section we show, with the help of practical examples, how to connect to Access Database from Excel
(your host application) using ADO to: (i) import or retrieve data from Access database to Excel worksheet;
and (ii) Export data from Excel worksheet to Access Database Table.

Range.CopyFromRecordset Method: This method is commonly used to copy records from an Aceess
Table to an Excel worksheet. Syntax: Range.CopyFromRecordset(Data, MaxRows, MaxColumns).

Range is the worksheet range to which the records are copied, starting at its upper-left corner. Data is the
Recordset (ie. set of records) in the Access database to be copied and the current row in the Recordset is
the starting record from where copying begins. MaxRows and MaxColumns refer to the maximum numbers
of rows (ie. records) and fields respectively to be copied and omitting these arguments will indicate that all
rows and fields are copied. Data is mandatory to specify while other arguments of MaxRows and
MaxColumns are optional.

Example 9:
B) Using ADO to Import data from Access Table to Excel worksheet (your host application).
Refer to Images 9a, 9b, 9c, 9d and 9e as mentioned in the code.
The code is simple to understand, though apparently long due to multiple options shown (reference to
images 9a to 9e) as to how data can be imported into Excel. Each option can be treated as a separate code
and run accordingly.

Image 9a

Image 9b

Image 9c

Image 9d

Image 9e

Sub automateAccessADO_9()
'Using ADO to Import data from an Access Database Table to an Excel worksheet (your host application).
'refer Image 9a to view the existing SalesManager Table in MS Access file "SalesReport.accdb".

'--------------
'DIM STATEMENTS

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

Dim i As Long, n As Long, lFieldCount As Long
Dim rng As Range

'instantiate an ADO object using Dim with the New keyword:

Dim adoRecSet As New ADODB.Recordset
Dim connDB As New ADODB.Connection

'--------------
'THE CONNECTION OBJECT

'--------------
'OPEN RECORDSET, ACCESS RECORDS AND FIELDS

Dim ws As Worksheet
'set the worksheet:
Set ws = ActiveWorkbook.Sheets("Sheet8")

'Set the ADO Recordset object:

Set adoRecSet = New ADODB.Recordset

'Opening the table named SalesManager:

strTable = "SalesManager"

'--------------
'COPY RECORDS FROM ALL FIELDS USING CopyFromRecordset:
'refer Image 9b to view records copied to Excel worksheet

'open recordset/table:
adoRecSet.Open Source:=strTable, ActiveConnection:=connDB, CursorType:=adOpenStatic,
LockType:=adLockOptimistic

Set rng = ws.Range("A1")

lFieldCount = adoRecSet.Fields.count

For i = 0 To lFieldCount - 1
'copy column names in first row of the worksheet:
rng.Offset(0, i).Value = adoRecSet.Fields(i).Name
Next i

'copy record values starting from second row of the worksheet:

rng.Offset(1, 0).CopyFromRecordset adoRecSet
'to copy 4 rows and 3 columns of the recordset to excel worksheet:
'rng.Offset(1, 0).CopyFromRecordset Data:=adoRecSet, MaxRows:=4, MaxColumns:=3

'select a column range:

Range(ws.Columns(1), ws.Columns(lFieldCount)).AutoFit
'worksheet columns are deleted because this code is only for demo:
Range(ws.Columns(1), ws.Columns(lFieldCount)).Delete

adoRecSet.Close

'--------------
'COPY RECORDS FROM SELECTED FIELDS USING CopyFromRecordset:
'refer Image 9c to view records copied to Excel worksheet

'copy all records from the selected fields (EmployeeId, FirstName & JoinDate):
strSQL = "SELECT EmployeeId, FirstName, JoinDate FROM SalesManager WHERE EmployeeId > 15"
adoRecSet.Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic,
LockType:=adLockOptimistic
Set rng = ws.Range("A1")
lFieldCount = adoRecSet.Fields.count

For i = 0 To lFieldCount - 1
'copy column names in first row of the worksheet:
rng.Offset(0, i).Value = adoRecSet.Fields(i).Name
Next i

'copy record values starting from second row of the worksheet:
rng.Offset(1, 0).CopyFromRecordset adoRecSet

'select a column range:

Range(ws.Columns(1), ws.Columns(lFieldCount)).AutoFit
'worksheet columns are deleted because this code is only for demo:
Range(ws.Columns(1), ws.Columns(lFieldCount)).Delete

adoRecSet.Close

'--------------
'COPY RECORDS FROM ALL FIELDS OF A RECORDSET:
'refer Image 9d to view records copied to Excel worksheet

adoRecSet.Open Source:=strTable, ActiveConnection:=connDB, CursorType:=adOpenStatic,

LockType:=adLockOptimistic

Set rng = ws.Range("A1")

lFieldCount = adoRecSet.Fields.count

For i = 0 To lFieldCount - 1
'copy column names in first row of the worksheet:
rng.Offset(0, i).Value = adoRecSet.Fields(i).Name
adoRecSet.MoveFirst
'copy record values starting from second row of the worksheet:
n=1
Do While Not adoRecSet.EOF
rng.Offset(n, i).Value = adoRecSet.Fields(i).Value
adoRecSet.MoveNext
n=n+1
Loop
Next i

'select column range to AutoFit column width:

Range(ws.Columns(1), ws.Columns(lFieldCount)).AutoFit
'worksheet columns are deleted because this code is only for demo:
Range(ws.Columns(1), ws.Columns(lFieldCount)).Delete
adoRecSet.Close

'--------------
'COPY RECORDS FROM SELECTED FIELDS OF A RECORDSET:
'refer Image 9e to view records copied to Excel worksheet

'copy all records from the 3 fields of EmployeeId, SurName, JoinDate:

strSQL = "SELECT EmployeeId, SurName, JoinDate FROM SalesManager"
adoRecSet.Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic,
LockType:=adLockOptimistic

Set rng = ws.Range("A1")

'select a column range:

Range(ws.Columns(1), ws.Columns(lFieldCount)).AutoFit
'worksheet columns are deleted because this code is only for demo:
Range(ws.Columns(1), ws.Columns(lFieldCount)).Delete

adoRecSet.Close

'--------------
'close the objects
connDB.Close

'destroy the variables

Set adoRecSet = Nothing
Set connDB = Nothing

End Sub

Example 10:

C) Using ADO to Export data from worksheet (your host application) to Access Table.
Refer to Images 10a, 10b & 10c, as mentioned in the code.

Image 10a

Image 10b

Image 10c

Sub automateAccessADO_10()
'Using ADO to Export data from Excel worksheet (your host application) to an Access Database Table.
'refer Image 10a to view the existing SalesManager Table in MS Access file "SalesReport.accdb"
'refer Image 10b for data in Excel worksheet which is exported to Access Database Table.
'refer Image 10c to view the SalesManager Table in Access file "SalesReport.accdb", after data is exported.

'--------------
'DIM STATEMENTS

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

Dim i As Long, n As Long, lastRow As Long, lFieldCount As Long

'instantiate an ADO object using Dim with the New keyword:

Dim adoRecSet As New ADODB.Recordset
Dim connDB As New ADODB.Connection

'--------------
'THE CONNECTION OBJECT

Dim ws As Worksheet
'set the worksheet:
Set ws = ActiveWorkbook.Sheets("Sheet9")

'Set the ADO Recordset object:

Set adoRecSet = New ADODB.Recordset

'Opening the table named SalesManager:

strTable = "SalesManager"
adoRecSet.Open Source:=strTable, ActiveConnection:=connDB, CursorType:=adOpenStatic,
LockType:=adLockOptimistic

'--------------
'COPY RECORDS FROM THE EXCEL WORKSHEET:
'Note: Columns and their order should be the same in both Excel worksheet and in Access database table

lFieldCount = adoRecSet.Fields.count
'determine last data row in the worksheet:
lastRow = ws.Cells(Rows.count, "A").End(xlUp).Row

'start copying from second row of worksheet, first row contains field names:
For i = 2 To lastRow
adoRecSet.AddNew
For n = 0 To lFieldCount - 1
adoRecSet.Fields(n).Value = ws.Cells(i, n + 1)
Next n
adoRecSet.Update
Next i

'--------------
'close the objects
adoRecSet.Close
connDB.Close

'destroy the variables

Set adoRecSet = Nothing
Set connDB = Nothing

End Sub

1.4 Microsoft Access: ActiveX Data Objects Extensions (ADOX).

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.

A) 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

B) 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.

C) 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

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 Column
Object.

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.

Image 1

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
Next

'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

Image 2

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
Next

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

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

'------------
'destroy the Catalog object variable:
Set catAdox = Nothing

End Sub

D) 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

Image 3a

Image 3b

Image 3c

Image 3d

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

'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

'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

E) 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.

Image 4a

Image 4b

Image 4c

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

'set the ActiveConnection property to a connection string:

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

F) ADOX View Object, Parameter, 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.

Image 5a

Image 5b

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

'set the ActiveConnection property to a connection string:

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

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

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

Image 6a

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
Next

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

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

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

End Sub

Image 6b

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

'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

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

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

End Sub

Image 7

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

'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

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

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

End Sub

Image 8

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
2 Connecting to Microsoft Access Database from Excel VBA, using DAO Object Model

2.1 Data Access Objects Library (DAO), Connect with Access Databases from Excel.
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.

Contents:
Connect with Databases using DAO, RDO and ADO Objects
DAO Objects & Programming model
The DBEngine object & Workspace Object
DAO Databases
Tables of a DAO Database
Fields / Columns of a Table
Recordset & Records of a DAO Database Table
-----------------------------------------------------------------------------------

This section explains using the DAO Objects & Programming model to Connect with Access Databases from
Microsoft Excel, the DBEngine object, Workspace Object & Workspaces Collection, DAO Databases, Tables
of a DAO Database, Fields / Columns of a Table, Recordset & Records of a DAO Database Table, with
practical examples.

A) Connect with Databases using DAO, RDO and ADO Objects

To connect with other databases, when working in VBA, you can use either DAO (Data Access Objects),
RDO (Remote Data Objects) or ADO (ActiveX Data Objects). After connecting to a database, you can
manipulate its data.

DAO, RDO and ADO are data access interfaces ie. they are object and programming models used to
access data. Earlier, DAO was used to interface with local databases (viz. MS Access), RDO was used to
interface with large databases such as Oracle and SQL Server. ADO was their replacement to interface with
all types of data sources. Both DAO and ADO are commonly used while connecting to Microsoft Access
Database.

DAO is native to Access, the DAO object library is the default reference in Access 2007 and the library will
be existing when you use Access (ADO object library was the default reference in Access 2000 and 2002,
whereas DAO returned as the default object library with Access 2003 after being the default in Access 97
earlier). DAO integrates well with Access databases and provides faster access. ADO provides access to a
wider variety of data sources than DAO, besides Access. ADO has succeeded DAO and is the latest data
access technology, is simpler and more flexible, and interfaces with Microsoft's powerful data access
technology of OLE DB. In ADO the objects are less than in DAO, and it contains more properties, methods
and events. ADO/OLE DB is recommended for new projects but it might not be worthwhile to convert DAO
code to ADO for existing projects.

ADO creates a reference to the database using the Connection object, to connect to the data source. You
use the Open and Close methods to open and close a Connection object. DAO creates a reference to the
database using the database object, to connect to the data source.

In Microsoft Access, Recordset objects are used to access and manipulate data in a database. A Recordset
object represents a set of records in a database table, or a set of records returned from running a query.
Both DAO and ADO libraries have a Recordset object, though the methods, properties, and options of the
respective object is different. A Record object is one row of data in a Recordset. A Recordset object has a
Fields collection which contains all the Field objects, where each Field object represents a column in the
Recordset. In other words, each record represents a row of data and contains many fields, and each field
corresponds to a column in the database table.

In your VBA code, you should ideally precede the object name by its program ID (ProgID) prefix, which in
ADO is "ADODB" and in DAO is "DAO". Many objects, for example the Recordset object, have similar
names in both DAO and ADO and it is advisable to have explicit references in your project. This becomes a
must if you have included references to both the DAO and ADO libraries in your VBA project, else the object
library mentioned first in the References list will prevail, resulting in confusion in the vba code.

While instantiating the Recordset object, you should use:
Dim daoRecSet As DAO.Recordset
Dim adoRecSet As ADODB.Recordset
instead of:
Dim RecSet As Recordset

B) DAO Objects & Programming model

DAO (Data Access Objects) is an object-oriented data access interface, used to connect to and access
databases. It was the first Objects & Programming model which used the Microsoft Jet database engine,
and is optimized to work Microsoft Access files (.mdb). The objects and collections in the DAO object
hierarchy are used to connect to a database, access and manipulate its data and database structure.

A database engine is the underlying software component of a database used to manipulate its data. DAO
Object Model by default uses the Microsoft Jet database engine for data access. ODBCDirect (which came
after ODBC) allowed DAO to access ODBC data sources directly without using the Jet database engine. In
this section we will illustrate connecting to Microsoft Access Database using DAO with the Jet engine. Prior
to Access 2007, Access used the Microsoft (JET) engine, but with Access 2007 the new and improved ACE
engine has succeeded and replaced JET. The ACE engine is fully backward-compatible so that it can be
used with the .accdb files (Access 2007) and the earlier .mdb files.

Automating Access from Excel: You can connect to and access a database using DAO, from external
applications which support automation (viz. MS Excel, MS Word, etc.), and in this section we show how to
do this from Microsoft Excel by using VBA. With automation you can control another application (MS
Access) within your host application (MS Excel) without any manual intervention. Automation is used
typically to run macros or queries from Excel to connect to or create or manipulate MS Access database and
its structure, to access and manipulate MS Access data and reports, to import data from MS Access to Excel
for creating charts and pivot tables and otherwise use the data for calculations and analysis.

ODBC (Open Database Connectivity):
ODBC (Open Database Connectivity) is an interface which enables an application to connect and access a
relational database, using the SQL query syntax. An ODBC database is a DBMS (Database Management
System) for which there is an appropriate ODBC driver (examples of DBMS include SQL Server, Oracle,
AS/400, Foxpro, Microsoft Access). The ODBC Driver is a software that resides between the ODBC Client
(which is the front-end application wherein the driver is loaded) and the DBMS (wherein the data is stored
for access), and it translates the command into a format that is understood by the DBMS. DAO Object Model
uses the Microsoft Jet database engine and is optimized to work Microsoft Access files (.mdb), but ODBC
databases can also be accessed with DAO and the Microsoft Jet database engine. A database engine is the
underlying software component of a database used to manipulate its data. Jet (Joint Engine Technology) is
used by Microsoft Access as its database engine.

OLE DB and ODBC:
OLE DB was intended as a successor to improve on ODBC by providing an enhanced and faster interface
for data access. OLE DB is not bound to the SQL language like ODBC and it supports all forms of data
sources (ie. relational and non-relational data sources including mainframe and hierarchical databases, e-
mail and file systems, text and graphical data, custom business objects, ...) whereas ODBC was limited to
relational databases. OLE DB was complex to be used directly with Visual Basic and Microsoft's ADO
(ActiveX Data Objects) Object Model was introduced which interfaces with an OLE DB provider and enables
an application (viz. Excel) to access and manipulate data from a database (viz. MS Access).

ODBC vs DAO, ADO vs DAO:
When working with ODBC data sources, use ODBC. With ODBC you can access any data source for which
there is an appropriate ODBC driver for the database you want to access. Examples of ODBC databases
include Oracle, Microsoft SQL Server, Microsoft Visual FoxPro, IBM DB2, Microsoft Access. When working
with Microsoft Jet (.mdb) databases, using DAO will be more efficient. Examples of Microsoft Jet databases
include Micorsoft Access, Microsoft SQL Server, Paradox. DAO Object Model uses the Microsoft Jet
database engine and is optimized to work Microsoft Access files (.mdb), but ODBC databases can also be
accessed with DAO and the Microsoft Jet database engine when you want the Jet database engine's speed
and DAO's extra functionality. DAO precedes ADO and ODBC precedes OLE DB. ADO/OLE DB is
recommended for new projects but it might not be worthwhile to convert DAO code to ADO for existing
projects.

Add a reference to the DAO Object Library
To use DAO in your VBA project, you must add a reference to the DAO Object Library in Excel (your host
application) by clicking Tools-References in VBE, and then choose an appropriate version (mostly, you
should choose the highest version number), which is "Microsoft DAO 3.6 Object Library" for Access 2000
onwards.

C) The DBEngine object & workspace object

The highest level object in the DAO object model is the DBEngine object, and it contains all objects in the
hierarchy of DAO objects. There can only be one DBEngine object and there is no collection of which it is an
element of. The DBEngine object has many properties and methods, and contains 2 collections - the
Workspaces collection and the Errors collection. You can configure the database engine with properties and
methods provided by the DBEngine object. A database engine is the underlying software component of a
database used to manipulate its data. You can refer to DBEngine directly without explicitly declaring an
object variable of type DBEngine.

Create a Workspace object to define a user session by name, in which a user performs all database
operations by using the Microsoft Access database engine. The Workspace object allows you to open
multiple databases or connections in a session, and you can open additional sessions with the Workspace
object. A Workspace session starts on creation of a new Workspace object and ends when the Workspace
object Close method is used. Multiple sessions (ie. workspace objects) are specifically useful when you want
to perform operations as different users or when you want to manage separate and independent operations
in each session. All active and unhidden workspace objects are called the Workspaces collection, which is
contained in the DBEngine object. In DAO, when you open databases, they automatically exist within the
default workspace which is the first workspace in the Workspaces collection. A default workspace,
DBEngine.Workspaces(0), is automatically created when a Workspace object is first referred to or used, with
the name as "#Default Workspace#", and if security is not enabled with username as "admin" (if security is
implemented then username is set to the name of the user who logs on).

You can use the CreateWorkspace method to create a Workspace object. It is not necessary to append a
Workspace object to the Wokspaces collection after creating it, and in this case you will need to refer it by
the object variable used in the CreateWorkspace method. It will be required to append a Workspace object
to the Wokspaces collection after creating it, if you want to refer to it from the Workspaces collection by its
ordinal number or Name viz. DBEngine.Workspaces(0) or
DBEngine.Workspaces("WorkspaceObjectName") or DBEngine.Workspaces![WorkspaceObjectName]. All
defined DAO Workspace objects appended to the collection comprise the Workspaces collection. There are
2 types of Workspace objects, as defined by WorkspaceTypeEnum Enumeration in the CreateWorkspace
method - (i) Microsoft Jet Workspace objects (type - 'dbUseJet') which creates a Microsoft Access
workspace; and (ii) ODBCDirect workspaces (type - 'dbUseODBC') which are not supported in Microsoft
Office Access 2007. In this section we will discuss only the Microsoft Jet Workspace objects.

By default the DBEngine.DefaultUser Property is set to "Admin" and the DBEngine.DefaultPassword
Property is set to a zero-length string ("") and the default Workspace object's user and password are defined
accordingly. When you start Access or access an Access database with vba, all users automatically log-in
with the default name "Admin" and the password of zero-length string (""), but to access a database in a
secured system (ie. a secured Access Database) users must provide a username and a password (if a
password has been assigned to the user). In a secured system, for the default workspace you set the
DefaultUser and DefaultPassword properties (for the DBEngine object), and after the default session has
been initialized, additional sessions can be created with user names and passwords. Note that password is
case-sensitive but not the username. In this section we do not go into further details of accessing a secured
Microsoft Access database.

DBEngine.CreateWorkspace Method

Use the DBEngine.CreateWorkspace Method to create a new Workspace object. Syntax:
DBEngineObject.CreateWorkspace(Name, UserName, Password, UseType). All arguments, except
UseType, are mandatory. In the Name argument, specify a unique Workspace name for a session. In the
UserName argument, specify the name of the user for identification. In the Password argument, you are
required to enter a password for the Workspace object with a maximum of 20 characters. The UseType
argument specifies one of the WorkspaceTypeEnum values: (i) dbUseJet - (Microsoft Jet Workspace
objects) which creates a Microsoft Access workspace, and is also the default; and (ii) dbUseODBC - for
ODBCDirect workspaces which are not supported in Microsoft Office Access 2007.

In DAO, when you open databases, they automatically exist within the default workspace which is the first
workspace in the Workspaces collection. You need to use the DBEngine.CreateWorkspace Method only to
create a second workspace which is seldom required.

Example 1: DAO WorkSpace Object & Workspaces Collection.
1. Create a new Workspace object, using the CreateWorkspace method, and append to the Workspaces
collection.
2. Access properties of all workspace objects (ie. default workspace and the newly created workspace).

Sub AccessDAO_CreateWorkspace_1()
'Create a new Workspace object, using the CreateWorkspace method, and append to the Workspaces
collection.
'Access properties of all workspace objects (ie. default workspace and the newly created workspace).

'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 wrkSpace As DAO.Workspace, wrkSpaceNew As DAO.Workspace
Dim prpWrkSpace As DAO.Property

'create a new Microsoft Jet Workspace, with the default type of dbUseJet:
Set wrkSpaceNew = DBEngine.CreateWorkspace("newWS", "admin", "", dbUseJet)

'append the new workspace to the Workspaces collection:

DBEngine.Workspaces.Append wrkSpaceNew

'return the number of workspace objects in the Workspaces collection (returns 2 - default & new workspace):
MsgBox DBEngine.Workspaces.count

'access properties of all workspace objects (ie. default workspace and the newly created workspace):
For Each wrkSpace In DBEngine.Workspaces
'workspace name (returns "#Default Workspace#" & "newWS"):
MsgBox "Workspace Name: " & wrkSpace.Name
'username property setting:
MsgBox wrkSpace.UserName
'properties of workspace object:
For Each prpWrkSpace In wrkSpace.Properties
MsgBox "Property Name: " & prpWrkSpace.Name
Next prpWrkSpace
Next wrkSpace

'returns the name of the default Workspace, ie. "#Default Workspace#":

MsgBox DBEngine.Workspaces(0).Name
'because the Workspace object has been appended to the Wokspaces collection after creating it, we can
refer to it from the Workspaces collection by its ordinal number or Name (returns "newWS"):
MsgBox DBEngine.Workspaces("newWS").Name
'if the Workspace object had not been appended to the Wokspaces collection after creating it, in this case
you would have had to refer it by the object variable used in the CreateWorkspace method (returns
"newWS"):
MsgBox wrkSpaceNew.Name

'close the objects:

wrkSpaceNew.Close

'destroy the variables:

Set wrkSpace = Nothing
Set wrkSpaceNew = Nothing
Set prpWrkSpace = Nothing

End Sub

DAO Workspace Object Methods

DAO Workspace Object Methods: The Close Method is used to close an open
Workspace. CreateDatabase method is used to create a new database and the OpenDatabase method is
used to open an existing database. To manage transaction processing during a session (ie. when a series of
database changes made in a session are treated as one unit), you have three transaction methods of
BeginTrans, CommitTrans and Rollback. The OpenConnection Method, available only in an
ODBCDirect workspace, is used to open a connection to an ODBC data source. Note that Microsoft Office
Access 2007 does not support ODBCDirect workspaces.

DAO Databases

DBEngine.OpenDatabase Method

Use the DBEngine.OpenDatabase Method to open a Database, as specified by its name/path. A reference
to the Database object variable (to which the database is assigned) is returned by this method, and the
database is not actually opened in the Microsoft Access window. If you open a database object without
specifying a workspace, it will exist within the default workspace: DBEngine.Workspaces(0). Syntax:
DBEngineObject.OpenDatabase(Name, Options, ReadOnly, Connect). Name argument is mandatory
while all other arguments are optional. In the Name argument you will specify the database file name and full
path, which you want to open. In the Options argument, you can specify False which is the Default and
opens the database in shared mode while specifying True opens the database in exclusive mode. In
the ReadOnly argument specifying False (default) will open the database with read-write access and
specifying True will open in read-only. Connect argument is used to specify connection information (ex.
password).

DAO Workspace.OpenDatabase Method

Use the DAO Workspace.OpenDatabase Method to open a Database, as specified by its name/path, in
the specified Workspace object. A reference to the Database object variable (to which the database is
assigned) is returned by this method, and the database is not actually opened in the Microsoft Access
window. Syntax: WorkspaceObject.OpenDatabase(Name, Options, ReadOnly, Connect). The
arguments are similar to as in the DBEngine.OpenDatabase Method, explained above.

DAO DBEngine.CreateDatabase Method

Use the DAO DBEngine.CreateDatabase Method to create, open and save a new Database. A reference
to the Database object variable (to which the new database is assigned) is returned by this method. Note
that this method creates a new empty database, which you will need to structure and enter content
thereafter. If you create a database object without specifying a workspace, it will exist within the default
workspace: DBEngine.Workspaces(0). Syntax: DBEngineObject.CreateDatabase(Name, Locale,
Option). Name and Locale arguments are mandatory. In the Name argument (max 255 characters) you will
specify the file name and full path of the database which is being created. The Locale argument specifies a
collating order for the database (this is equated with the Database.CollatingOrder Property which specifies
the database sort order sequence) ie. the character set to be used to determine how database values will be
sorted. Specifying the constant "dbLangGeneral" for this argument means creating a database which will
support sorting for "English, German, French, Portuguese, Italian, and Modern Spanish". A password for the
new Database can also be created in concatenation with the constant specified in the Locale argument
viz. dbLangGeneral & ";pwd=123", where password is "123". The Option argument specifies a constant to
determine the version for the data format and if the database should be encrypted, and not specifying a
constant will create an un-encrypted database.

DAO Workspace.CreateDatabase Method

Use the DAO Workspace.CreateDatabase Method to create, open and save a new Database. A reference
to the Database object variable (to which the new database is assigned) is returned by this method. Note
that this method creates a new empty database, which you will need to structure and enter content
thereafter. This method creates a new Database and opens it in the specified workspace object. Syntax:
Workspace.CreateDatabase(Name, Locale, Option). The arguments are similar to as in the
DBEngine.CreateDatabase Method, explained above.

Example 2: Open an existing Database, Create a new Database.
1. Open an existing Database using the DAO OpenDatabase Method.
2. Create a new Database using the DAO CreateDatabase Method.
3. Return Databases and enumerate their properties in a Workspace.

Sub AccessDAO_OpenDatabaseCreateNewDatabase_2()
'Create a New Microsoft Jet Workspace; Open an existing Database using the DAO OpenDatabase Method;
Create a new Database using the DAO CreateDatabase Method; Return Databases and enumerate their
Properties in a Workspace;

'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, strDBNameNew As String, strDBNew As
String
Dim daoDB As DAO.Database, daoDBNew As DAO.Database, daoDBS As DAO.Database
Dim wrkSpaceNew As DAO.Workspace
Dim prpDB As DAO.Property

'---------------
'SET DATABASE NAMES (EXISTING & NEW):

'your data source with which to establish connection - ENTER the existing 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

'name of the new MS Access Database being created:

strDBNameNew = "SalesReportNew.accdb"
'set the string variable to the new Database:
strDBNew = strMyPath & "\" & strDBNameNew

'---------------
'CREATE A NEW MICROSOFT JET WORKSPACE:

'create a new Microsoft Jet Workspace, with the default type of dbUseJet:
Set wrkSpaceNew = DBEngine.CreateWorkspace("newWS", "admin", "", dbUseJet)

'append the new workspace to the Workspaces collection:

DBEngine.Workspaces.Append wrkSpaceNew

'---------------
'OPEN AN EXISTING DATABASE:

'open the database in the default workspace:

'Set daoDB = DBEngine.Workspaces(0).OpenDatabase(strDB)
'If you open a database object without specifying a workspace, it will exist within the default workspace:
'Set daoDB = DBEngine.OpenDatabase(strDB)

'If you open a database by specifying a workspace object, it will exist within the specified workspace:
Set daoDB = wrkSpaceNew.OpenDatabase(strDB, True)
'alternatively:
'Set daoDB = DBEngine.Workspaces("newWS").OpenDatabase(strDB, False)

'---------------
'CREATE A NEW DATABASE:

'If you create a database object without specifying a workspace, it will exist within the default workspace:
Set daoDBNew = DBEngine.CreateDatabase(strDBNew, dbLangGeneral)
'alternatively, to create a new database and open in the new Workspace object:
'Set daoDBNew = wrkSpaceNew.CreateDatabase(strDBNew, dbLangGeneral)

'---------------
'ACCESS DATABASES IN EACH WORKSPACE (DEFAULT AND NEW WORKSPACE):

'return the number of database objects in the new Workspace:

MsgBox "No of database objects in the new Workspace: " & wrkSpaceNew.Databases.count

'access databases in the new workspace:

For Each daoDBS In wrkSpaceNew.Databases
MsgBox daoDBS.Name
For Each prpDB In daoDBS.Properties
MsgBox "Property Name: " & prpDB.Name
Next prpDB
Next daoDBS

'return the number of database objects in the default Workspace:

MsgBox "No of database objects in the default Workspace: " & DBEngine.Workspaces(0).Databases.count

'access databases in the default workspace:

For Each daoDBS In DBEngine.Workspaces(0).Databases
MsgBox daoDBS.Name
For Each prpDB In daoDBS.Properties
MsgBox "Property Name: " & prpDB.Name
Next prpDB
Next daoDBS

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

'destroy the variables:

Set daoDB = Nothing
Set daoDBNew = Nothing
Set daoDBS = Nothing
Set wrkSpaceNew = Nothing
Set prpDB = Nothing

End Sub

Return a Reference to the Current Database - CurrentDb Method

Use the CurrentDb Method to return a reference to the database which is currently open in the Microsoft
Access window, from vba code. The method returns a database object, without the need to specfy the
database name. You can use other DAO objects with the database object variable returned by this method.
A reference to the current database is provided by the first member of the Databases collection. The
reference pointed to the current database by using the syntax DBEngine(0)(0) can also be used but this
syntax refers to the open copy of the current database, whereas with the CurrentDb method you can create
'multiple database object variables' referring to the current database because this method creates a new
instance of the current database making it amenable for multi users. However, it is much slower to use
CurrentDb than using DBEngine(0)(0). Note that another Database can be opened and worked upon
simultaneously, using the OpenDatabase method, while the current database is already open in the
Microsoft Access window.

Example 3: CurrentDb Method - return a reference to the currently open database.

Sub AccessDAO_ReferCurrentDatabase_3()
'CurrentDb Method - return a reference to the currently open database.

'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 daoCDB1 As DAO.Database, daoCDB2 As DAO.Database, daoDB As DAO.Database
Dim recSet As DAO.Recordset

'---------------
'RETURN MULTIPLE INSTANCES OF THE DATABASE CURRENTLY OPEN IN THE MICROSOFT
ACCESS WINDOW:

'assign current database reference to multiple object variables of type Database:

Set daoCDB1 = CurrentDb
Set daoCDB2 = CurrentDb

MsgBox daoCDB1.Name
MsgBox daoCDB2.Name

'refer DAO TableDef Object in current database - you need to first assign the current database reference to
an object variable (ex. daoCDB2):
Dim daoTblDef As DAO.TableDef
Dim fld As DAO.Field
Set daoTblDef = daoCDB2.TableDefs("SalesManager")
For Each fld In daoTblDef.Fields
MsgBox fld.Name
Next fld

'----
'USE CurrentDb DIRECTLY WITH A RECORDSET OBJECT:

'CurrentDb can be used directly with a Recordset object, while in most other DAO objects you need to first
assign the current database reference to an object variable as above.
Set recSet = CurrentDb.OpenRecordset("SalesManager", dbOpenDynaset)
'displays first 3 fields of the first record:
MsgBox recSet.Fields(0)
MsgBox recSet.Fields(1)
MsgBox recSet.Fields(2)

'---------------
'OPEN ANOTHER DATABASE USING THE OpenDatabase METHOD, TO WORK ON SIMULTANEOUSLY,
WHILE THE CURRENT DATABASE IS ALREADY OPEN IN THE MICROSOFT ACCESS WINDOW:

'open the database in the default workspace:

Set daoDB = DBEngine.OpenDatabase(strDB)

MsgBox daoDB.Name

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

'destroy the variables:

Set daoCDB1 = Nothing
Set daoCDB2 = Nothing
Set daoDB = Nothing
Set daoTblDef = Nothing
Set fld = Nothing
Set recSet = Nothing

End Sub

D) Tables of a DAO Database

TableDef Object and TableDefs collection

A TableDef object, with its properties and methods, is used to manipulate a table definition. With a
TableDef object you can: create a new table (Database.CreateTableDef Method); create or add a new Field
in a table (TableDef.CreateField Method); create a new Index (TableDef.CreateIndex Method); create a new
Recordset and append it to the Recordsets collection (TableDef.OpenRecordset Method); update a linked
table's connection information (TableDef.RefreshLink Method); set or return information about a linked table
(TableDef.Connect Property); set or return the name of a linked table (ableDef.SourceTableName Property);
set or return validation value/rule for a field's data (TableDef.ValidationRule Property); set or return the text
message displayed when the field value does not conform to the ValidationRule (TableDef.ValidationText
Property); and so on.

All stored TableDef objects in a database are referred to as the TableDefs collection. You create a new
TableDef object using the Database.CreateTableDef Method. It is required to append a TableDef object to
the TableDefs collection after creating it, using the DAO TableDefs.Append Method. You can refer to a
TableDef object in the TableDefs collection by its ordinal number or Name viz. TableDefs(0) or
TableDefs("TableDefObjectName") or TableDefs![TableDefObjectName].

Database.CreateTableDef Method

Use the Database.CreateTableDef Method to create a new TableDef object. Syntax:
DatabaseObject.CreateTableDef(Name, Attributes, SourceTableName, Connect). All arguments are
optional to specify. The Name argument sets the name of the TableDef object, which can be a maximum of
64 characters. The Attributes argument sets a value indicating characteristic(s) of the TableDef object. The
Attributes Property is read/write for a TableDef object till it is appended to its collection.
The SourceTableName argument specifies the name of a linked or the base table that is the original data
source in an external database. The Connect argument is a String value, which provides information of a
TableDef object's linked table or an open database source, consisting of a database type specifier and a
database path.

Note that it is required to define one Field atleast before you can append a TableDef object to the TableDefs
collection. Use the TableDefs.Delete Method to delete a TableDef object from the TableDefs collection.

E) Fields / Columns of a Table

A Field object corresponds to a column of data of similar data type and properties. The Index, QueryDef,
Relation and TableDef objects all have a Fields collection, which represents all stored Field objects as
specified in the respective object. The Recordset object also has a Fields collection, which represents all
stored Field objects in a record or a row of data. A field object has its own properties & methods by which it
is manipulated.

Create a new Field

TableDef.CreateField Method

Use the TableDef.CreateField Method to create a new Field object. Syntax:
TableDefObject.CreateField(Name, Type, Size). All arguments are optional to specify.
The Name argument specifies a name for the new Field. The Type argument sets the data type of the Field,
as indicated by a constant. The Size argument determines the maximum size of a Field. For a Field with
character data (except Memo), size determines the maximum number of characters; for numeric fields, it is
the maximum size in bytes (of storage). Text fields can be set upto a maximum of 255 characters for a
Microsoft Access database, whereas for non-Text fields the size is automatically determined by their Type
property. Not specifying the Size will default the Field size to as permissible by the database. For Memo or
Long Binary Fields use the Field.FieldSize Property to determine the size in the number of bytes used in the
database, whereas use the Size property for all other Field data types.

You can use the CreateField method to add a new field to an Index or Relation object. To add a field to an
Index object, use the DAO Index.CreateField Method, Syntax: IndexObject.CreateField(Name, Type, Size).
The type and size arguments are not supported for an Index object, and are ignored in this case. To add a
field to a Relation object, use the DAO Relation.CreateField Method, Syntax:
RelationObject.CreateField(Name, Type, Size). The type and size arguments are not supported for a
Relation object, and are ignored in this case.

Fields Collection Properties & Methods

Count the number of Fields

The Count property of the fields collection determines the number of fields in a collection, wherein
numbering for members of a collection begins with zero. If you have seven fields in a Recordset, using
RecordsetObject.Fields.count will return 7, and RecordsetObject.Fields(0) will return the value of the first
field [OrdinalPosition of the first field is 0].

Access Fields by their ordinal position or Name property

You can Access Fields by their ordinal position or Name property viz. Recordset.Fields.
(Name/OrdinalPosition). Recordset.Fields(0).Name returns the Name of the first field, and
Recordset.Fields(0).Value returns the content in the first field. The Value property of the Field object is its
Default property viz Recordset.Fields(0) is the same as Recordset.Fields(0).Value and will return the first
fields's value.

Examples: To reference a field named "FirstName", which is the second field in the table, you can use any
of the following:-
RecordsetObject.Fields("FirstName")
RecordsetObject.Fields(1)
RecordsetObject![FirstName]

DAO Fields.Append Method

To add or append a new field to the Fields Collection of a TableDef or an Index object, use the
DAO Fields.Append Method. To add a field to a table, use the Syntax:
TableDefObject.Append(FieldObject). To add a field to an Index, use the Syntax:
IndexObject.Append(FieldObject). The FieldObject argument mentions the Field Object variable which is
being appended and is necessary to specify.

DAO Fields.Delete Method

To delete a field from the Fields Collection, use the DAO Fields.Delete Method. To delete a field from a
table, use the Syntax: TableDefObject.Fields.Delete(Name). The Name argument mentions the name of
the Field which is being deleted and is necessary to specify. 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 Fields.Refresh Method

The relative position of a Field object within the Fields collection is usually the order in which the field has
been appended in the collection, the first appended field at first position will have an OrdinalPosition of 0
(zero), the second appended field at second position will have an OrdinalPosition of 1, and so on, and this
position can be changed (or returned) by using the DAO Field.OrdinalPosition Property. A change in the
ordinal position of a Field may not change the order of the Fields in the collection unless the DAO Fields.
Refresh Method is used. 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.

Field Object Properties

Field properties are used to determine or return the name, size, type and characteristics of a Field. Some of
these properties are elaborated below.

DAO Field.Name Property

Use the DAO Field.Name Property to set or return a Field's name. It is a String value not exceeding 64
characters. The property is read-only after the Field object is appended to the Fields collection, before which
it is read/write.

DAO Field.Value Property

Use the Field.Value Property to return, enter or edit the data in a Field. This is the default property of a
Field object viz. you can refer to a field object without specifying the value property. For example, entering
"Lisa" in the field named "FirstName" can be done either as RecordSetObject.Fields("FirstName")
= "Lisa" or as RecordSetObject.Fields("FirstName").Value = "Lisa".

DAO Field.OrdinalPosition Property

The relative position of a Field object within the Fields collection is usually the order in which the field has
been appended in the collection, the first appended field at first position will have an OrdinalPosition of 0
(zero), the second appended field at second position will have an OrdinalPosition of 1, and so on, and this
position can be changed (or returned) by using the DAO Field.OrdinalPosition Property. Note that this
property uses "relative postion" so that if you have 3 fields and you change the OrdinalPosition property of
these to 10, 12 & 15, then the field with OrdinalPosition value of 12 will be returned in an order relative to the
others, ie. between the fields whose values have been set as 10 and 15.

The property is read-write for a Field object before it is appended to a Fields collection. After a Field object is
appended, for Fields contained within a TableDef object it is read-write, and for fields contained within
Recordset or QueryDef objects it is read-only, but the property is not supported for fields contained within
Index & Relation objects.

DAO Field.Size Property

The Field.Size Property determines the maximum size of a Field. For a Field with character data (except
Memo), size determines the maximum number of characters; for numeric fields, it is the maximum size in
bytes (of storage). For Text fields you must set the Size property which can be set upto a maximum of 255
characters for a Microsoft Access database, whereas for non-Text fields the size is automatically determined
by their Type property. Not specifying the Size will default the Field size to as permissible by the database.
For Memo or Long Binary Fields use the Field.FieldSize Property to determine the size in the number of
bytes used in the database, whereas use the Size property for all other Field data types.

The property is read-write for a Field object before it is appended to a Fields collection. After a Field object is
appended, the property is supported for Fields contained within a TableDef object, Recordset object or
QueryDef object, wherein it is read-only, but the property is not supported for fields contained within Index &
Relation objects.

DAO Field.Type Property

Use the Type property to set or return the operational or data type of a Field. The value returned by this
property is a constant which indicates the data type of a field. The property is read-only after the Field object
is appended to the Fields collection or to any object, before which it is read/write.

Examples of data type constants that are supported by DAO, for the Type property, include:
dbBoolean (Boolean Value - Yes/No); dbChar (Char); dbCurrency (Currency); dbDate (Date); dbDouble
(Double); dbGUID (GUID); dbInteger (Integer); dbBigInt (Big Integer); dbSingle (Single); dbLong (Long);
dbMemo (Memo); dbText (Text); ....

DAO Field.Attributes Property

Use the DAO Field.Attributes Property to set (or return) the Field characteristics. The Field
characteristic(s) is specified by a value or constant, which can be: dbAutoIncrField (to automatically
increment the Field value to a unique Long integer); dbDescending (to sort Field values in a descending
order - default sort order for a Field is Ascending if this attribute is not specified - this attribute is applicable
only to an index field ie. to Fields collection of an Index); dbFixedField (dbFixedField specifies that the field
has a fixed size - numeric fields have a Fixed field size by default - maps to ADO column attribute
adColFixed); dbVariableField (valid only for text fields, it specifies a Variable Field size ie. the Text data
type Field can store variable text lengths); dbUpdatableField (when the Field value can be updated or
changed); dbHyperlinkField (hyperlink field, valid only for Memo field types); dbSystemField (these fields
cannot be deleted).

To create an auto-increment field, set data type of field to Long and set Attributes property to
dbAutoIncrField. An auto-increment field (also referred to as AutoNumber field) by default starts at 1 and
increments sequentially and can be used aptly as a primary key field to automatically insert unique numbers
in a field.

Set multiple attributes - sum the respective constants (using the plus "+" sign) to set multiple attributes,
wherein any non-meaningful values get ignored without giving an error.

This property is read/write for a field before being appended to a collection. For a Field object after it is
appended to a collection: for Fields contained within a TableDef object, the property is read/write; for Fields
contained within an Index object, this property remains read-write until the TableDef object which contains
the Index object is appended to a Database and then read-only thereafter; for Fields contained within a
Recordset object or QueryDef object, the property is read-only; for Fields contained within a Relation object
this property is not supported.

DAO Field.DefaultValue Property

Use the Field.DefaultValue Property to specify the default value to be entered in a Field automatically on
creation of a new record. A text or an expression of String data type upto a maximum of 255 characters, can
be specified as the default value. For AutoNumber and Long Binary fields, this property is not applicable.

The property is read-write for a Field object before it is appended to a Fields collection. After a Field object is
appended, for Fields contained within a TableDef object it is read-write, and for fields contained within
Recordset or QueryDef objects it is read-only, but the property is not supported for fields contained within
Index & Relation objects.

DAO Field.Required Property

The Field.Required Property determines whether a field can accept null values. Setting the Property to
False will allow null values in the 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.

The property is read-write for a Field object before it is appended to a Fields collection. After a Field object is
appended, for Fields contained within a TableDef object it is read-write, and for fields contained within
Recordset or QueryDef objects it is read-only, but the property is not supported for fields contained within
Index & Relation objects.

DAO Field.AllowZeroLength Property

Setting the Field.AllowZeroLength Property to True will allow value for Text or Memo data type Fields to
be set to an empty or zero-length string (""). Zero Length string vs Null value: Note that in Access when you
specify a Zero Length string it means that you actually specify a value, and when you set the Required
porperty to be True it means that the Field can have a Null value which means that NO value needs to be
entered - for the user there is no visible difference between the two.

The property is read-write for a Field object before it is appended to a Fields collection. After a Field object is
appended, for Fields contained within a TableDef object it is read-write, and for fields contained within
Recordset or QueryDef objects it is read-only, but the property is not supported for fields contained within
Index & Relation objects.

DAO Field.ValidationRule Property

Use the Field.ValidationRule Property to validate a field's data with a specified rule or condition. The
property specifies a string value as a comparison like in a WHERE clause (as used in SQL statements) but
does not use the WHERE word. If the field's value does not conform to the specified rule or condition, the
error message (a string value) as specified by the ValidationText property gets displayed. Only databases
using the Microsoft Access database engine support validation.

The property is read-write for a Field object before it is appended to a Fields collection. After a Field object is
appended, for Fields contained within a TableDef object it is read-write, and for fields contained within
Recordset or QueryDef objects it is read-only, but the property is not supported for fields contained within
Index & Relation objects.

DAO Field.ValidationText Property

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 specified by the ValidationRule property.

The property is read-write for a Field object before it is appended to a Fields collection. After a Field object is
appended, for Fields contained within a TableDef object it is read-write, and for fields contained within
Recordset or QueryDef objects it is read-only, but the property is not supported for fields contained within
Index & Relation objects.

Example 4a: Create Tables and Fields in a DAO Database.
Refer Image 4a as mentioned in the code.
1. Create a New Database;
2. Create & Append Tables (ie. TableDef objects);
3. Create & Append Fields;
4. Enumerate Tables in the Database and their Properties;
5. Enumerate Fields in a Table;
6. Delete Fields & Tables;

Image 4a

Sub AccessDAO_CreateTablesCreateFields_4a()
'Create a New Database;
'Create & Append Tables (ie. TableDef objects);
'Create & Append Fields;
'Enumerate Tables in the Database and their Properties;
'Enumerate Fields in a Table;
'Delete Fields & Tables;

'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 daoTD As DAO.TableDef, daoTD1 As DAO.TableDef, daoTD2 As DAO.TableDef
Dim daoFld As DAO.Field
Dim daoPrp As DAO.Property

'---------------
'get path / location of the database, presumed to be in the same location as the host workbook:
strMyPath = ThisWorkbook.Path
'name of the new MS Access Database being created:
strDBName = "SalesReportNew.accdb"
'set the string variable to the new Database:
strDB = strMyPath & "\" & strDBName

'---------------
'CREATE A NEW MS ACCESS DATATABASE, TABLES AND FIELDS:
'Refer Image 4a to view Tables & Fields (in "SalesManager" Table) of the new Database
("SalesReportNew.accdb") after running below code, before deleteing any table or field.

'Create a New Database: If you create a database object without specifying a workspace, it will exist within
the default workspace:
Set daoDB = DBEngine.CreateDatabase(strDB, dbLangGeneral)

'Create Tables (ie. TableDef objects) named "SalesManager" and "Performance":

Set daoTD1 = daoDB.CreateTableDef("SalesManager")
Set daoTD2 = daoDB.CreateTableDef("Performance")

'Before you append the TableDef object to the TableDefs collection, you will create Fields and append them
to the new TableDef object (Table named "SalesManager") created above:
With daoTD1
'create a new auto increment field, and set attribute:
'set data type of field to Long, set Attributes property to dbAutoIncrField. An auto-increment field (also
referred to as AutoNumber field) by default starts at 1 and increments sequentially and can be used aptly as
a primary key field to automatically insert unique numbers in a field.
.Fields.Append .CreateField("EmployeeId", dbLong)
.Fields("EmployeeId").Attributes = dbAutoIncrField + dbFixedField

'create a Text field, maximum 30 characters, and required.

Set daoFld = .CreateField("FirstName", dbText, 30)
daoFld.Attributes = dbVariableField
daoFld.Required = True
.Fields.Append daoFld

'Text field, allow zero length, and max 25 characters:

.Fields.Append .CreateField("SurName", dbText, 25)
.Fields("SurName").Required = True
.Fields("SurName").AllowZeroLength = True

'create a Date field with a validation rule and Validation Text:

Set daoFld = .CreateField("JoinDate", dbDate)
'specify a default value for the field:
daoFld.DefaultValue = "#04/01/2010#"
'validate the field's value, before it is set, with a specified rule or condition:
daoFld.ValidationRule = ">=#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:
daoFld.ValidationText = "JoinDate should be on or after 04/01/2010 but within current date"
.Fields.Append daoFld

'Currency Field and required:

.Fields.Append .CreateField("Sales", dbCurrency)
.Fields("Sales").Required = True

'create a Boolean (Yes/No) field:

.Fields.Append .CreateField("NewJoinee?", dbBoolean)

'create a Hyperlink field, and set the attribute:

Set daoFld = .CreateField("WebProfile", dbMemo)
daoFld.Attributes = dbHyperlinkField + dbVariableField
.Fields.Append daoFld
End With

Set daoFld = Nothing

'Before you append the TableDef object to the TableDefs collection, you will create Fields and append them
to the new TableDef object (Table named "Performance") created above:
With daoTD2
.Fields.Append .CreateField("FirstName", dbText)
End With

'Save/Append the tables "SalesManager" & "Performance" to the TableDefs collection:
daoDB.TableDefs.Append daoTD1
daoDB.TableDefs.Append daoTD2

'---------------
'ENUMERATE DATABASE TABLES AND PROPERTIES:

'return number of Tables in database:

MsgBox daoDB.TableDefs.count

'Enumerate Tables in the database and their properties:

For Each daoTD In daoDB.TableDefs
'Use the Attributes property to ignore System and Hidden Tables. The Attributes argument returns or sets a
value indicating characteristic(s) of the TableDef object. Note, that to check a specific attribute we have used
the logical AND operator for comparison of: (i) the TableDef Attributes property and the dbSystemObject
constant; and (ii) the TableDef Attributes property and the dbHiddenObject constant. You can alternatively
do an equivalency test using numerical values of the constants.
If ((daoTD.Attributes And dbSystemObject) Or (daoTD.Attributes And dbHiddenObject)) Then
Else
For Each daoPrp In daoTD.Properties
MsgBox "Table Name: " & daoTD.Name & " - Property Name: " & daoPrp.Name & ", Property Type: " &
daoPrp.Type & ", Property Value: " & daoPrp.Value
Next
End If

'Alternate If statement to ignore System and Hidden Tables: dbSystemObject constant has a numerical
value of -2147483646; dbHiddenObject constant has a numerical value of 1;
'If daoTD.Attributes >= 0 And daoTD.Attributes <> 1 Then
'MsgBox "Table Name: " & daoTD.Name
'End If
Next

'---------------
'ENUMERATE TABLE FIELDS:

'return number of Fields in the Table named "SalesManager":

MsgBox daoTD1.Fields.count

'Enumerate Fields and return their name, type & size in the Table named "SalesManager":
For Each daoFld In daoTD1.Fields
MsgBox daoFld.Name & ", " & daoFld.Type & ", " & daoFld.Size
Next

'---------------
'DELETE FIELDS AND TABLES IN DATABASE:

'Delete a field in the Table named "SalesManager":

'daoTD1.Fields.Delete "SurName"

'Delete the Table (TableDef object) named "Performance" created above:

'daoDB.TableDefs.Delete daoTD2.Name
'or
daoDB.TableDefs.Delete "Performance"

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

'destroy the variables:

Set daoDB = Nothing
Set daoTD = Nothing
Set daoTD1 = Nothing
Set daoTD2 = Nothing
Set daoFld = Nothing
Set daoPrp = Nothing

End Sub

F) Recordset & Records of a DAO Database Table

After connecting to a database, you can manipulate its data. In Microsoft Access, Recordset objects are
used to access and manipulate data in a database. A Recordset object represents a set of records in a
database table, or a set of records returned from running a query. Both DAO and ADO libraries have a
Recordset object, though the methods, properties, and options of the respective object is different. A Record
object is one row of data in a Recordset. A Recordset object has a Fields collection which contains all the
Field objects, where each Field object represents a column in the Recordset. In other words, each record
represents a row of data and contains many fields, and each field corresponds to a column in the database
table.

In your VBA code, you should ideally precede the object name by its program ID (ProgID) prefix, which in
ADO is "ADODB" and in DAO is "DAO". Many objects, for example the Recordset object, have similar
names in both DAO and ADO and it is advisable to have explicit references in your project. This becomes a
must if you have included references to both the DAO and ADO libraries in your VBA project, else the object
library mentioned first in the References list will prevail, resulting in confusion in the vba code.

While instantiating the Recordset object, you should use:
Dim daoRecSet As DAO.Recordset
Dim adoRecSet As ADODB.Recordset
instead of:
Dim RecSet As Recordset

DAO Recordset Types

There are five types of Recordsets:

1. A Table-type recordset is based on a Table and not on a query. A Table-type recordset is created only
when working with a single non-linked table. Valid for Microsoft Access Jet workspaces only. You can use
the Seek method (but not the Find Method) to search through this recordset (using a Table index), which is
faster than using the Find method.

2. A Dynaset-Type recordset results from a query. The set of records can contain fields from one or more
underlying tables or any linked table (ie. any table linked to the Access Database). The recordset gets
dynamically updated and reflects any change made to the underlying records. After the recordset has been
created it does not add any new record satisfying the criteria. This recordset type supports the Find method
(but not the Seek method) to search through the recordset, which is however slower than the Seek method.

3. A Snapshot-type recordset shows the data as at the time when a snapshot is taken ie. when a recordset
is created. The set of records can contain fields from one or more underlying tables or any linked table. This
recordset type is a static copy of the records and does not get dynamically updated and does not reflect any
change made to the underlying records. If a field value is changed in a record, it will not be updated
dynamically like it gets done in a Dynaset-Type recordset and you will need to refresh the recordset to
update. This recordset type is used to read data and searching through this recordset is very fast. This
recordset type supports the Find method to search through the recordset.

4. A Forward-only-type recordset is identical to a snapshot-type wherein only scroll forward through
records is possible.

5. A Dynamic-type recordset results from a query from one or more underlying tables. In this recordset you
can add, change or delete records from a row-returning query. This recordset is similar to the Dynaset-Type
except that after running a query the matching records added, deleted or edited in the underlying tables by
other users also get automatically reflected in the recordset of this type. Valid for ODBCDirect workspaces
only.

All active recordsets of a database are contained in the Recordsets collection, wherein a recordset gets
appended to the collection when it is opened and gets removed from the collection when it is closed (using
the Close method). Each recordset object contains a collection of the fields and a collection of the indexes in
the underlying table.

DAO Recordset.Type Property

Use the Recordset.Type Property to set or return the type of Recordset, using the RecordsetTypeEnum
constants or values. There are five settings: dbOpenTable (value-1, Table-type, for Microsoft Access
workspaces only); dbOpenDynaset (value-2, Dynaset-type); dbOpenSnapshot (value-4, Snapshot-type);
dbOpenForwardOnly (value-8, Forward-only type); dbOpenDynamic (value-16, Dynamic-type, for
ODBCDirect workspaces only).

Create a new Recordset object

Create a Recordset object and append it to the Recordsets collection, using the OpenRecordset method.
You can use the OpenRecordset method to open a Recordset that is based on a Table, SQL statement,
stored or parameter query as detailed below. Arguments have the same meaning across the
OpenRecordset methods.

DAO Database.OpenRecordset Method. Syntax: DatabaseObject.OpenRecordset(Name, Type,
Options, LockEdit). In this method you pass the Table name to the method and use the Set operator to
return the recordset.

Only the Name argument is mandatory to specify while other arguments are optional. The Name argument
specifies the source of records and can be the name of a table or a query, or it can be an SQL statement. In
the Type argument, specify one of the five types for the recordset by using the constants: dbOpenTable
(Table-type); dbOpenDynaset (Dynaset-type); dbOpenSnapshot (Snapshot-type); dbOpenForwardOnly
(Forward-only type); dbOpenDynamic (Dynamic-type). A Recordset created in a Microsoft Access local table
without specifying the Type will default to Table-type. Executing the OpenRecordset method on a linked
table or query, without specifying the Type, will default to Dynaset-type. In the Options argument you can
specify one of the many constants representing the recordset's characteristics viz. specifying dbReadOnly
opens a recordset as read-only, and so on. The LockEdit argument specifies a constant which determines
the type of record locking used when a recordset is opened.

DAO TableDef.OpenRecordset Method. Syntax: TableDefObject.OpenRecordset(Type, Options). In
this method you first get a reference to the Table (TableDefObject) and then use the Set operator to return
the recordset.

DAO Recordset.OpenRecordset Method. Syntax: RecordsetObject.OpenRecordset(Type, Options). In
this method you first get a reference to a Recordset Object and then use the Set operator. Refer Example 7
wherein this method is used to Filter Records.

Creating a Recordset, based on a stored query or a parameter query, using the QueryDef.OpenRecordset
Method. For details on this method, refer section "Create and Exceute a Query".

Create a new record in a Database Table - use the AddNew & Update methods

DAO Recordset.AddNew method

Create a new record for a Recordset object, using the Recordset.AddNew method. Syntax:
RecordsetObject.AddNew. When you use this method, the field's value is set to its default value and in the
absence of a default value specification, it is set to Null. Ensure that after adding a new record using the
AddNew method or after making any changes in a record using the Edit method, you must save the record
and/or any changes to it by using the Update method BEFORE you perform any operation like move to
another record or use the Edit or AddNew method again or close the recordset or set bookmark property for
another record.

In DAO, after using the Update method, the current record will be the record which had focus before the
AddNew method. Using the LastModified property (for a DAO recordset) returns a Bookmark pointing to
the most recent added / modified record and setting the Bookmark property to this bookmark will make the
new record as the current record.

When you add a record to a dynaset-type Recordset, using the AddNew method, the new record will appear
at the end of the recordset disregrading the recordset's sorting if any. In this case you can re-create the
recordset or use the ReQuery method to have the new record appear in its sorted position. When a record is
added to a table-type Recordset, the new record appears in accordance with the recordset's index and in
the absence of an index the new record will appear at the end of the recordset.

DAO Recordset.Update Method

Ensure that after adding a new record using the AddNew method or after making any changes in a record
using the Edit method, you must save the record and/or any changes to it by using the Recordset.Update
Method BEFORE you perform any operation like move to another record or use the Edit or AddNew method
again or close the recordset or set bookmark property for another record. Syntax: RecordsetObject
.Update(UpdateType, Force). Both arguments of UpdateType & Force are optional.

Recordset.LastModified Property

In Table-type or Dynaset-Type recordsets, a bookmark of the record which has most recently been added or
changed, is returned by the Recordset.LastModified Property. Use this property to bookmark and move to
the record which has last been added or modified. Syntax: RecordsetObject.LastModified. Refer Example
4b for using this property.

Recordset.Bookmark Property

Use the Recordset.Bookmark Property to uniquely identify the current record by setting or returning a
bookmark. You can create any number of bookmarks in a recordset, by saving each bookmark and
assigning its value to a variable and then return to that record by setting the Recordset's Bookmark property
to the variable. Refer Example 4b for using this property.

Edit Records in a Recordset

Use the DAO Recordset.Edit Method to make changes to fields of the current record in a Recordset object.
Ensure that after making any changes in a record using the Edit method, you must save the changes to it by
using the Update method. After editing the record, the current record to which changes are made remains
current ie. the record with focus. Syntax: RecordsetObject.Edit.

Moving between Records in a Recordset

DAO Recordset.Move Method. Syntax: RecordsetObject.Move(Rows, StartBookmark). This method
moves the position of the current record as per the specified number of rows (Rows argument) starting from
a bookmarked record specified by the StartBookmark argument or from the current record if this argument is
omitted. It is necessary to specify the Rows argument, and if this is more than 0 the current record moves
forward towards end of recordset, and if less than 0 then the current record moves backwards. You can use
this method with rows argument set to 0, to retrieve the current record's underlying data.

MoveFirst, MoveLast, MoveNext, and MovePrevious Methods (DAO): MoveFirst method moves the
current record to the first record. Remember that on opening a recordset, the first record is the current
record. Using the MoveLast method moves the current record to the last record in the recordset. MoveNext
method moves the current record one position forward and MovePrevious moves the current record one
position backward. Note that the MoveFirst, MoveLast, and MovePrevious methods cannot be used on a
Forward-only-type recordset. Syntax:
RecordsetObject.MoveFirst, RecordsetObject.MoveLast, RecordsetObject.MoveNext, RecordsetObje
ct.MovePrevious.

EOF Property (DAO) indicates whether the current record position is after the last record in the set of
records, wherein its value will be TRUE. BOF Property (DAO) indicates whether the current record position
is before the first record in the set of records, wherein its value will be TRUE. Both properties return a
Boolean value and are used to determine if the current record is outside the limits of the Recordset object.
There will be no current record if either the EOF Property or BOF Property is True, and if both properties are
True on opening a recordset it will indicate that there are no records. Opening a Recordset having atleast
one record makes the first record as the current record and in this case both the EOF Property and BOF
Property will be False. Syntax: RecordsetObject .EOF, RecordsetObject .BOF.

Count the number of Records in a Recordset

Use the DAO Recordset.RecordCount Property to: (i) count the number of records which have been
acceessed in a Dynaset-Type or Snapshot-type or Forward-only-type recordset, and after the last record is
accessed (ie. after the recordset is populated) RecordCount indicates the total number of records contained
in the recordset; or (ii) count the total number of records in a table-type Recordset or in a TableDef object,
wherein RecordCount always returns the correct number. To forcibly access the last record in a Dynaset-
Type or Snapshot-type or Forward-only-type recordset, use the Recordset.MoveLast method. Syntax:
RecordsetObject.RecordCount.

Close DAO objects, using the Close Method

You should close an open Recordset by using the DAO Recordset.Close Method, which will free any
associated system resources. Similarly, close an open Workspace object using the Workspace.Close
Method and close an open Database using the Database.Close Method. Closing an object is not enough
to remove it from memory, for which you need to set the object variable to Nothing, after closing the object.

To close a Recordset: RecordsetObject.Close
To destroy the Recordset variable: Set RecordsetObject = Nothing

Example 4b: Add / Edit Records and Enter Data, in Tables.
Refer Image 4b as mentioned in the code.
1. Add new records to a table using the AddNew method;
2. Edit records using the Edit method;
3. Use the Recordset.Bookmark Property to identify the current record;
4. Use Recordset.LastModified Property pointing to the most recent added / modified record.

Image 4b

Sub AccessDAO_AddRecordsEnterData_4b()
'Add new records to a table using the AddNew method;
'edit records using the Edit method;
'Use the Recordset.Bookmark Property to identify the current record;
'Use Recordset.LastModified Property pointing to the most recent added / modified record.

'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.
'Refer Image 4b after running below code which adds & edits records in the database file created in example
4a above (note that the field "SurName" of "SalesManager" Table has not been deleted).

Dim strMyPath As String, strDBName As String, strDB As String, recBookMark 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 = "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

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

'Open a table-type recordset based on a MS Access Table named "SalesManager":

Set recSet = daoDB.OpenRecordset("SalesManager")

'----------------
'add new records to a table, using the AddNew method (of the Recordset object):
With recSet
.AddNew
'you need not enter a value for the auto-incrementing field of "EmployeeId"; the start value is 1. In DAO you
cannot set the Seed of the auto-number, however you can start at a specific value in a new table by entering
the specific value for the first record of an AutoNumber field and subsequent records will increment from this
specific start value.
.Fields("EmployeeId") = 55
.Fields("FirstName") = "Lisa"
.Fields("SurName") = "Randall"
'enter a date/time value between # and #, or within double-quotes:
.Fields("JoinDate") = "08/11/2012"
.Fields("Sales") = "22456"
.Fields("NewJoinee?") = True
'for a hyperlink field, use both # and double-quotes:
.Fields("WebProfile") = "#http://www.google.com#"
'will save only after Update method is run:
.Update

.AddNew
'note that "JoinDate" field is omitted, hence its default value will be entered
.Fields("FirstName") = "Tracy" & " " & "Von"
.Fields("SurName") = "Murray"
.Fields("Sales") = "41098"
.Fields("NewJoinee?") = False
.Fields("WebProfile") = "#http://www.yahoo.com#"
.Update
'second record - save position of current record:
.Bookmark = .LastModified
recBookMark = .Bookmark

.AddNew
.Fields("FirstName") = "John"
.Fields("SurName") = "Mason"
.Fields("JoinDate") = #9/2/2012#
.Fields("Sales") = "31478"
.Fields("NewJoinee?") = True
.Fields("WebProfile") = "#http://www.msn.com#"
.Update

'In DAO, after the Update, the current record will be the record which had focus before the AddNew. Using
the LastModified property (for a DAO recordset - it does not work with an ADO recordset) returns a
Bookmark pointing to the most recent added / modified record. This makes the new record as the current
record.
.Bookmark = .LastModified
End With
'returns "John"
MsgBox recSet.Fields("FirstName")

'edit the new record, using the Edit method (of the Recordset object):
With recSet
'Recordset.Edit Method is valid for a DAO recordset, it does not work with an ADO recordset:
.Edit
.Fields("FirstName") = "Julia"
.Fields("SurName") = "Willis"
'will save only after Update method is run:
.Update
End With

'returns "Julia"
MsgBox recSet.Fields("FirstName")

'return the second record whose position was saved:
recSet.Bookmark = recBookMark
'returns "Tracy Von"
MsgBox recSet.Fields("FirstName")

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

'destroy the variables:

Set daoDB = Nothing
Set recSet = Nothing

End Sub

Example 5a: Open Recordset, Enumerate Recordset, Recordset Properties, Navigate
through Records.
1. OpenRecordset method of the Database object - Open Recordset based on a Table; Open Recordset
based on a SQL statement.
2. OpenRecordset method of the TableDef object - Open Recordset based on a Table.
3. Enumerate the Recordset; List all valid properties of Recordset.
4. Use the MoveFirst and MoveNext methods to navigate through records, together with EOF Property.

Sub AccessDAO_OpenRecordsetMoveThruRecords_5a()
'OPEN RECORDSET; ENUMERATE RECORDSET; RECORDSET PROPERTIES; NAVIGATE THROUGH
RECORDS:
'OpenRecordset method of the Database object - Open Recordset based on a Table; Open Recordset
based on a SQL statement.
'OpenRecordset method of the TableDef object - Open Recordset based on a Table.
'Enumerate the Recordset; List all valid properties of Recordset.
'Use the MoveFirst and MoveNext methods to navigate through records, together with EOF Property.

'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 daoTblDef As DAO.TableDef
Dim daoFld As DAO.Field
Dim daoPrp As DAO.Property
Dim n As Long, i As Long

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

'---------------
'OpenRecordset METHOD OF THE DATABASE OBJECT - OPEN RECORDSET BASED ON A TABLE:

'Open table-type, read-only recordset based on a MS Access Table:

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

'return the first field (name & value) of each record of the Recordset:
'while moving forward within a recordset, use EOF so as not to cross the last record. EOF Property indicates
that the current record position is after the last record in the set of records.
Do While Not recSet.EOF
MsgBox recSet.Fields(0).Name & " - " & recSet.Fields(0).Value
'MoveNext method moves the current record one position forward.
recSet.MoveNext
Loop

recSet.Close
Set recSet = Nothing

'-----
'OpenRecordset METHOD OF THE DATABASE OBJECT - OPEN RECORDSET BASED ON AN SQL
STATEMENT:

'Open dynaset-type recordset based on a SQL statement:

strSQL = "SELECT * FROM SalesManager WHERE EmployeeId > 18"
Set recSet = daoDB.OpenRecordset(strSQL, dbOpenDynaset)

'Enumerate the Recordset - all fields in each record of the Recordset:

fieldsCount = recSet.Fields.count
n=1

'navigate through records in a recordset:

With recSet
'MoveFirst method moves the current record to the first record.
.MoveFirst
Do While Not .EOF
MsgBox "Record No. " & n
For i = 0 To fieldsCount - 1
MsgBox .Fields(i).Name & " - " & .Fields(i).Value
Next i
.MoveNext
n=n+1
Loop
End With

recSet.Close
Set recSet = Nothing

'-----
'OpenRecordset METHOD OF THE TableDef OBJECT - OPEN RECORDSET BASED ON A TABLE:

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

'Open snapshot-type recordset based on a MS Access Table:

Set recSet = daoTblDef.OpenRecordset(dbOpenSnapshot, dbReadOnly)

'List all fields of each record of the Recordset:

Do While Not recSet.EOF
For Each daoFld In recSet.Fields
MsgBox daoFld.Name & " - " & daoFld.Value
Next daoFld
recSet.MoveNext
Loop

'List all valid properties of the Recordset object:

For Each daoPrp In recSet.Properties
'skip invalid values with resume next statement
On Error Resume Next
MsgBox "Property Name: " & daoPrp.Name & ";Property Value: " & daoPrp.Value
Next daoPrp

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

'destroy the variables:

Set daoDB = Nothing
Set recSet = Nothing
Set daoTblDef = Nothing
Set daoFld = Nothing
Set daoPrp = Nothing

End Sub

Example 5b: Count the number of Records in a Recordset - DAO Recordset.RecordCount
Property.
Refer Image 5 as mentioned in the code.

Image 5

Sub AccessDAO_RecordCount_5b()
'Count the number of Records in a Recordset - DAO Recordset.RecordCount Property
'refer Image 5 to view the SalesManager Table in MS Access file "SalesReport.accdb", on which
RecordCount is done in this 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 daoTblDef As DAO.TableDef
Dim qryD As DAO.QueryDef

'assign the database reference to an object variable:

Set daoDB = DBEngine.Workspaces(0).OpenDatabase(strDB)

'---------------
'RecordCount in a TableDef object:
Set daoTblDef = daoDB.TableDefs("SalesManager")
'returns 5, refer Image 5:
MsgBox "RecordsCount in a TableDef Object: " & daoTblDef.RecordCount

'-----
'RecordCount in a Table-type recordset:
Set recSet = daoDB.OpenRecordset("SalesManager", dbOpenTable)
'returns 5, refer Image 5:
MsgBox "RecordsCount in Table-type recordset: " & recSet.RecordCount

recSet.Close
Set recSet = Nothing

'-----
'RecordCount in a Dynaset-Type recordset:
strSQL = "SELECT * FROM SalesManager WHERE EmployeeId > 18"
Set qryD = daoDB.CreateQueryDef("sqlQuery", strSQL)
Set recSet = qryD.OpenRecordset(dbOpenDynaset)
'returns 1, refer Image 5:
MsgBox "RecordsCount in Dynaset-Type recordset BEFORE MoveLast: " & recSet.RecordCount
recSet.MoveLast
'returns 3, refer Image 5:
MsgBox "RecordsCount in Dynaset-Type recordset AFTER MoveLast: " & recSet.RecordCount

daoDB.QueryDefs.Delete ("sqlQuery")

recSet.Close
Set recSet = Nothing

'-----
'RecordCount in a Snapshot-type recordset:
Set recSet = daoDB.OpenRecordset("SalesManager", dbOpenSnapshot)
'returns 1, refer Image 5:
MsgBox "RecordsCount in Snapshot-type recordset BEFORE MoveLast: " & recSet.RecordCount
recSet.MoveLast
'returns 5, refer Image 5:
MsgBox "RecordsCount in Snapshot-type recordset AFTER MoveLast: " & recSet.RecordCount

recSet.Close
Set recSet = Nothing

'-----
'RecordCount in a Forward-only-type recordset:
Set recSet = daoDB.OpenRecordset("SalesManager", dbOpenForwardOnly)
'returns 1, refer Image 5:
MsgBox "RecordsCount in Forward-only-type recordset BEFORE MoveNext: " & recSet.RecordCount
recSet.MoveNext
'returns 2, refer Image 5:
MsgBox "RecordsCount in Forward-only-type recordset AFTER first MoveNext: " & recSet.RecordCount
recSet.MoveNext
'returns 3, refer Image 5:
MsgBox "RecordsCount in Forward-only-type recordset AFTER second MoveNext: " & recSet.RecordCount

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

'destroy the variables:

Set daoDB = Nothing
Set recSet = Nothing
Set daoTblDef = Nothing
Set qryD = Nothing

End Sub

Example 6: Find Method (FindFirst & FindNext) in a Dynaset or Snapshot-type recordset;
Edit / Delete Records.
Refer Images 6a, 6b & 6c as mentioned in the code.
1. Locate records matching specified criteria using the FindFirst and FindNext methods in a dynaset or
snapshot-type recordset;
2. Edit a record using the Edit method;
3. Delete a record using the Delete method.

Image 6a

Image 6b

Image 6c

Sub AccessDAO_FindMethod_EditDeleteRecords_6()
'Locate records matching specified criteria using the FindFirst and FindNext methods in a dynaset or
snapshot-type recordset;
'Edit a record using the Edit method;
'Delete a record using the Delete method;

'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.
'refer Image 6a to view the existing SalesManager Table in MS Access file "SalesReport.accdb".

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

Dim daoDB As DAO.Database
Dim recSet As DAO.Recordset

'Open a dynaset-type recordset based on a MS Access Table named "SalesManager":

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

'----------------
'USE FindFirst AND FindNext METHODS TO LOCATE ALL RECORDS THAT MATCH A SPECIFIED
CRITERIA:

'locate first record with matching criteria:

recSet.FindFirst "SurName LIKE '*A*'"

'if a record with matching criteria is found:
If recSet.NoMatch = False Then
'the NoMatch property is set to True, if no matched record is found:
Do While Not recSet.NoMatch
'return record with matching criteria: returns Murray, Mason & Davis: refer Image 6a:
MsgBox recSet.Fields("SurName")
'locate next record with matching criteria:
recSet.FindNext "SurName LIKE '*A*'"
Loop
Else
MsgBox "No Matching Record Found!"
End If

'--------------------
'LOCATE RECORD TO EDIT, USING THE FindFirst METHOD:
'refer Image 6b after running below code:

'recSet.FindFirst "FirstName = 'Jim'"

'recSet.FindFirst "SurName = ""Murray"""
recSet.FindFirst "EmployeeId=56"

'the NoMatch property is set to True, if no matched record is found:

If Not recSet.NoMatch Then
'modifying a DAO recordset - edit a record in a table, using the Edit method (of the Recordset object):
With recSet
.Edit
.Fields("FirstName") = "James"
.Fields("SurName") = "Bond"
'will save only after Update method is run:
.Update
'Using the LastModified property returns a Bookmark pointing to the most recent added / modified record.
This makes the new record as the current record.
.Bookmark = .LastModified
End With
Else
MsgBox "No Matching Record Found!"
End If

'returns James, refer Image 6b after running this code:

MsgBox recSet.Fields("FirstName")

'--------------------
'FIND RECORD WHICH YOU WANT TO DELETE:
'refer Image 6c after running below code:

recSet.FindFirst "EmployeeId=56"

'modifying a DAO recordset - delete a record in a table, using the Delete method (of the Recordset object):
'record with EmployeeId 56 is deleted, refer Image 6c:
If recSet.NoMatch = False Then
recSet.Delete
Else
MsgBox "Record Not Found"
End If

'no current record after the Delete method is used, hence go to First Record:
recSet.MoveFirst
'returns Tracy, refer Image 6c:
MsgBox recSet.Fields("FirstName")

'go to the Last Record, then go back one record:

With recSet
.MoveLast
.Move -1
'returns Jim, refer Image 6c:
MsgBox recSet.Fields("FirstName")
End With

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

'destroy the variables:

Set daoDB = Nothing
Set recSet = Nothing

End Sub

Example 7: Use the DAO Recordset.Filter Property to Filter Records (applicable to
dynaset–type, snapshot–type, or forward–only–type Recordsets).
Refer Images 7a & 7b, as mentioned in the code.

Image 7a

Image 7b

Sub AccessDAO_RecordsetFilter_7()
'Filter Records: Use the DAO Recordset.Filter Property to determine inclusion of records in a Recordset
opened thereafter. A Filter can be applied to dynaset–type, snapshot–type, or forward–only–type
Recordsets.

'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, recSetF As DAO.Recordset

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

'set values of all records in NewJoinee? field (Boolean) to False:

'refer Image 7a to view the SalesManager Table in MS Access file "SalesReport.accdb", after running below
code.
Set recSet = daoDB.OpenRecordset("SalesManager")
Do While Not recSet.EOF
recSet.Edit
recSet.Fields("NewJoinee?").Value = False
recSet.Update
recSet.MoveNext
Loop

recSet.Close
Set recSet = Nothing

'---------------
'create a filtered Recordset (dynaset–type) with an SQL statement, returning records from SalesManager
Table whose Employee Id is between 15 and 56, returning/sorting them in the order of JoinDate:
Set recSet = daoDB.OpenRecordset("SELECT * FROM SalesManager WHERE EmployeeId BETWEEN 15
AND 56 ORDER BY JoinDate")

'refilter the recordset using the Filter property, returning records whose JoinDate is post "01/01/2011" and
mark them as NewJoinee:
recSet.Filter = "JoinDate BETWEEN #01/01/2011# AND Now"
'create another filtered Recordset (dynaset–type), using the DAO Recordset.OpenRecordset Method:
Set recSetF = recSet.OpenRecordset

'return the EmployeeId & JoinDate fields of each record in the filtered Recordset:
'refer Image 7b to view the SalesManager Table, after running below code.
Do While Not recSetF.EOF
MsgBox recSetF.Fields("EmployeeId") & ", " & recSetF.Fields("JoinDate")
'set values of filtered records in NewJoinee? field to True:
recSetF.Edit
recSetF.Fields("NewJoinee?").Value = True
recSetF.Update
recSetF.MoveNext
Loop

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

'destroy the variables:

Set daoDB = Nothing
Set recSet = Nothing
Set recSetF = Nothing

End Sub
2.2 Create Index/Relationship between Fields, Create and Exceute a Query

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.

A) 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.

Image 8a

Image 8b

Image 8c

Image 8d

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

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

Image 8e

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

'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

B) 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.

Image 10a

Image 10b

Image 10c

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

'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

C) 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.

Image 11

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

'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

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

Image 12

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

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

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

Image 13a

Image 13b

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

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

Image 14

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

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

Image 15

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

'---------------
'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
2.3 Microsoft Access DAO Object Model: Import or Export Data from Access to Excel

A) Using Automation (DAO) to Copy Data from Microsoft Access to Excel:

In this section we show, with the help of practical examples, how to connect to Access Database from Excel
(your host application) using DAO to: (i) import or retrieve data from an Access database to Excel
worksheet; and (ii) Export data from Excel worksheet to an Access Database Table.

Range.CopyFromRecordset Method: This method is commonly used to copy records (valid for both ADO
or DAO Recordset objects) from an Aceess Table to an Excel worksheet. Syntax:
Range.CopyFromRecordset(Data, MaxRows, MaxColumns).

Range is the worksheet range to which the records are copied, starting at its upper-left corner. Data is the
Recordset (ie. set of records) in the Access database to be copied and the current row in the Recordset is
the starting record from where copying begins. MaxRows and MaxColumns refer to the maximum numbers
of rows (ie. records) and fields respectively to be copied and omitting these arguments will indicate that all
rows and fields are copied. Data is mandatory to specify while other arguments of MaxRows and
MaxColumns are optional.

Example 16:

B) Import data from Access Database Table to Excel (your host application).
Refer Images 16a, 16b, 16c, 16d & 16e as mentioned in the code.

Image 16a

Image 16b

Image 16c

Image 16d

Image 16e

Sub AccessDAO_ImportFromAccessToExcel_16()
'Using DAO to Import data from an Access Database Table to an Excel worksheet (your host application).
'refer Image 16a 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 i As Long, n As Long, lLastRow As Long, lFieldCount As Long
Dim daoDB As DAO.Database
Dim recSet As DAO.Recordset

'assign the database reference to an object variable:

Set daoDB = DBEngine.Workspaces(0).OpenDatabase(strDB)

Dim ws As Worksheet
'set the worksheet:
Set ws = ActiveWorkbook.Sheets("Sheet8")

'--------------
'COPY RECORDS FROM ALL FIELDS USING CopyFromRecordset:
'refer Image 16b to view records copied to Excel worksheet

'Open a recordset based on a MS Access Table named "SalesManager":

Set recSet = daoDB.OpenRecordset("SalesManager")

Set rng = ws.Range("A1")

lFieldCount = recSet.Fields.count

For i = 0 To lFieldCount - 1
'copy column names in first row of the worksheet:
rng.Offset(0, i).Value = recSet.Fields(i).Name
Next i

'copy record values starting from second row of the worksheet:
rng.Offset(1, 0).CopyFromRecordset recSet
'to copy 4 rows and 3 columns of the recordset to excel worksheet:
'rng.Offset(1, 0).CopyFromRecordset Data:=recSet, MaxRows:=4, MaxColumns:=3

'select a column range:

Range(ws.Columns(1), ws.Columns(lFieldCount)).AutoFit
'worksheet columns are deleted because this code is only for demo:
Range(ws.Columns(1), ws.Columns(lFieldCount)).Delete

recSet.Close
Set recSet = Nothing

'--------------
'COPY RECORDS FROM SELECTED FIELDS USING CopyFromRecordset:
'refer Image 16c to view records copied to Excel worksheet

'copy records from the selected fields (EmployeeId, FirstName & JoinDate):
strSQL = "SELECT EmployeeId, FirstName, JoinDate FROM SalesManager WHERE EmployeeId > 15"
'Open a dynaset-type recordset based on a SQL statement:
Set recSet = daoDB.OpenRecordset(strSQL, dbOpenDynaset)

Set rng = ws.Range("A1")

lFieldCount = recSet.Fields.count

For i = 0 To lFieldCount - 1
'copy column names in first row of the worksheet:
rng.Offset(0, i).Value = recSet.Fields(i).Name
Next i

'copy record values starting from second row of the worksheet:

rng.Offset(1, 0).CopyFromRecordset recSet

'select a column range:

Range(ws.Columns(1), ws.Columns(lFieldCount)).AutoFit
'worksheet columns are deleted because this code is only for demo:
Range(ws.Columns(1), ws.Columns(lFieldCount)).Delete
recSet.Close
Set recSet = Nothing

'--------------
'COPY RECORDS FROM ALL FIELDS OF A RECORDSET:
'refer Image 16d to view records copied to Excel worksheet

'Open a recordset based on a MS Access Table named "SalesManager":

Set recSet = daoDB.OpenRecordset("SalesManager")

Set rng = ws.Range("A1")

lFieldCount = recSet.Fields.count

For i = 0 To lFieldCount - 1
'copy column names in first row of the worksheet:
rng.Offset(0, i).Value = recSet.Fields(i).Name
recSet.MoveFirst
'copy record values starting from second row of the worksheet:
n=1
Do While Not recSet.EOF
rng.Offset(n, i).Value = recSet.Fields(i).Value
recSet.MoveNext
n=n+1
Loop
Next i

'select column range to AutoFit column width:

Range(ws.Columns(1), ws.Columns(lFieldCount)).AutoFit
'worksheet columns are deleted because this code is only for demo:
Range(ws.Columns(1), ws.Columns(lFieldCount)).Delete

recSet.Close
Set recSet = Nothing

'--------------
'COPY RECORDS FROM SELECTED FIELDS OF A RECORDSET:
'refer Image 16e to view records copied to Excel worksheet

'copy all records from the 3 fields of EmployeeId, SurName, JoinDate:

strSQL = "SELECT EmployeeId, SurName, JoinDate FROM SalesManager"
'Open a dynaset-type recordset based on a SQL statement:
Set recSet = daoDB.OpenRecordset(strSQL, dbOpenDynaset)

Set rng = ws.Range("A1")

lFieldCount = recSet.Fields.count

'select a column range:

Range(ws.Columns(1), ws.Columns(lFieldCount)).AutoFit
'worksheet columns are deleted because this code is only for demo:
Range(ws.Columns(1), ws.Columns(lFieldCount)).Delete

recSet.Close

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

'destroy the variables:

Set daoDB = Nothing
Set recSet = Nothing

End Sub

Example 17:

C) Export data from Excel (your host application) to Access Database Table.
Refer Images 17a, 17b & 17c as mentioned in the code.

Image 17a

Image 17b

Image 17c

Sub AccessDAO_ExportFromExcelToAccess_17()
'Using DAO to Export data from Excel worksheet (your host application) to an Access Database Table.
'refer Image 17a to view the existing SalesManager Table in MS Access file "SalesReport.accdb", indexed
on the "EmployeeId" field.
'refer Image 17b for data in Excel worksheet which is exported to Access Database Table.
'refer Image 17c to view the SalesManager Table in Access file "SalesReport.accdb", after data is exported.

'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 i As Long, n As Long, lLastRow As Long, lFieldCount As Long
Dim daoDB As DAO.Database
Dim recSet As DAO.Recordset

'assign the database reference to an object variable:

Set daoDB = DBEngine.Workspaces(0).OpenDatabase(strDB)

Dim ws As Worksheet
'set the worksheet:
Set ws = ActiveWorkbook.Sheets("Sheet9")

'Open a recordset based on a MS Access Table named "SalesManager":

Set recSet = daoDB.OpenRecordset("SalesManager")

'--------------
'COPY RECORDS FROM THE EXCEL WORKSHEET:
'Note: Columns and their order should be the same in both Excel worksheet and in Access database table

lFieldCount = recSet.Fields.count
'determine last data row in the worksheet:
lLastRow = ws.Cells(Rows.count, "A").End(xlUp).Row
'start copying from second row of worksheet, first row contains field names:
For i = 2 To lLastRow
recSet.AddNew
For n = 0 To lFieldCount - 1
recSet.Fields(n).Value = ws.Cells(i, n + 1)
Next n
recSet.Update
Next i

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

'destroy the variables:

Set daoDB = Nothing
Set recSet = Nothing

End Sub

HCIA-openGauss V1.0Training Materials
No ratings yet
HCIA-openGauss V1.0Training Materials
504 pages
LESSON 8 - Visual Basic Database Programming
100% (4)
LESSON 8 - Visual Basic Database Programming
14 pages
Data Providers
No ratings yet
Data Providers
12 pages
An Introduction To LDAP
No ratings yet
An Introduction To LDAP
13 pages
Visual Foxpro Notes
No ratings yet
Visual Foxpro Notes
27 pages
Ado.net
No ratings yet
Ado.net
38 pages
ISAT Brochure
No ratings yet
ISAT Brochure
2 pages
Session 9 Tables and Arrays
No ratings yet
Session 9 Tables and Arrays
23 pages
Visual Basic Notes
100% (7)
Visual Basic Notes
44 pages
ANSI Escape Sequences
No ratings yet
ANSI Escape Sequences
4 pages
Microsoft Visual Basic 6 (Intrinsic Control)
No ratings yet
Microsoft Visual Basic 6 (Intrinsic Control)
10 pages
Amazon & Cisco - Interview Feedback - Aswin Suresh
No ratings yet
Amazon & Cisco - Interview Feedback - Aswin Suresh
2 pages
Add, Edit, Delete and Run Access Queries With VBA
No ratings yet
Add, Edit, Delete and Run Access Queries With VBA
5 pages
Crystal Report Server PDF
No ratings yet
Crystal Report Server PDF
16 pages
Excel VBA - Message and Input Boxes in Excel, MsgBox Function, InputBox Function, InputBox Method
No ratings yet
Excel VBA - Message and Input Boxes in Excel, MsgBox Function, InputBox Function, InputBox Method
2 pages
Turbo Pascal Version 6.0 Turbo Vision 1990
No ratings yet
Turbo Pascal Version 6.0 Turbo Vision 1990
427 pages
Using Extended MessageBox Class - Visual FoxPro - Using Win32 (WinAPI) Functions
100% (1)
Using Extended MessageBox Class - Visual FoxPro - Using Win32 (WinAPI) Functions
3 pages
Final Year Project - Directory and System Analysis
No ratings yet
Final Year Project - Directory and System Analysis
25 pages
ADO
No ratings yet
ADO
7,264 pages
Running Generic CADD in Windows XP
0% (2)
Running Generic CADD in Windows XP
2 pages
Your Oracle R - : Regular Expressions in An Oracle World
100% (1)
Your Oracle R - : Regular Expressions in An Oracle World
43 pages
FOXPRO (Aj)
No ratings yet
FOXPRO (Aj)
44 pages
C Zentrix Manual
100% (1)
C Zentrix Manual
134 pages
Visual Basic 2015 - The Complete Beginners Guide 1
No ratings yet
Visual Basic 2015 - The Complete Beginners Guide 1
194 pages
Microsoft Access Tips - ADO Programming Code Examples
No ratings yet
Microsoft Access Tips - ADO Programming Code Examples
4 pages
L 3-Python Collections
No ratings yet
L 3-Python Collections
57 pages
Reference Card - Regular Expressions
No ratings yet
Reference Card - Regular Expressions
1 page
ITE7 Chp11
No ratings yet
ITE7 Chp11
182 pages
Intro ANSI X12 Technical Specifications Manual PDF
No ratings yet
Intro ANSI X12 Technical Specifications Manual PDF
31 pages
Vim Cheat Sheet
No ratings yet
Vim Cheat Sheet
2 pages
IBM OS 390 Server User Manual
0% (1)
IBM OS 390 Server User Manual
76 pages
PC Software and Multimedia
No ratings yet
PC Software and Multimedia
46 pages
Chapter 8 - Advanced SQL
No ratings yet
Chapter 8 - Advanced SQL
41 pages
Bosch CertificateManagement TechNote
No ratings yet
Bosch CertificateManagement TechNote
14 pages
TIB Bwce 2.6.2 Migration
0% (1)
TIB Bwce 2.6.2 Migration
96 pages
Revision Excel VBA
No ratings yet
Revision Excel VBA
12 pages
VB Program Examples
No ratings yet
VB Program Examples
65 pages
MS Dos CommandS
No ratings yet
MS Dos CommandS
6 pages
Guardian UsersGuide
No ratings yet
Guardian UsersGuide
460 pages
Using The Win32 API - Visual Basic 6 (VB6)
No ratings yet
Using The Win32 API - Visual Basic 6 (VB6)
10 pages
vDOS Printing
No ratings yet
vDOS Printing
3 pages
Regular Expressions: Software Team
No ratings yet
Regular Expressions: Software Team
22 pages
Computer Basics: CS 1 Introduction To Computers and Computer Technology Rick Graziani Fall 2007
No ratings yet
Computer Basics: CS 1 Introduction To Computers and Computer Technology Rick Graziani Fall 2007
58 pages
LibreOffice BASIC Constants
No ratings yet
LibreOffice BASIC Constants
1 page
SC21-9020-4 System 36 System Reference Oct86
No ratings yet
SC21-9020-4 System 36 System Reference Oct86
1,174 pages
LAB REPORT ON MS Dos Kritik
No ratings yet
LAB REPORT ON MS Dos Kritik
20 pages
File Handling in
No ratings yet
File Handling in
10 pages
Day 1 - Notes
No ratings yet
Day 1 - Notes
20 pages
Ms Access Exercise - 010837
No ratings yet
Ms Access Exercise - 010837
3 pages
Report Microsoft Dynamics AX
No ratings yet
Report Microsoft Dynamics AX
56 pages
Inside COM
No ratings yet
Inside COM
5 pages
Practical File OS
No ratings yet
Practical File OS
59 pages
Window 8
No ratings yet
Window 8
203 pages
Writing Windows Device Drivers Yashavant P 33551368 PDF
No ratings yet
Writing Windows Device Drivers Yashavant P 33551368 PDF
2 pages
Unit - 5 - Chapter 3 - Creating, Updating, and Deleting Documents in MongoDB
No ratings yet
Unit - 5 - Chapter 3 - Creating, Updating, and Deleting Documents in MongoDB
60 pages
Sql-Data Types
No ratings yet
Sql-Data Types
15 pages
Advanced GitLab CI/CD Pipelines: An In-Depth Guide for Continuous Integration and Deployment
From Everand
Advanced GitLab CI/CD Pipelines: An In-Depth Guide for Continuous Integration and Deployment
Adam Jones
No ratings yet
Beginning Microsoft SQL Server 2012 Programming
From Everand
Beginning Microsoft SQL Server 2012 Programming
Paul Atkinson
1/5 (1)
Oracle VM Manager 2.1.2
From Everand
Oracle VM Manager 2.1.2
Tarry Singh
No ratings yet
DB2 Exam C2090-320 Practice Questions
From Everand
DB2 Exam C2090-320 Practice Questions
Robert Wingate
No ratings yet
DB2 9.7 for Linux, UNIX, and Windows Database Administration: Certification Study Notes
From Everand
DB2 9.7 for Linux, UNIX, and Windows Database Administration: Certification Study Notes
Roger E. Sanders
5/5 (1)
Table 3
No ratings yet
Table 3
49 pages
Built-In Constants: Vbnullchar
No ratings yet
Built-In Constants: Vbnullchar
2 pages
Table 1
No ratings yet
Table 1
91 pages
Interrupt A Macro: Command Button Dim
No ratings yet
Interrupt A Macro: Command Button Dim
1 page
Use VBA To Compare Two Lists and Show The Values in The First Column That Are Missing From The Second Column
No ratings yet
Use VBA To Compare Two Lists and Show The Values in The First Column That Are Missing From The Second Column
4 pages
This Tutorial Covers:: VBA Coding Language
No ratings yet
This Tutorial Covers:: VBA Coding Language
10 pages
Obtaining A List All Window Handles
No ratings yet
Obtaining A List All Window Handles
5 pages
Read A Text File With VBA in Excel, and Write The Text To A Spreadsheet
No ratings yet
Read A Text File With VBA in Excel, and Write The Text To A Spreadsheet
4 pages
Last Row Column
No ratings yet
Last Row Column
1 page
Open A Text File in Excel VBA
No ratings yet
Open A Text File in Excel VBA
5 pages
Sub As String: I) VBA Code To List All Files Within A Folder Only
No ratings yet
Sub As String: I) VBA Code To List All Files Within A Folder Only
3 pages
Run VBA Macros When A Spreadsheet Opens or Closes
No ratings yet
Run VBA Macros When A Spreadsheet Opens or Closes
2 pages
Introduction To Collections in Excel VBA
No ratings yet
Introduction To Collections in Excel VBA
2 pages
Key Code: (Backspace) (BS) (Break) (Capslock) (Clear)
No ratings yet
Key Code: (Backspace) (BS) (Break) (Capslock) (Clear)
7 pages
Get All Child Windows
No ratings yet
Get All Child Windows
2 pages
Read Data From A Closed Excel File
No ratings yet
Read Data From A Closed Excel File
3 pages
Merge Multiple Excel Sheets
No ratings yet
Merge Multiple Excel Sheets
2 pages
The VBA GetAttr Function
No ratings yet
The VBA GetAttr Function
3 pages
How To Use The SPACE Function (VBA) : Macro Code
No ratings yet
How To Use The SPACE Function (VBA) : Macro Code
3 pages
Protect Your Source Code From Decompiling or Reverse Engineering
No ratings yet
Protect Your Source Code From Decompiling or Reverse Engineering
4 pages
List Files From Folders and Subfolders
No ratings yet
List Files From Folders and Subfolders
4 pages
Find and Replace
No ratings yet
Find and Replace
5 pages
Excel VBA Custom Classes & Objects, Class Modules, Custom Events
No ratings yet
Excel VBA Custom Classes & Objects, Class Modules, Custom Events
19 pages
Read Data From Text File
No ratings yet
Read Data From Text File
2 pages
Intro To Vba Fso Copyfile: Vba Copy A File Macro
No ratings yet
Intro To Vba Fso Copyfile: Vba Copy A File Macro
6 pages
Excel VBA Split Function
No ratings yet
Excel VBA Split Function
11 pages
Excel VBA Automate Microsoft Word From Excel
No ratings yet
Excel VBA Automate Microsoft Word From Excel
14 pages
Exporting and Import Text With Excel
No ratings yet
Exporting and Import Text With Excel
6 pages
How To Use The CountFilesInFolder Macro
No ratings yet
How To Use The CountFilesInFolder Macro
2 pages
Folders and File Handling in Excel VBA
No ratings yet
Folders and File Handling in Excel VBA
5 pages
Unit 1 Introduction To Big Data and Hadoop
No ratings yet
Unit 1 Introduction To Big Data and Hadoop
100 pages
(CT208H) Ch2 - Oracle Architecture
No ratings yet
(CT208H) Ch2 - Oracle Architecture
60 pages
VTP Asp - Net (16-30)
No ratings yet
VTP Asp - Net (16-30)
65 pages
Splunk Enterprise Search Reference 6.4.1
No ratings yet
Splunk Enterprise Search Reference 6.4.1
3 pages
Data_Lakehouse_Architecture
No ratings yet
Data_Lakehouse_Architecture
11 pages
Chapter 6 - Simple Queries in SQL
No ratings yet
Chapter 6 - Simple Queries in SQL
164 pages
A Sample UNIVERSITY EER Schema, Design Choices, and Formal Definitions
No ratings yet
A Sample UNIVERSITY EER Schema, Design Choices, and Formal Definitions
5 pages
Top 100 SQL Interview Q&A_ Flashcards _ Quizlet
No ratings yet
Top 100 SQL Interview Q&A_ Flashcards _ Quizlet
11 pages
Hive Partitions
No ratings yet
Hive Partitions
5 pages
DBS_CourseOutline
No ratings yet
DBS_CourseOutline
3 pages
Relational Model
No ratings yet
Relational Model
30 pages
DATA WAREHOUSING
No ratings yet
DATA WAREHOUSING
23 pages
Birla Institute of Technology & Science, Pilani Pilani Campus SECOND SEMESTER 2015 - 2016 Database Systems (Cs F212/ Is F243) Mid Semester Exam
No ratings yet
Birla Institute of Technology & Science, Pilani Pilani Campus SECOND SEMESTER 2015 - 2016 Database Systems (Cs F212/ Is F243) Mid Semester Exam
3 pages
Next-Generation Database Interfaces: A Survey of LLM-based Text-to-SQL
No ratings yet
Next-Generation Database Interfaces: A Survey of LLM-based Text-to-SQL
17 pages
Resume Nitin Saini
No ratings yet
Resume Nitin Saini
3 pages
Itcerttest: It Exam Study Guide / Simulations
No ratings yet
Itcerttest: It Exam Study Guide / Simulations
7 pages
Package in Java
No ratings yet
Package in Java
22 pages
Calculus Source Quiz 2212 PDF
No ratings yet
Calculus Source Quiz 2212 PDF
60 pages
Django Model View Template (MVT) Overview - Onlinetutorialspoint
No ratings yet
Django Model View Template (MVT) Overview - Onlinetutorialspoint
6 pages
Database Denormalization Example-05032020-061547am
No ratings yet
Database Denormalization Example-05032020-061547am
4 pages
OxyClassifieds v7 Installation Manual
No ratings yet
OxyClassifieds v7 Installation Manual
9 pages
Database Management Systems-Chapter 1: Dr. M. Brindha Assistant Professor Department of CSE NIT, Trichy-15
No ratings yet
Database Management Systems-Chapter 1: Dr. M. Brindha Assistant Professor Department of CSE NIT, Trichy-15
26 pages
Data Types
No ratings yet
Data Types
3 pages
000-oracle-cloud-data-management-foundations-associate-2023-V5
No ratings yet
000-oracle-cloud-data-management-foundations-associate-2023-V5
35 pages
2014 Wassce May-june – Elective Ict [Paper 3]
100% (1)
2014 Wassce May-june – Elective Ict [Paper 3]
3 pages
Red Hat JBoss Enterprise Application Platform-7.2-Getting Started With JBoss EAP For OpenShift Online-En-US
No ratings yet
Red Hat JBoss Enterprise Application Platform-7.2-Getting Started With JBoss EAP For OpenShift Online-En-US
53 pages
DMDW 6
No ratings yet
DMDW 6
41 pages
Rdbmsexp 6
No ratings yet
Rdbmsexp 6
6 pages
Salesforce Developer Codes: 1. Apex Class Basic Examples (Addition Example)
100% (1)
Salesforce Developer Codes: 1. Apex Class Basic Examples (Addition Example)
47 pages

Uploaded by

Uploaded by

TABLE OF CONTENTS:

'set the string variable to the Database:

'instantiate an ADO object:

'set the string variable to the Database:

'instantiate an ADO object

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

'set the string variable to the Database:

'instantiate an ADO object:

'Set the ADO Recordset object:

'Opening the table named SalesManager:

adoRecSet.Open Source:=strTable, ActiveConnection:=connDB, CursorType:=adOpenStatic,

'count number of fields - returns 7 fields:

'Use the Type property to return the data type of a Field.

'Delete the new record:

'destroy the object variables

A) Use ADO to Execute SQL statements to Manage your Database

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

'instantiate an ADO object using Dim with the New keyword:

'delete records in the SalesManager Table:

'select a specific field (FirstName), basis text reference:

'select multiple fields, basis numeric reference:

'select all fields, basis text reference:

'destroy the variables

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

'instantiate an ADO object using Dim with the New keyword:

'select all fields, basis JoinDate criteria:

'destroy the variables

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

'instantiate an ADO object using Dim with the New keyword:

'to get names of all fields of a specified table ("SalesManager"):

'destroy the variables

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

'instantiate an ADO object using Dim with the New keyword:

connDB.Execute "CREATE TABLE SalesManager(EmployeeId LONG, FirstName Text(40), Surname

'destroy the variables

'instantiate an ADO object using Dim with the New keyword:

'open Recordset ie. SalesManager Table:

Do While Not adoRecSet.EOF

'2 records are found

'destroy the variables

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

'instantiate an ADO object using Dim with the New keyword:

'Place the filter BEFORE opening the recordset.

'adoRecSet.Filter = "FirstName = 'Jim' OR EmployeeID > 35"

adoRecSet.Open Source:="SalesManager", ActiveConnection:=connDB, CursorType:=adOpenKeyset,

MsgBox "Records found: " & i

'destroy the variables

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

'instantiate an ADO object using Dim with the New keyword:

'delete the Telephone column in the SalesManager Table:

'delete the City & ZipCode columns in the SalesManager Table:

'destroy the variables

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

'instantiate an ADO object using Dim with the New keyword:

'Set the ADO Recordset object:

'Opening the table named SalesManager:

Set rng = ws.Range("A1")

'copy record values starting from second row of the worksheet:

'select a column range:

'select a column range:

adoRecSet.Open Source:=strTable, ActiveConnection:=connDB, CursorType:=adOpenStatic,

Set rng = ws.Range("A1")

'select column range to AutoFit column width:

'copy all records from the 3 fields of EmployeeId, SurName, JoinDate:

Set rng = ws.Range("A1")

'select a column range:

'destroy the variables

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

'instantiate an ADO object using Dim with the New keyword:

'Set the ADO Recordset object:

'Opening the table named SalesManager:

'destroy the variables

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

'destroy the object variable:

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

'set name of the new table you are going to add:

'save the new table by appending to the catalog tables collection:

'set an ADO connection to database: