4.1.1 Understanding DAO Libraries

DAO (Data Access Objects) is a programming interface in Microsoft Access that provides a way to interact with databases. It allows developers to manage and manipulate data in relational database management systems (RDBMS) like Microsoft Access and other databases such as SQL Server. DAO libraries are used in VBA to programmatically work with Access objects like tables, queries, records, and more.

What is DAO?

DAO is a set of objects, methods, and properties that provide a high-level interface for database interaction. It was originally designed for Microsoft Access but can also be used with other databases like SQL Server when working with the Microsoft Jet Database Engine (used by Access).

DAO provides several objects that allow developers to interact with databases in a meaningful way:

• Database: Represents an open database.
• Recordset: Represents a set of records that can be used to read or write data.
• TableDef: Defines the structure of a table.
• Field: Represents a single column of data within a table.
• QueryDef: Represents a saved query.
• Connection: Allows for interaction with a database, opening connections to external databases like SQL Server.

DAO is commonly used in VBA to interact with data, perform CRUD (Create, Read, Update, Delete) operations, and handle database structures.

DAO Libraries in Microsoft Access

In VBA, the DAO library is typically referenced automatically, but in some cases, it may need to be explicitly added. This library provides the tools needed for database operations and is often used in older versions of Access. It is still widely used today, particularly for working with Access databases.

Key DAO Objects:

1. Database Object: The Database object represents the entire database and allows access to its components, such as tables, queries, and relationships.
   • Example:

     Dim db As DAO.Database
     Set db = CurrentDb()
     

2. Recordset Object: A Recordset is a collection of records retrieved from a database. You can manipulate recordsets using DAO methods like AddNew, Update, and Delete.
   • Example:

     Dim rs As DAO.Recordset
     Set rs = db.OpenRecordset("SELECT * FROM Employees")
     

     This code opens a recordset based on the "Employees" table and allows interaction with its data.
3. TableDef Object: The TableDef object represents the structure of a table in a database, including its fields and indexes. It is primarily used for modifying the structure of tables (e.g., adding or deleting fields).
   • Example:

     Dim tdf As DAO.TableDef
     Set tdf = db.TableDefs("Employees")
     

4. QueryDef Object: The QueryDef object represents a saved query in the database. You can use it to create, modify, or execute queries programmatically.
   • Example:

     Dim qdf As DAO.QueryDef
     Set qdf = db.QueryDefs("GetEmployeeData")
     

5. Field Object: The Field object represents a single column (or field) of data in a table or recordset.
   • Example:

     Dim fld As DAO.Field
     Set fld = rs.Fields("EmployeeName")
     

6. Connection Object: The Connection object allows for interactions with external databases (e.g., SQL Server). It provides methods to connect to a remote database and execute commands.
   • Example:

     Dim conn As DAO.Connection
     Set conn = New DAO.Connection
     conn.Open "Provider=SQLOLEDB;Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
     

Why Use DAO Libraries?

DAO is particularly well-suited for:

1. Managing Access Databases: DAO is optimized for working with Access databases and allows for easy manipulation of tables, queries, and relationships.
2. Speed and Efficiency: DAO is faster and more efficient than ADO (ActiveX Data Objects) when working with Access databases due to its tight integration with the Jet Database Engine.
3. Simplified Database Interaction: DAO abstracts much of the complexity of database interactions, making it easier for developers to focus on business logic and user interfaces.

Using DAO Libraries in Access VBA

By default, DAO is included as part of Microsoft Access, but it may need to be explicitly referenced in the References dialog box (Tools > References) when using VBA.

1. Referencing DAO Library:
   • In the VBA editor, go to Tools > References.
   • Check the box for Microsoft DAO 3.6 Object Library (or a later version, such as Microsoft Office xx.x Access Database Engine Object Library).
2. Common DAO Methods:
   • OpenRecordset: Opens a recordset based on a query or table.
   • Execute: Executes a SQL query or command.
   • CreateTableDef: Creates a new table definition.
   • CreateQueryDef: Creates a new query definition.
   Example of Opening a Recordset:

   Dim rs As DAO.Recordset
   Set rs = CurrentDb.OpenRecordset("SELECT * FROM Employees")
   

DAO vs ADO

DAO is often contrasted with ADO (ActiveX Data Objects), another popular data access library. While ADO can be used with a variety of databases (e.g., SQL Server, Oracle), DAO is specifically designed for use with Access databases and is more efficient when working within the Access environment. For Access databases, DAO is generally preferred over ADO.

DAO in Action: Practical Example

Here’s a practical example of how you might use DAO in Access VBA to interact with a database and generate a report:

Sub GenerateReport()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sql As String
    
    ' Open the current database
    Set db = CurrentDb()
    
    ' Define a SQL query to get data for the report
    sql = "SELECT * FROM Employees WHERE Department = 'Sales'"
    
    ' Open a recordset based on the query
    Set rs = db.OpenRecordset(sql)
    
    ' Loop through the recordset and display the data
    Do While Not rs.EOF
        Debug.Print rs!EmployeeName & " - " & rs!JobTitle
        rs.MoveNext
    Loop
    
    ' Close the recordset and database
    rs.Close
    db.Close
End Sub

Conclusion:

DAO libraries in Microsoft Access offer a powerful set of tools for interacting with databases, especially when working within Access itself. By understanding and using DAO objects like Database, Recordset, TableDef, and QueryDef, developers can automate and optimize data access, manipulation, and reporting within Access databases. Whether you're managing tables, generating reports, or automating queries, DAO provides a consistent and efficient approach to database management within the Access environment.