Menu

DAO/ADO Objects

Other links

DAO/ADO Objects

There are several ways to connect to a database in VBA whether it is a relational database (RDBMS) or a flat-file database (like Excel).

Also, where and what type of database application/server it is will pretty much determine which is considered best for the job.

The two I'm going to mention in the article (DAO and ADO) is considered the more popular techniques deployed but for your reference you may want to investigate the older RDO (Remote Data Object) which has been really replaced with DAO, OLE-DB and ODBC to help establish which would be best for your solution.

DAO

DAO stands for Data Access Objects and is one of the technologies to allow communications to external applications (mainly databases).

In order to use this feature, users will need to add the DAO library to the project.

Choose from the Tools menu and select Reference…

This library will than allow objects to be created to interrogate a database, tables, fields and return information to populate a spreadsheet. This will also allow users to add, edit, update and delete data to an external file without the need to open the associated application.

An advanced feature of this library will even allow users to create, modify and delete structures of a database whether a table, query, stored procedure or fields.

Using the control flow techniques as discussed in this manual, the user can fully control how data should be handled - opening the potential power of VBA.

Note: In order to test this section, users will need an Access database and will need to familiarise themselves with the database. It is not essential to have Microsoft Access loaded as this reference uses the backdoor but it will be difficult to check the database without it!

Example - Connecting to a database:

Sub ConnectDB()
    Dim db As Database
    Dim rst As Recordset

    Set db = OpenDatabase("C:\db1.mdb")
    Set rst = db.OpenRecordset("Customers")

    'displays the first record and first field
    MsgBox rst.Fields(0)

    'close the objects
    rst.Close
    db.Close

    'destroy the variables
    Set rst = Nothing
    Set db = Nothing
End Sub

The above example opens an Access database (db1.mdb) in memory and sets a reference to one of its known tables using the ‘OpenRecordset’ method. It then displays the first row and first field of the table:

The property Fields of the RecordSet object is a collection (or array) that is held in memory and by changing the element number, users can return a different field (column of the table).

rst.Fields(1)

The above illustration would show the customer’s name instead of the ID number.

A good discipline is to close and set an object to Nothing that releases memory, hence the last four lines of code.

Example - Working with records:

'Opens a connection to the Customers table
'and populates a blank worksheet.
Sub PopulateCustomers()
     Dim db As Database
     Dim rst As Recordset
     Dim i As Long

     Set db = OpenDatabase("C:\db1.mdb")
     Set rst = db.OpenRecordset("Customers")

     'look through each record and populate
     'ID, Name and Country into a worksheet.
     Do Until rst.EOF
        ActiveCell.Offset(i, 0).Value = rst.Fields(0)
        ActiveCell.Offset(i, 1).Value = rst.Fields(1)
        ActiveCell.Offset(i, 2).Value = rst.Fields(8)
        i = i + 1
        rst.MoveNext
     Loop

    'close the objects
    rst.Close
    db.Close

    'destroy the variables
    Set rst = Nothing
    Set db = Nothing
End Sub

The above example once again opens the table ‘Customers’. Using a conditional loop at which point the property EOF (End Of File) returns True or False every time the record changes using the MoveNext method, three columns in the worksheet from the starting active cell are populated by three different field indexes.

Even though the above example used rst.Fields(8) to determine the ninth column, it may be fair to say that users may not know the position number of the field but instead know its fieldname. In this case, users can refer to the name of the field as a string argument.

rst.Fields(“Post Code”).

Note: Be careful to include a command to increment the collection (MoveNext method) otherwise this would cause the procedure to loop infinitely or run out of worksheet rows firing an error. Save your work first before testing the above.

When interrogating a table in a database, it may be required to test to see if the table actually has records in it before iterating through each record.

Wrap an If statement around the loop to test this out:

If Not rst.EOF And Not rst.BOF Then

[code here]...

End If

If this returns True then at least one record is present. If both EOF and BOF are True, it means the cursor is positioned at the beginning and at the end of the record set (which means it’s empty).

The Not keyword inverses the returning value which means that in the above example, both must be False if this is to run any code in between the statement.

Example - Editing records in a database:

Not only can users populate data from an external database, but also it is possible to change data in an external database.

