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.
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.
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 MicrosoftAccess 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.
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.
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.
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.
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:
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.
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.
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:
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.
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:
Obtain a reference to the appropriate Document object.
Set the UserName property to establish the account whose
permissions are being modified.
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.
