ADO or DAO? (2007)

Topic

New Reply

If I create a DB in Access 2007 is it a DAO or ADO database? Not even sure if I am using these terms correctly.

Reply | Quote

Replies

An Access database is neither a “DAO database” nor an “ADO database”. DAO and ADO are both object models that let you work with Access databases (and others) in VBA.
Which one of these two is available to you as programmer is determined by which references have been set in Tools | Databases in the Visual Basic Editor. You can set a reference to DAO (Microsoft DAO 3.6 Object Library) or to ADO (Microsoft ActiveX Data Objects m.n Library), or to both.
In an Access 2000 database, there is a reference to ADO by default, and in an Access 2002/2003 or 2007 database, there is a reference to DAO by default. But you can change that to suit your needs/preferences.

Reply | Quote

Okay. If I planned to later convert a DB application to some SQL DB (such as MySQL), would it be good to set the reference to ADO? Or does it really matter?

Reply | Quote

It doesn’t matter. Both ADO and DAO can work with external databases such as SQL Server or MySQL through the ODBC driver for that database. And if you use linked tables in an Access frontend, it’s as if you’re working with local Access tables.

Reply | Quote

Thanks for information… Somehow I think I did not ask the right question since I have no better understanding now than before except for usage of these terms in a sentence.

As I read various articles and tips about Access, there is frequent reference to ADO or DAO such that it makes me think there must be some importance to this… hence the question. Now, as I understand it, there is nothing to be concerned about.. at least not as a casual and amateur developer.

Reply | Quote

If you don’t write VBA code, you don’t need to concern yourself with ADO or DAO.

If you write VBA code that manipulates recordsets, you will have to use ADO or DAO. Either will work fine, provided that you have set a reference to the relevant object library in Tools | Options in the Visual Basic Editor. And although you can set a reference to both, you should not mix the two object models in the same piece of code – it leads to confusion.

Both object models have objects of type Recordset, for example, and objects of type Field. For this reason, it is a good idea to be explicit when declaring variables, for example:

Dim rst As ADODB.Recordset
Dim fld As ADODB.Field

when you’re using ADO, or

Dim rst As DAO.Recordset
Dim fld As DAO.Field

when you’re using DAO. That way, there can’t be any ambiguity.

Reply | Quote

Got it. Thanks for the explanation.

Reply | Quote