Microsoft Access 2007 tutorial:DAO(Data Access Object)

DAO(Data Access Object)

I. What is DAO?

DAO is an object that was created by Microsoft for working with Microsoft Access files. With DAO, you can open another database while you are using the current database. For instance, you create the interface in the current database, but its data is stored in  another database. You can display and modify the data through the interface. To use DAO object, you need to add Microsoft DAO 3.6 Object Library. To do so, follow the steps:

- Access VBE (Alt + F11)

- Click Tool--> References

- Check Microsoft DAO 3.6 Object Library

- Click OK

Look at the figure below may be more clearer:

Microsoft Access 2007 The Referce Dialog

II. Open Database File With and Without Password

To open database file with DAO, you have to declare a variable that has a data type as Database of DAO. Follow the syntax below:

Dim DbVariable As DAO.Database

Ex: Dim Db As DAO.Database

II.1. Open Database File with Password

To open a database file with password, there are two syntaxes:


Set database=DBEngine.OpenDatabase(dbname, [options], [read-only] ,[connection])


Set database=DAO.OpenDatabase(dbname, [options],[read-only], [connection])

The explanation of Syntax:

Set: The key word is used to set the variable representing an open database.

Database: A variable represents database object that you have to declare for opening your intended existing database file.

DBEngine.OpenDatabase: A method use to open a specified database object.

DbName: The name and path (string type) of Microsoft Jet Database file (Microsoft Access file) that you want to open. It can be the DSN (Data Source Name) of ODBC Data Source.

Options: The optional argument of the syntax that provides two logical values to the parameter are:

- True: Open the database file in exclusive mode style (only one user can access the database)

- False: (Default)Open the database file in share mode style (multiple users can access the database)

Read Only: The optional argument of the syntax that provides two logical values to the parameter are:

- True: The user can't modify the database.

- False: The user can append, edit or delete data in the open database. (Default)If the user misses to select the options (True or False), the database file will take False.

Connection: The optional argument of the syntax that provides the values in connection with database (including password) to the parameter.

For example, open a database file(SaleAndStock.accdb) that contains the password(123). This database file locates in drive C. In addition, the open database file allows many users to access this database and the users can manipulate the data also. To do so, type the code below:

Dim Db As DAO.Database

Private Sub Form_Load()

   Set Db = DBEngine.OpenDatabase("C:\SaleAndStock.accdb", False, False, ";pwd=123")

End Sub

II.2. Open Database File without Password

Opening database file without password is almost the same opening database file with password. But, the difference is that when you open the database file without password, you no need to put the connection (the password).

For instance, open a database file located in drive D (AdvancedDatabase.accdb) in exclusive mode that allows only one user to access this database. Furthermore, the database is not allowed the user to manipulate the data (read only). This can be done with the following code:

Dim Db As DAO.Database

Private Sub Form_Load()

   Set Db = DAO.OpenDatabase("D:\AdvancedDatabase.accdb", True, True)

End Sub

Note: If you want to open the database file in the current location (the location that the database is locating), you can use the code below:

Dim Db As DAO.Database

Set Db = CurrentDb()


This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.