3.2. 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. A single table is a TableDef.

The difference between accessing a select query as a view and as a table is that if you access a query in the Views collection, you have access to the Command and CommandText. This is what gives you the SQL of the View. When you access a View as a Table, you do not have access to the Command or CommandText.

I have included two samples below that show you how to loop through the queries and tables in an Access Database. Example 3-3 uses ADO, while Example 3-4 uses DAO. When I need to make changes to queries, I tend to use DAO when working with Access, and ADOX when working with SQL Server. The reason why I tend to use DAO when working with Access queries is that on many occasions when I am working with queries and I am writing SQL on the fly, the query may change from a select query to an action query. I find it is easier to just use the QueryDefs collection, as opposed to determining whether the query is now in the Views or Procedures collection. But, as you will see below, you are not limited to doing that. Also note that in the ADO example I am using the GetCn procedure from above. I am just ignoring the recordset that is returned.

Example 3-3. Using ADO and ADOX to loop through queries and tables

 Public Sub getinfo( ) Dim adoconn As ADODB.Connection Dim adors As ADODB.Recordset Dim sql As String Dim filenm As String Dim adocat As adox.Catalog Dim adovw As adox.View Dim adozz As adox.Procedure Dim adotbl As adox.Table Dim x As Integer Dim xlsht As Excel.Worksheet Set xlsht = Sheets("Sheet1") sql = "Select * from Table1" filenm = "C:\Data\sampledb.mdb" Call GetCn(adoconn, adors, sql, filenm, "", "") Set adocat = New adox.Catalog adocat.ActiveConnection = adoconn x = 2 xlsht.Cells(1, 1).Value = "Views" For Each adovw In adocat.Views   xlsht.Cells(x, 1).Value = adovw.Name   x = x + 1 Next x = 2 xlsht.Cells(1, 2).Value = "Procedures" For Each adozz In adocat.Procedures   xlsht.Cells(x, 2).Value = adozz.Name   x = x + 1 Next x = 2 xlsht.Cells(1, 3).Value = "Tables" For Each adotbl In adocat.Tables   xlsht.Cells(x, 3).Value = adotbl.Name   x = x + 1 Next Set adocat = Nothing Set adozz = Nothing Set adovw = Nothing Set adotbl = Nothing adors.Close adoconn.Close Set adors = Nothing Set adoconn = Nothing Set xlsht = Nothing End Sub 

Example 3-4. Using DAO to loop through queries and tables

 Public Sub getDAOinfo( ) Dim wrk As DAO.Workspace Dim db As DAO.Database Dim qry As DAO.QueryDef Dim tbl As DAO.TableDef Dim x As Integer Dim xlsht As Excel.Worksheet Set wrk = DAO.CreateWorkspace("myworkspace", "admin", "") Set db = wrk.OpenDatabase("C:\Data\sampledb.mdb") Set xlsht = Sheets("Sheet2") xlsht.Cells(1, 1).Value = "Queries" xlsht.Cells(1, 2).Value = "Query Type" x = 2 For Each qry In db.QueryDefs   xlsht.Cells(x, 1).Value = qry.Name   xlsht.Cells(x, 2).Value = qry.Type   x = x + 1 Next xlsht.Cells(1, 3).Value = "Tables" x = 2 For Each tbl In db.TableDefs   xlsht.Cells(x, 3).Value = tbl.Name   x = x + 1 Next Set tbl = Nothing Set qry = Nothing db.Close Set db = Nothing wrk.Close Set wrk = Nothing Set xlsht = Nothing End Sub 

In the DAO example, I place this information into Sheet2. This lets us compare the results of the DAO and ADO/ADOX methods. I also added the query type to the DAO method. This comes across as an integer. To find out what the values are, go to the Immediate Window on the Visual Basic Editor and type ? <constant to determine the values, or go to the object browser and look at the QueryDefTypeEnum collection; the object browser will show you the value if you click on a member of the collection. I have included a table of the values in Table 3-1.

Table 3-1. Query type values

Query type	Value
Action query	240
Append query	64
Compound query	160
Crosstab query	16
Data Definition query	96
Delete query	32
Make table query	80
Select query	0
Union query	128
Pass-through query	112
Update query	48

The type of query is useful if you expect a query to return records or make a table. For records, check the query type to ensure that it returns records. Likewise, to make a table, make sure that the query type is 80.

While it is unlikely that you would use either of the examples above for anything other than potentially documenting a database that you are unfamiliar with, I think that these examples are a good introduction to ADO and DAO and how you can connect to databases using each one. In addition to connecting to Access databases, you can connect to enterprise databases, such as SQL Server or Oracle, using these methods. Since DAO was designed specifically for Jet databases, Chapter 7 will only cover connecting to SQL Server with ADO. However, if you feel more comfortable with DAO, you can connect to an SQL Server database using DAO. When dealing with remote data sources like SQL Server, DAO uses more resources than ADO and therefore runs slower. For this reason, it's frequently better to use ADO with SQL Server.

There are plenty of examples in the help file that comes with DAO to show you how to connect to data sources other than Access. One other thing to keep in mind is that you can use both ADO and DAO in the same application. You will notice when you read over the code above that when I referred to a recordset, I called it an ADODB.RECORDSET. If you only had a reference to ADO, you could simply refer to it as a RECORDSET. However, if you decided later that you needed the functionality in DAO for a specific item, you would have to go to all of your recordset references and change them to ADODB.RECORDSET. So, my recommendation is to always declare your variables with ADODB.ObjectType or DAO.ObjectType. I even do that with Excel items that I'm unlikely to use in any other applications (worksheets, workbooks, etc. as Excel.Worksheet, Excel.Workbook, etc.).