Microsoft Access 2010 tutorial:dao


DAO (Data Access Object)

I. What is DAO?

DAO (Data Access Object) is an object that was created by Microsoft company for working with Microsoft Access files. You can use DAO to open another database and can modify its data while you are working in the current database. For example, you create a form for displaying, saving, updating, and deleting products' information in the AdvancedDatabase database, but its data is stored in the SaleAndStock database. It means that you are using AdvancedDatabase database, but you can display and modify its data in SaleAndStock database. To use DAO, you need to add Microsoft DAO 3.6 Object Library by doing the following:

- Go to Microsoft Visual Basic for Applications window (Alt + F11 or double-click any module)

- Click Tool

- Click References

- Check Microsoft DAO 3.6 Object Library

- Click Ok

Look at the figure below maybe more clearer.

Access Microsoft DAO 3.6 Object Library 

II. Open database file with and without password

If you want to open a database file with DAO, you need to declare a variable that has a data type as Database of DAO object and follow the declaration statement below:

Dim DbVariable As DAO.Database

Ex: Dim Db As DAO.Database

II.1. Open database file with password

There are two syntaxes for opening database file with password. You can select one among those two types below:

Syntax1:

Set database=DbEngine.OpenDatabase (dbName, [Options], [Read-Only], [Connection])

Syntax2:

Set database=DAO.OpenDatabase (dbName, [Options], [Read-Only], [Connection])

The explanation of syntax

 Set: It is a keyword that is used to set the variable that represents an open database.

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

DbEngine.OpenDatabase: A method uses to open a specified database object.

dbName: The name and path 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. It must be written between double quotes.

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

- True: Opens a database file in exclusive mode (only one user can access to the database)

- False: (Default)Opens a database in share mode (multiple users can access to the database)

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

- True: User can't modify the database.

- False: It is a default option of Read-Only argument of the syntax. Through this option, user can append, edit, delete data in the open database.

Connection: It is also an optional argument of the syntax. It specifies connection information including passwords.

For example, open a database file (SaleAndStock.accdb) that contains the password (lavy123). The database file locates in drive D. In addition, this database is allowed multiple users to access to, but the users cannot manipulate data in the database. To solve the example, use the following code:

Dim Db As DAO.Database

Private Sub Form_Load()

   Set Db = DBEngine.OpenDatabase("D:\SaleAndStock.accdb", False, True, ";pwd=lavy123")

End Sub

II.2. Open database file without password

Opening database file without password is almost the same opening database file with password. When you open the database file without password, you use the same syntax; you just remove 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 allowed the user to manipulate the data. 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, False)

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()


Comments




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.