Integrating Excel and Access

Choosing Between ADO and DAO

In Chapter 1, I gave a brief description of ADO (including ADOX) and DAO. In this chapter, the biggest differences between the two methods of accessing data come with the use of queries. When you develop an Access Database, you will often have many different types of queries. For example, you might have one query that simply retrieves data (Select Query) or one that appends data from one table into another (Append Query).

When you are dealing with DAO, each type of query is a QueryDef object. Once you have a DAO database connection, you can simply cycle through the QueryDefs collection with a For Each...Next loop and get information about each query; you can then make changes to the queries, if necessary. The QueryDefs collection contains every query in the Access database. When using DAO, you do not need to know what type of query you are dealing with to take actions on it.

This is not as easy a task when using ADO and ADOX. ADOX categorizes queries similarly to the way SQL Server does. Specifically, ADOX considers action queries (Append Queries, Make Table Queries, etc.) to be procedures and select queries to be views. To loop through all of the queries in an Access Database using ADOX, you would cycle through the Procedures and Views collections. The other major difference between ADOX and DAO is that in ADOX, the Views are also part of the Tables collection, while in DAO they are not. The collection of tables in DAO is called the TableDefs collection. ...