'Opens a connection to the table Customers
'and adds a new record and then updates and closes
Sub AddNewRecord()
    Dim db As Database
    Dim rst As Recordset

    Set db = OpenDatabase("C:\db1.mdb")
    Set rst = db.OpenRecordset("Customers")

    rst.AddNew
    rst.Fields("Customer ID") = "XYZ"
    rst.Fields("Company Name") = "XYZ Foods Ltd"
    rst.Fields("Post Code") = "NW1 8PY"
    rst.Update

    'close the objects
    rst.Close
    db.Close

    'destroy the variables
    Set rst = Nothing
    Set db = Nothing
End Sub

The above example once again opens a connection to the ‘Customer’ table and then uses two methods to add and update the new record.

The Add method triggers the mode to add the record but does not save it to the table until you call the Update method.

Note: Be careful to consider the table’s structure and database rules that are often implemented such as primary keys and foreign indexes. The above example would fail if the customer id field was a unique primary key and the table already had such a reference.

Further coding would be required to test to see if the record number existed, before adding and updating the record.

To edit a record, users must first locate the record (if it can be found) and then use the Edit method.

rst.Edit

rst.Fields("Customer ID") = "XYZ"

rst.Fields("Company Name") = "XYZ Foods Ltd"

rst.Fields("Post Code") = "W12 6RF"

rst.Update

Example - Creating a table:

'Opens a connection to the table Customers
'and adds a new record and then updates and closes
Sub CreateTable()
    Dim db As Database
    Dim rst As Recordset
    Dim tbl As TableDef

    Set db = OpenDatabase("C:\db1.mdb")
    Set tbl = db.CreateTableDef("Contact Log")

    With tbl
        .Fields.Append .CreateField("Log ID", dbInteger)
        .Fields.Append .CreateField("Date", dbDate)
        .Fields.Append .CreateField("Caller", dbText)
        .Fields.Append .CreateField("Comment", dbText)
        .Fields.Append .CreateField("Completed", dbBoolean)
        db.TableDefs.Append tbl
    End With

    Set rst = db.OpenRecordset("Contact Log")
    rst.AddNew
    rst.Fields("Log ID") = 1
    rst.Fields("Date") = Date
    rst.Fields("Caller") = "Ben Beitler"
    rst.Fields("Comment") = "Arranged VBA training next week."
    rst.Fields("Completed") = True
    rst.Update

    'close the objects
    rst.Close
    db.Close

    'destroy the variables
    Set rst = Nothing
    Set tbl = Nothing
    Set db = Nothing
End Sub

The above example will create a new table ‘Contact Log’, create new fields and then bind it to the new table using db.TableDefs.Append tbl. Next it will add a single record using the correct data to match the data types as defined in the table.

This procedure will only run once and then cause an error if executed again. This is due to the fact this database cannot contain duplicate named tables.

Therefore, users need to add error-handling procedures as well as testing to see if the table exists.

To delete a table along with its records, use db.TableDefs.Delete "Contact Log".

Again an error will be fired if the system cannot locate the table (misspelling or already deleted).

There are many properties and methods of DAO which are not covered in this guide. This library allows many ways to produce the same effect which include writing SQL (structured query language).

ADO

ADO stands for ActiveX Data Objects is an alternative method of connecting to a database.

In order to use this feature, users will need to add the ADO library to the project.

Choose from the Tools menu and select Reference…

Note: You may have noticed that there several versions of ActiveX Data Objects in the illustration above. Generally, you should choose the latest version but depending on which version of Excel (or more accurately Windows operating system) try and pick the best fit version. For example 2.8 is for those running on Windows XP where as users would choose 6.0 for Windows Vista.

An advanced feature of this library (ADOX) will even allow users to create, modify and delete structures of a database whether a table, query, stored procedure or fields.

Using the control flow techniques as discussed in this manual, the user can fully control how data should be handled - opening the potential power of VBA.

Example - Connecting to a database:

Sub ConnectExcelDB()
    Dim cn as ADODB.Connection

    Set cn = New ADODB.Connection

    With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\pivot data.xls;" & _
             "Extended Properties=Excel 8.0;"
.Open
    End With
End Sub

The above example creates a connection and open the workbook 'pivot data'. It requires the Extended Properties=Excel 8.0 argument (which users need to adjust for their own version of Excel).

Sub ConnectAccessDB()
    Dim cn As ADODB.Connection

    Set cn = New ADODB.Connection

    With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\db2.mdb;"
.Open
    End With
End Sub

The above example connects to an Access database (db2).

