1.3. ADO and DAO

As stated earlier, ADO and DAO are the two primary methods of data access. For the purposes of connecting to a data source and simply extracting data, the two may be used interchangeably. According to Microsoft, DAO was designed specifically for the Microsoft Jet database at the heart of Access, but it is still able to access other databases while taking a performance hit. There are also some differences in features when it comes to making changes to a data source (adding tables, fields, etc.) and performing more complex query functions, such as data shaping, turning the query result into XML, and using cursors. I generally use DAO when dealing with Access (Jet) databases and ADO when dealing with SQL Server or other databases.

If you have done any work in Microsoft Access, you are probably familiar with queries . When you build a query in the design mode in Access, you are really making a graphical representation of the SQL. To see how this works, you can change the query view in Access to SQL View and see what this looks like.

When you use ADO and DAO, you can reference queries and tables and simply open them. Eventually you will need to modify queries or write them from scratch. In those cases, you can get a head start by designing the query graphically in Access, changing the view to SQL view, and copying the text to your VBA project. You can then make any changes that you need to.

While you can simply copy the text of a query and use it in your code, you can also write SQL on the fly within VBA. This is useful when you want to give users the option to bring in certain fields from the database, change the field used to sort, modify the sort order, etc. Also, there are times when you want to place criteria for a query directly in the query instead of using parameters.

In both ADO and DAO, the primary objects that you will work with are queries, recordsets, fields, and parameters. When using DAO, you also have an object called a QueryDef that performs specific tasks in the book. The QueryDef object references a query. When you assign a variable declared as a QueryDef object and refer to a query, you can perform certain tasks, such as changing the SQL of the query, setting the parameter values, and opening the recordset.

There are some specific differences between ADO and DAO regarding how you set up the connection to the data source. You will see examples of each method throughout this book. When making a decision about which one to use, I suggest deciding based on ease of use. For example, if I am working in an Access database and writing VBA code to modify data structure, I find it much easier to use the DAO object model to accomplish those tasks rather than using ActiveX Data Objects Extensions for DDL and Security (ADOX). With ActiveX data objects, there are different object models for data manipulation, data definition and security, and Remote Data Services (RDS) and multidimensional data (ADOMD). In addition, you can download software development kits (SDKs) from Microsoft that explain both object models. Visit http://www.microsoft.com and search for MDAC (the short name of Microsoft's data access software).