Visual Basic 6 (VB6)

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.

---

Data Access Objects

• Introduction
• DDL and DML
• Setting up Access to a Database
• Managing Data
• Modifying Database Structure
• Managing Security

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.

Data Access Objects

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.

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.

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

• Establish the appropriate system database.
  Before any other data access code is executed, you must initialize the database engine using the correct system database file (normally system.mda or system.mdw).
• Obtain a user account and password.
  Opening a secured database will normally be done in the context of user account. You can establish the account to use by either specifying the DefaultUser and DefaultPassword properties of the DBEngine object or by passing an account and password in the CreateWorkspace method of the DBEngine object.

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.

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: 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.

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:

• Record Operations
• Bulk Updates

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

• Recordset Object Methods
• Database or QueryDef Execute Method

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.

• Insert
  Adds a new record to a table.
• Update
  Modifies an existing record.
• Delete
  Removes an existing record from a table.

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

Table: Customers
Fields:

• CustID
  Customer ID number. Counter
• CustFirst
  Customer first name. Text.
• CustLast
  Customer last name. Text.

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:

• Error Handling
  Database i/o is one of the most error-prone areas. There could file access errors, network errors, user errors or simply bugs in the code. Accessing data without an error handler is essentially a presciption for disaster. You can use a complex handler with retry loops and other constructs or a simple handler that merely informs the user of the error, but you must include some type of error handling. If you're using the Execute method with a Database or QueryDef object, you'll need to use the dbFailOnError option in the statement.
• Cleanup Code
  Although you can theorically let database object variables go out of scope with no further action after you're done with them, it's good practice to always use the Close method where applicable and set any object variables to Nothing before letting them go out of scope.

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:

• Text values must be enclosed in single quotes.
• Date values must be enclosed in pound signs (#).
• Numeric values are not delimited.
• Embedded single quotes in strings must be replaced with two single quotes.

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: 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: Here's what the previous example would look like if you were using the Contacts table to create a new table called customers: 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. 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.

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.

• DAO
  Use the Delete method with the appropriate collection. To delete a table, you would use the Delete method of the TableDefs collection.
• SQL
  Use the DROP statement. To delete a table using SQL, you would execute the following SQL statement using the Execute method of the Database object:
  DROP TABLE Customers;

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.

Managing Security

If you are unfamiliar with the Jet security system, you should visit my Understanding Jet Security 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.

• Create a User account.
  Use the CreateUser method of the Workspace object.
• Create a Group account.
  Use the CreateGroup method of the Workspace object.
• Add a User to a Group.
  You can either use the Append method of the Groups collection for a User object, or use the Append method with the Users collection of a group object.
• Remove a User from a Group.
  Use the Delete method with the Users collection of a Group or with the Groups collection of a User.
• Delete a User.
  Use the Delete method with the Users collection of the Workspace object.
• Delete a Group.
  Use the Delete method with the Groups collection of the Workspace object.

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: The results will be as follows: 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.

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.

Originally written by Joe Garrick