Skip to Main Content
Integrating Excel and Access
book

Integrating Excel and Access

by Michael Schmalz
November 2005
Intermediate to advanced content levelIntermediate to advanced
236 pages
6h 32m
English
O'Reilly Media, Inc.
Content preview from 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. ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Business solutions Automating Microsoft® Access with VBA

Business solutions Automating Microsoft® Access with VBA

Susan Sales Harkins, Mike Gunderloy
Microsoft® Office Excel 2003 Programming Inside Out

Microsoft® Office Excel 2003 Programming Inside Out

Curtis Frye, Wayne S. Freeze, Felicia K. Buckingham

Publisher Resources

ISBN: 0596009739Supplemental ContentErrata Page