Chapter 13: Accessing Data with VBA Code
507
Not every property associated with a Field object is valid at a particular time. Some properties are
only set after the field contains data, or when the field is involved in an index. For example, the
Value property of the Field object cannot be referenced directly from code. Instead, you set or
get the value of a field only through the field’s membership in a Recordset object. The On Error
Resume Next statement allows this code to run, in spite of invalid properties. The errors that may
occur when invalid properties are referenced by this code are ignored.Given the obvious similarities between ADO and DAO, you might be confused as to which syntax to
choose for new Access applications. (I’m assuming that existing Access application already specify
either ADO or DAO.) After all, Microsoft continues to support ADO and DAO, and it has introduced
ACEDAO, an Access 2007/2010-specific version of DAO. So, which is best for your applications?As with everything else in database development, the answer depends on your specific situation. In
spite of its more complex object model, DAO is somewhat faster and easier for certain tasks. Because
DAO doesn’t require a connection string, DAO code tends to be simple and easy to write. Very often a
successful DAO procedure can be written strictly from memory, without having to look up the syntax
in a book or online. DAO is also somewhat faster than ADO, especially when working with small data
sets.ADO, on the other hand, excels when connecting to external databases, whether the data source is
another Access application or a SQL Server database. Depending on the referenced provider, ADO
connections include properties that tell you the connection’s state (open, connecting, disconnected,
and so on). This information can be extremely valuable in some situations.Tip
There is no problem including both ADO and DAO code in the same application — but you cannot use
DAO and ACEDAO in the same project. Just be sure to prefix object references with DAO (or ACEDAO),
or ADODB, depending on the object syntax you’re using.In many cases, the decision to use DAO or ADO depends on the example code you might find to use
in your application. There are, literally, thousands of VBA code examples available using either DAO
or ADO. From a purely technical standpoint, there is no compelling reason to use either DAO or ADO.
The only exception is when working with SQL Server data. Because Microsoft provides a native ADO
provider for SQL Server, ADO is clearly the better choice when working with SQL Server. Once a con-
nection is established to the SQL Server database, the ADO Command object is the ideal way to invoke
a stored procedure or run an ad hoc query against SQL Server tables. In a SQL Server context, ADO will
almost always be faster and more efficient than DAO because DAO’s access to SQL Server is limited to
using an OLEDB data source pointing to the SQL Server database.Cross-Reference
OLEDB is covered in Chapter 37.In case you’re wondering, ACEDAO is the default data access library in Access 2010. Every new Access
2010 database is created with a reference set to the Microsoft Office 14.0 Access Database Engine
Object Library (ACEDAO) already in place. If you want to use ADO in your Access 2010 applications,
you’ll have to manually add a reference to the Microsoft ActiveX Data Objects 6.0 Library.Deciding between ADO and DAO