Jose's Database Programming Corner

Using Jet Data Access Objects



Introduction to Jet

Lets start by clearing up a common point of confusion regarding Access and Jet. Jet is a database engine. It handles the i/o to the database and provides programmable objects representing the database. Access is an application which can be used to build databases. While both share the same Jet database engine, when you are working with databases in Visual Basic, you are working with Jet, not Access.

Access provides many tools for managing databases which are not available from Visual Basic, such as forms, reports, etc. While it is possible to programmatically control Access from Visual Basic using DDE or OLE Automation, these are Access objects, not Jet objects. They are not part of the native interface available from Visual Basic.

Jet, on the other hand, provides a set of programmable objects which you can use to manage both the structure of a database and its data. While there are dozens of objects available in the Jet Data Access Object (DAO) library, you will most commonly be working with the Database object, Recordset objects, and QueryDef objects. You'll also need to understand the basics of using the DBEngine object and the Workspace object.


Return to Top of Page Return to top of page

Data Access Objects

Return to beginning of DAO section Introduction

The help files provided with Visual Basic and Access have extensive and complete descriptions of the DAO objects which I don't intend to duplicate here. What I will provide is a means to help you navigate through the extensive set of objects, properties, and methods to find what you need and an explanation of how some of the objects are used.

To assist you in navigating the object library, I have provided a map of the DAO library. You may wish to keep the map graphic open in a separate window while reading the rest of the page. If you're using Internet Explorer or Netscape, right click on the map and select "Open in New Window" from the context menu.

Data Access Objects

Data Access Objects

Return to beginning of DAO section DDL and DML

If you've worked with databases before, you may be accustomed to thinking of the database language as being divided into Data Definition Language (DDL) and Data Manipulation Language (DML). While Jet DAO provides access to both the structure and the data in a database, there is no clear distinction between DDL objects and DML objects. While some objects, such as the Recordset object are used strictly for DML, others, such as the QueryDef object, cross the line between the two. For example, you can use the CreateQueryDef method to create a saved query in a database, then later use the same QueryDef object returned by CreateQueryDef to execute a SQL statement to modify data in the database.

Although in most cases you will probably be using Microsoft Access to build your database structure, there may be times when you need to create, modify, or delete database objects in your code. For more information on modifying the database structure using DAO, you can read the Modifying Database Structure section.

The most common use for the DAO library, however, is to manage data. This is most often done using the Recordset object. With the Recordset object, you can insert, update, and delete records in the database. For more information on managing data with DAO, read the Managing Data section.

It should be noted that nearly all of the properties and methods available through DAO are also available using Structured Query Language (SQL). You can use SQL to create, modify, and delete objects and data in the database. In some cases, using DAO will be more straightforward than SQL and in other cases it is simpler to use SQL than DAO. The choice of using SQL or DAO is yours. You can use one or the other exclusively, or mix and match the two.

Return to beginning of DAO section Setting up Access to a Database

Before you can do anything useful with DAO, you will need to open a database. Normally, this is as simple as using the OpenDatabase method of the default workspace, but some special considerations apply if you are using a secured database. If this is the case, you will need to establish the proper system database, user account, and password.

Let's first take a look at a simple example of opening an unsecured database.

	Dim sDBName As String
	Dim ws As Workspace
	Dim db As Database

	' GetDBName is some function that returns the full path to the .mdb file
	sDBName = GetDBName()

	' set a reference to the default workspace
	Set ws = DBEngine.Workspaces(0)
	' open the file
	Set db = ws.OpenDatabase(sDBName)

If the database is secured, some additional steps are involved.

Regardless of the method you use, you will probably need to build a login form where the user can enter an account name and password. While you might wish to store the account name in the registry or wherever you are keeping any other profile information, for security reasons it is not recommended that you store the password. Also for security reasons, you should not append the Workspace you create to the Workspaces collection of the DBEngine

