Data Access from Excel VBA - Choosing Between ADO and DAO (Page 3 of 4 )
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 theQueryDefscollection with aFor Each...Nextloop and get information about each query; you can then make changes to the queries, if necessary. TheQueryDefscollection 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 theProceduresandViewscollections. The other major difference between ADOX and DAO is that in ADOX, the Views are also part of theTables collection, while in DAO they are not. The collection of tables in DAO is called theTableDefs collection. A single table is aTableDef.
The difference between accessing a select query as a view and as a table is that if you access a query in theViewscollection, 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 theQueryDefs collection, as opposed to determining whether the query is now in theViewsorProcedurescollection. But, as you will see below, you are not limited to doing that. Also note that in the ADO example I am using theGetCn 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 theQueryDefTypeEnum 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 |
Table 3-1. Query type values (continued)
| Query type | Value |
| 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 anADODB.RECORDSET. If you only had a reference to ADO, you could simply refer to it as aRECORDSET. 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 toADODB.RECORDSET. So, my recommendation is to always declare your variables withADODB.ObjectType orDAO.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.).
Next: CopyFromRecordset Versus Looping >>
More Database Articles
More By O'Reilly Media
|
 This article is excerpted from chapter three of the book Integrating Excel and Access, written by Michael Schmalz (O'Reilly, 2005; ISBN: 0596009739). Check it out today at your favorite bookstore. Buy this book now.
|
|