There other ways to connect as well as setting optional arguments which control the method of connection (using ODBC or DSN-Less etc) which is beyond this article.

Example - Reading from a database:

Using an Access database, users can connect to table, query or write SQL (structured query language) into the calling object.

Sub ReadingData()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cn = New ADODB.Connection

    With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\db2.mdb;"
.Open
    End With

    Set rs = New ADODB.Recordset
    'opens a connection to a table called customers.
    rs.Open "Customers", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    'show the second fields value, first record (column 2, row 1).
    Debug.Print rs.Fields(1).Value 'second columns - starts at 0

    rs.Close
    cn.Close

    Set rs = Nothing
    Set cn = Nothing
End Sub

The above example creates a connection. It then creates another new object (rs) which the recordset of a table, query or SQL source and opens it too.

Now you have a collection of data (all records in that file). Using a property (Fields), you can pass either an index or string name into it to refer to any field in that source file and return one of several values (in this case the data value).

Make sure you close and dispose of the objects when finished (and in the correct order) though it will clear and dispose of all objects when the procedure comes to an end - just good habits of programming!

To refer to an actual field instead of an index, use Fields("Customer Name").

It is good practice to narrow down the recordset to the smallest amount of data in memory which the above example fails to do (all records). Instead, consider passing a query or SQL statement instead:

rs.Open "Select * From Customers Where Country='UK';", cn .....

There are optional arguments which also help performance and restrictions to an open connection which I've used in my example above adOpenKeyset, adLockOptimistic, adCmdTable and will require further investigation to help establish the rule (refer to VBA help for more information).

Example - Writing to a database:

Create a connection and open a recordset (table) to add, edit and delete records.

Sub EditingData()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cn = New ADODB.Connection

    With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\db2.mdb;"
.Open
    End With

    Set rs = New ADODB.Recordset
    rs.Open "Customers", cn, adOpenDynamic, adLockOptimistic
    'edit the second field, first record's value.
    rs.Fields(1).Value = "Always Open QM"
    rs.Update 'save the changes.

    rs.Close
    cn.Close

    Set rs = Nothing
    Set cn = Nothing
End Sub

Using the rs.Update property enforces any changes to be saved and written to the database.

If you wish add a new record, you can use rs.AddNew method but it will still need to use rs.Update to save the changes.

Example:

Sub NewRecord()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cn = New ADODB.Connection

    With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\db2.mdb;"
.Open
    End With

    Set rs = New ADODB.Recordset

    rs.Open "Customers", cn, adOpenDynamic, adLockOptimistic
    'adding a new record.
    rs.AddNew
    rs.Fields(0).Value = "XYZ" 'customer ID field
    rs.Fields(1).Value = "XYZ Limited" 'customer name field
    rs.Fields(5).Value = "London" 'city field
    rs.Fields(8).Value = "UK" 'country field
    rs.Update 'save the changes.

    rs.Close
    cn.Close

    Set rs = Nothing
    Set cn = Nothing
End Sub

The above example populates new values to four fields and then saves the changes. Make sure any record being added satisfies the rules of the data source which is being used to store the data which will include indexing (which is generally a mandatory field).

Other useful methods include EOF (end of file) and BOF (beginning of file) which allows you to iterate through records using loops. - look at the help for more information.

There is much, much more on this subject (I've not done this justice) and users should now be confident to go off and investigate further using various other resources (books and the web!).

Finally, which one to use DAO or ADO?

There are many arguments which one should use but as a general rule if you are going to communicate with Microsoft 'Jet' engine (Access, SQL etc) then using DAO is quicker and easier to master.

Consider using ADO for across platform applications typically over the web (server) and non-Microsoft Window environments which have the capability to create DSN-less connections. It also handles multiple databases at the same time and is considered the standard with other programming languages.

Both have similar members (methods and properties) and can conflict if both are being referenced in the same module.

Next Topic: Input/Output Files

Want to teach yourself Access? Free online guide at About Access Databases

Tip: Decide on which library to use and stock with it. Don't mix the two together (ADO and DAO) though it can still work but the order of referencing will matter.

DAO/ADO Objects

DAO

ADO

Tip: Decide on which library to use and stock with it. Don't mix the two together (ADO and DAO) though it can still work but the order of referencing will matter.

VBA Keywords: DAO, ADODB, Connection, Recordset, Open, Update, EditMode, AddNew, Fields, Set, New & With...End With