Let's take a look at an example.

	Dim sSystemDBName As String
	Dim sDBName As String
	Dim sUserName As String
	Dim sPassword As String
	Dim ws As Workspace
	Dim db As Database

	' GetSystemDBName is assumed to be a function that returns the full path of the system database
	sSystemDBName = GetSystemDBName()
	' GetDBName is assumed to be a function that returns the full path of the file to open
	sDBName = GetDBName()
	' GetAccountInfo is assumed to be a sub that returns the account and password in the paramter list
	GetAccountInfo sUserName, sPassword

	' Assign the SystemDB property - this is the full path to the system.mdw file
	DBEngine.SystemDB = sSystemDBName

	' Create a secure workspace
	Set ws = DBEngine.CreateWorkspace("foo", sUserName, sPassword)

	' Open the database
	Set db = ws.OpenDatabase(sDBName)

This is a fairly straightforward process, but rather poorly documented. Additionally, this applies only to 32-bit VB4 and Jet 3.0. If you are using an earlier edition of the Jet engine, you will still need to create an application initialization file with a miminum of the following:

	[Options]
	SystemDB=

Keep in mind that if you are working with a secured database, you must set the SystemDB property of the DBEngine before calling any other DAO code or the OpenDatabase method will fail.

Return to beginning of DAO section Managing Data

Inserting, updating, and deleting records will be your most common operations in a database application. In general, you can divide data modification operations into two basic groups:

There are also two ways of performing both types of operations:

In general, the methods of the Recordset object will be easier to use, but the SQL based approach of using the Execute method of either the Database or QueryDef object will be faster.

Record Operations

Most of the data management you do will probably be based on operations involving a single record. There are three basic operations you can perform.

In the following examples, we'll use a simple hypothetical table structured as follows:

Table: Customers
Fields:

Additionally, in each of the examples, assume that we have a Database object variable, db, that is a reference to an open database file.

Operation Method Sample Code
Insert Recordset AddNew AddNew method of the Recordset object
    Dim rs As Recordset

    ' The dbAppendOnly constant is added to improve performance
    Set rs = db.OpenRecordset _
        ("Customers", dbOpenDynaset, dbAppendOnly)
    ' use optimistic locking
    rs.LockEdits = False
    rs.AddNew
    rs.CustFirst = "Joe"
    rs.CustLast = "Garrick"
    rs.Update
		

Notes: If you are using a counter field as the primary key for a table, you will often wish to retrieve the value of the new counter when inserting a record. You can do this by either assigning the value of the counter field to a variable before calling the Update method, or by returning to the new record using the built-in LastModified bookmark. I generally use the former method. To do so, delcare a long integer variable in the procedure and assign it the value of the counter field anywhere between using the AddNew and the Update methods. For example:

    rs.AddNew
    lCustID = rs.CustID
    ...
    rs.Update
		
Database Execute Execute method of the Database Object
    Dim sSQLInsert As String

    sSQLInsert = "INSERT INTO Customers " & _
        "(CustFirst, CustLast) VALUES ('Joe', 'Garrick');"
    db.Execute sSQLInsert, dbFailOnError
		

Notes: Search the VB help files for "INSERT INTO" for a complete description of the syntax for inserting records using the Execute method. Also note that this is considerably less practical if you are using counters for your primary key since there is no readily available means of retrieving the new primary key value. While you could theoretically open a new recordset based on the table and move to the last record or query using a WHERE clause based on the newly inserted values, neither method is completely reliable. This method is generally better suited for inserting records where the value of the primary key can be predetermined and explicitly assigned to the appropriate field or fields.

Update Recordset Edit Edit method of the Recordset object.
    Dim sSQL As String
    Dim rs As Recordset

    sSQL = "SELECT CustFirst, CustLast " & _
        "FROM Customers WHERE CustID = 1;"
    Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)
    rs.LockEdits = False
    rs.Edit
    rs.CustFirst = "John"
    rs.CustLast = "Smith"
    rs.Update
		

Notes: Here we have opened a Recordset using the CustID field, which is the primary key for the table, as the criteria for the WHERE clause of the query. Generally, using the primary key is the only reliable way of retrieving a single record. You could also open the table without any criteria and use a Find method or the Seek method, but doing so is normally less efficient than simply specifying the records to retrieve in the SQL statement.

Database Execute Execute method of the Database Object
    Dim sSQL As String

    sSQL = "UPDATE Customers " & _
        "SET CustFirst = 'John', CustLast = 'Smith' WHERE CustID = 1;"
    db.Execute sSQL
		

Notes: Here we have the same where clause for the query, but use the UPDATE ... SET syntax and the Execute method. This approach is generally more efficient than using the Edit method of the Recordset object, but the SQL syntax is somewhat more obscure and more difficult to build dynamically if you are passing the values to update as parameters to the procedure.

Delete Recordset Delete Delete method of the Recordset object
    Dim sSQL As String
    Dim rs As Recordset

    sSQL = "SELECT CustID FROM Customers WHERE CustID = 1;"
    Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)
    rs.Delete
		

Notes: The WHERE clause from the previous examples is again used to open a Recordset containing a single record, to which the Delete method is applied. When you use the Delete method, the current record remains current but is inaccessible. Note also that if you will be deleting a record, there is no reason to retrieve more than a minimum number of fields from the record. Using "SELECT * FROM Customers WHERE CustID = 1;" would simply be a waste of resources.

Database Execute Execute method of the Database object
    Dim sSQL As String

    sSQL = "DELETE FROM Customers WHERE CustID = 1;"
    db.Execute sSQL
		

Notes: The same WHERE clause is again used. Like the other examples, this is generally a more efficient way of removing records. When using a SQL DELETE, there is no need to specify a field list.


The example code shown above is, of course, simply a skeleton of what a complete procedure would look like. In addition to the actual insert, update, or delete action, any procedure which manipulates data should also include the following:

At this point you may be asking yourself if you should be using recordsets or SQL statements with the Execute method. There's no clear answer to this question. Generally, I use recordsets for insertions and updates for simplicity. Although there is a slight performance penalty, it's considerably easier to construct the code using recordsets. In most cases, the WHERE clause of the SQL statement used to select the records will be the same, but constructing the VALUES clause for an insert or the SET clause for an update can be complicated. The same rules that apply for constructing a WHERE clause apply:

Bulk Updates

Although it's possible to do bulk updates using recordsets, it's probably the most inefficient and difficult approach. Normally, the preferred method is to use the same SQL INSERT, UPDATE ... SET, and DELETE statements you would use with a single record, but adjust the WHERE clause so that it returns more than one record. In the case of an insert, you will normally be selecting records from one table and inserting them into another. Rather than providing a list of fixed values, the SQL statement would look like this:

	INSERT INTO Customers (CustFirst, CustLast) SELECT ContactFirst, ContactLast FROM Contacts;

There is also another type of insert construction that can be used to build a new table from existing records (known as a make-table query in Access). Here's the general form:

	SELECT  INTO  FROM 

Here's what the previous example would look like if you were using the Contacts table to create a new table called customers:

	SELECT ContactFirst, ContactLast INTO Customers FROM Contacts;

If for some reason (perhaps a test too complex to be expressed as a SQL statement) you do need to do a bulk update using a recordset, the preferred method is to use a Do loop.

	Do While Not rs.EOF
	  rs.Edit
	  ...
	  rs.Update
	  rs.MoveNext
	Loop

In the loop, rs.EOF is the test for the loop exit. The EOF property of a recordset will be True after moving past the last record. Even if you do have a total count of records, using a For loop is not recommended.

Return to beginning of DAO section Modifying Database Structure

In most cases, you will be using Access or another third party tool to build the structure of the databases your application will use, however, there may be times when it becomes necessary to create database objects in code. Manipulating the database structure is similar to manipulating the data - you can either use DAO to create objects and set properties, or you can use SQL and the Execute method of the Database object.

The choice of which to use is for the most part personal preference. Both methods are capable of defining database objects. In some cases you must use DAO. The following table shows the SQL and DAO equivalents for creating database objects.

Object DAO (Object.Method) SQL
Table Database.CreateTableDef CREATE TABLE statement
Query Database.CreateQueryDef Unavailable. Use DAO CreateQueryDef
Field TableDef.CreateField CREATE TABLE
ALTER TABLE ... ADD COLUMN
Index TableDef.CreateIndex CREATE TABLE
ALTER TABLE ... ADD CONSTRAINT
CREATE INDEX
Relationship TableDef.CreateRelation CONSTRAINT clause with CREATE TABLE or ALTER TABLE


It is also possible to delete objects from the database structure.

In general, the Visual Basic help files have fairly complete explanations of each of the techniques for creating and deleting database objects, including examples for each.

Return to beginning of DAO section Managing Security

If you are unfamiliar with the Jet security system, you should visit my Understanding Jet Security page. You may also wish to review the Access security white paper available from Microsoft. I've made a copy available on my downloads page.

Although in most cases you will be able to manage security attributes for a database using the Access interface, there may be times when you need to manage security using DAO. There may also be occasions where you need to give an end user access to an object for which they normally would not have permission.

The security information you can work with will fall into one of two categories:

  1. User and Group Accounts
    With Jet security, accounts are independent of any database and are defined with the context of a system database. Access to accounts is gained through the Workspace object.
  2. Object Permissions
    Permissions are assigned to User or Group accounts for database objects. Access to the Permissions property is obtained via the Document objects in the Documents collection of the appropriate Container object.

User and Group Accounts

User and Group accounts exist outside the context of any particular database and are defined in the system or workgroup database. The following outlines the general procedures for working with these objects.

The VB help files come with examples of managing User and Group accounts. Search help for the Workspace object, User object, or Group object.

Object Permissions

Object permissions are granted and revoked by using the Permissions property of the Document object representing the database object. The individual Document objects can be found within the Documents collections of the appropriate Container objects.

Although there are Container objects for forms, reports, macros, and modules in an Access database, you will normally be working with the Tables Container, which represents saved tables and queries in a database.

There are three steps involved in assigning permissions for an object:

  1. Obtain a reference to the appropriate Document object.
  2. Set the UserName property to establish the account whose permissions are being modified.
  3. Grant the permission using the bitwise Or operator or revoke it using And Not.

It is important to remember to use the bitwise operators to grant or revoke permissions rather than simply adding the appropriate constant. If you add a security constant to the Permissions property of an object for which that permission has already been assigned, the result will not be correct. This is because the Permissions property is a bit field. If you're not familiar with using bit fields, run the following code:

   Dim lBitField As Long

   Debug.Print "Add 1 to the bit field twice."
   lBitField = 0
   Debug.Print lBitField
   lBitField = lBitField + 1
   Debug.Print lBitField
   lBitField = lBitField + 1
   Debug.Print lBitField

   Debug.Print "Or 1 to the bit field twice."
   lBitField = 0
   Debug.Print lBitField
   lBitField = lBitField Or 1
   Debug.Print lBitField
   lBitField = lBitField Or 1
   Debug.Print lBitField

The results will be as follows:

Add 1 to the bit field twice.
 0
 1
 2
Or 1 to the bit field twice.
 0
 1
 1

As you can see, the results are not correct when using the addition operator with a bit field.

The following example illustrates assigning and revoking permissions for a document.

	Dim doc As Document
	Dim sAccount As Group

	' The Tables container holds both tables and queries, which share the same
	' name space in a Jet database file.
	Set doc = db.Containers("Tables").Documents("Customers")
	' set permissions for the Users group.
	' Note that since user and groups share the same name space, there is only
	' a UserName property. No GroupName property is necessary.
	doc.UserName = "Users"
	' grant the Delete permission
	doc.Permissions = doc.Permissions Or dbSecDeleteData
	' revoke the Update permission
	doc.Permissions = doc.Permissions And Not dbSecReplaceData

Another important factor to remember when working with objects and permissions is that a user will inherit any permissions assigned to groups of which the user is a member. You can use the AllPermissions property of a Document object to examine both directly assigned and inherited permissions.

Normally, any permission assigned to the user or any group the user belongs to are available to the user. One situation where the permissions may not be what you expect occurs when working with attached tables. With an attached Jet table, the user (or any group the user belongs to) must have permissions for the table in both the database where the table is attached and in the database where the table actually resides. This also applies to tables attached from other sources, such as SQL Server. Although Jet is not aware of security settings on server databases, it cannot violate them. If you are working with server tables, you will need to make sure that the user has the appropriate permissions assigned on both the server and the locally attached tables.


Return to Beginning of DAO Section Return to Beginning of DAO Section

Return to Top of Page Return to top of page

| 1997 Joe Garrick |
I don't know if this email is valid anymore, the webpages are no longer available on the Internet