C H A P T E R 11 | Microsoft Office 97/Visual Basic Programmer's Guide |
Data Access Objects |
Microsoft DAO objects provide a way to interact with a database from any application that includes Visual Basic for Applications. DAO objects represent different parts of a database. You can use DAO objects to work with the parts of your database from code. With DAO objects, you can:
Note In order to use DAO objects, you must select the Data Access check box when you install Microsoft Office. If you haven't installed Data Access with Microsoft Office, run Setup again.
DAO objects are organized in a hierarchical relationship. Objects contain collections, and collections contain other objects. The DBEngine object is the toplevel object that contains all the other objects and collections in the DAO object hierarchy. The following table summarizes the DAO objects.
Object | Description |
Connection | Network connection to an Open Database Connectivity (ODBC) database |
Container | Security information for various types of objects in the database |
Database | Open database |
DBEngine | The top-level object in the DAO object hierarchy |
Document | Security information for individual objects in the database |
Error | Data access error information |
Field | Field in a TableDef, QueryDef, Recordset, Index, or Relation object |
Group | Group account in the current workgroup |
Index | Table index |
Parameter | Query parameter |
Property | Property of an object |
QueryDef | Saved query definition in a database |
Recordset | Set of records defined by a table or query |
Relation | Relationship between two table or query fields |
TableDef | Saved table definition in a database |
User | User account in the current workgroup |
Workspace | Active DAO session |
Designing Databases in Microsoft Access
You can design databases in Visual Basic with DAO. However, if
you're programming in an application other than Microsoft Access,
you may find it faster to design your database in the Microsoft
Access user interface, then write DAO code for any additional
functionality that you want. With Microsoft Access, you can quickly
and easily design tables, queries, indexes, and relationships;
link tables from external data sources; and implement security.
You can then open the database with DAO from another application
that hosts Visual Basic.
There are a few things to keep in mind when you create a database
in Microsoft Access:
Setting a Reference to the Microsoft DAO Object Library
To work with DAO objects from within any application, you must
have a reference to the Microsoft DAO 3.5 object library. Microsoft
Access sets this reference automatically. You may need to set
it yourself if you're working within another Microsoft Office
application.
To set a reference to the Microsoft DAO 3.5 object library from
a Microsoft Office application other than Microsoft Access, open
the Visual Basic Editor, click References on the Tools
menu, and then select the Microsoft DAO 3.5 Object Library
check box. Once you've set a reference to the DAO object library,
you can view the DAO objects in the Object Browser by clicking
DAO in the Project/Library box.
Some objects, properties, and methods that were supported in earlier
versions of Microsoft DAO have been replaced by new objects, properties,
and methods with more complete functionality, and are no longer
supported by DAO version 3.5. If you're working with an application
created in an earlier version of Microsoft DAO, you can set a
reference to the Microsoft DAO 2.5/3.5 compatibility library rather
than to the Microsoft DAO 3.5 object library. The Microsoft DAO
2.5/3.5 compatibility library contains all of the objects, methods,
and properties that are in the Microsoft DAO 3.5 object library,
plus some that existed in DAO version 2.5, but that are no longer
supported in DAO version 3.5.
Code that uses objects, methods, and properties that were available
in DAO version 2.5 but are no longer available in DAO version
3.5 will continue to run when you reference the Microsoft DAO
2.5/3.5 compatibility library. However, it's a good idea to update
your code to take advantage of the features of DAO version 3.5,
and to write new code that uses the objects, properties, and methods
provided by the Microsoft DAO 3.5 object library. The Microsoft
DAO 2.5/3.5 compatibility library is larger, so it requires more
resources. Also, future versions may not support some objects,
methods, and properties which are now available in the compatibility
library.
To determine whether you need to use the compatibility library,
make sure there is a reference set to the Microsoft DAO 3.5 object
library and compile all modules that contain DAO code. If your
code compiles without any problems, you can use the Microsoft
DAO 3.5 object library. If your DAO code generates compile errors,
then you should set a reference to the Microsoft DAO 2.5/3.5 compatibility
library and try to compile your code again.
For more information about which DAO features are supported in
the DAO 2.5/3.5 compatibility library but not in the Microsoft
DAO 3.5 object library, search DAO Help for "Obsolete features
in DAO," or search Microsoft Access Help for "DAO, compatibility
with previous versions."
Referring to DAO Objects in Visual Basic
You refer to DAO objects in code in the same way that you refer
to other objects. Because the DBEngine object doesn't have
a collection, you can refer to it directly. You must refer to
other objects within their collections and according to their
positions in the object hierarchy.
You can refer to any type of object within a collection in one
of two ways: by its Name property setting or by its index
number, which indicates its position within the collection.
DAO objects are indexed beginning with zero. This means that the
first object in a collection has an index number of 0, the second
object has an index number of 1, and so on. The following examples,
which refer to a Database object within the Databases collection, illustrate both ways to refer to an object within
a collection.
To refer to a Database object in code, you also need to
refer to it according to its position within the object hierarchy.
The following code fragment shows how you can actually refer to
a Database object in code. The Database object is
the first member of the Databases collection of the default
Workspace object, which is a member of the Workspaces collection of the DBEngine object. Note that if you're
working in an application other than Microsoft Access, you must
open a database with the OpenDatabase method before you
run this code.
Databases("database name")
Databases(0)
When you work with DAO objects from any application other than
Microsoft Access, you may want to qualify the object with the
Visual Basic name of the DAO object library, which is DAO.
By qualifying objects when you use them, you ensure that Visual
Basic always creates the correct object. The following example
declares a DAO object variable of type Database:
Dim dbs As Database
Set dbs = DBEngine.Workspaces(0).Databases(0)
' Qualify object variable type.
Dim dbs As DAO.Database
Adding New DAO Objects to a Collection
As stated earlier in this chapter, some DAO objects represent
the structure of the database, and others provide a means for
you to work with the data stored in the database. Objects that
represent the structure of the database are saved with the database.
Objects that you use to work with the data in the database generally
are not saved, but are created each time you need them.
When you create a new DAO object to be saved with the database,
you must append it to the appropriate collection of saved objects
by using that collection's Append method. The following
example creates a new TableDef object named ArchivedInvoices
with a new Field object named OrderID. It appends the new
Field object to the Fields collection of the new
TableDef object, and it appends the TableDef object
to the TableDefs collection of the Database object
that represents the open database.
Note The
following example, and other examples in this chapter, use the
Northwind sample database to illustrate concepts of DAO programming.
In order to try these examples, you need to have installed the
Northwind sample database which is included with Microsoft Access.
By default, it is installed in the C:\Program Files\Microsoft
Office\Office\Samples folder. If you haven't installed the Northwind
sample database, you can install it by running Setup again.
Function AddTable() As Boolean
' Declare object variables and constant.
Dim dbs As Database, tdf As TableDef, fld As Field
Const conPath As String = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
On Error GoTo Err_AddTable
' Assign current database to database variable.
Set dbs = DAO.DBEngine.Workspaces(0).OpenDatabase(conPath)
' Create new table and field, and assign to table and field variables.
Set tdf = dbs.CreateTableDef("ArchivedInvoices")
Set fld = tdf.CreateField("OrderID", dbLong)
' Add field to table's Fields collection.
tdf.Fields.Append fld
' Add table to database's TableDefs collection.
dbs.TableDefs.Append tdf
dbs.Close
AddTable = True
Exit_AddTable:
Exit Function
Err_AddTable:
MsgBox
"Error " & Err & ": " & Err.Description
AddTable = False
Resume Exit_AddTable
End Function
Note The
preceding example uses the OpenDatabase method to open the Northwind
sample database, return a reference to it, and assign this reference
to an object variable of type Database. If you're programming
within Microsoft Access, use the CurrentDb function to return
a reference to the database that's currently open in Microsoft
Access.
Working with External Data
You can use DAO to work with databases in different formats. There
are three different categories of database formats that are accessible
through DAO. The first type of format is the Microsoft Jet format.
You can use DAO to work with all databases created with the Microsoft
Jet database engine, including those created in Microsoft Access,
Microsoft Visual Basic, Microsoft Visual C++®,
and Microsoft Excel.
The second type of database format is the installable ISAM format.
An installable ISAM is a driver that provides access to external
database formats through DAO and the Microsoft Jet database engine.
You must use your application's Setup program to install any installable
ISAMs that you want to use. Installable ISAMs are loaded by Microsoft
Jet when you refer to them in code. The individual database formats
for which installable ISAMs are available include:
The third type of database format that is accessible through DAO
is the Open Database Connectivity (ODBC) data source. ODBC data
sources, such as Microsoft SQL Server
versions 4.2 and later, require an ODBC driver. Often an ODBC
data source resides on a network server. ODBC is useful for developing
client/server applications. The next section introduces two ways
to work with ODBC data sources through DAO.
Using DAO to Work with ODBC Data Sources
There are two different ways to use DAO to work with ODBC data
sources: through Microsoft Jet, or by means of a new technology
called ODBCDirect. If you're working with a
database created with the Microsoft Jet database engine or in
an external format supported by an installable ISAM, all DAO operations
are processed through Microsoft Jet. If you're working with an
ODBC data source, you can either process DAO operations through
Microsoft Jet, or you can use ODBCDirect to circumvent the Microsoft
Jet engine and work directly with the data in the ODBC data source.
Whether you use DAO with Microsoft Jet or with ODBCDirect to work
with an ODBC data source depends on what kind of operations you
need to perform on the data source. You can use DAO with Microsoft
Jet when you need to take advantage of Microsoft Jet's unique
features for ODBC operations, such as the ability to create or
modify objects or to join data from different database formats.
You can use ODBCDirect when you need to run queries or stored
procedures against a backend server, such as Microsoft SQL
Server, or when your client application needs only the specific
capabilities of ODBC, such as batch updates or asynchronous queries.
ODBCDirect can also make certain client/server operations significantly
faster.
Because not all DAO features are available with ODBCDirect, Microsoft
DAO still supports ODBC through the Microsoft Jet database engine.
You can use ODBC through Microsoft Jet, ODBCDirect, or both, with
a single ODBC data source.
Which of these two methods you can use to access an ODBC data
source is determined by what type of workspace you're working
in. A workspace, represented by a Workspace object, is an active session for a particular user account.
A session marks a sequence of operations performed
by the database engine. A session begins when a particular user
logs on and ends when that user logs off. The operations that
a user can perform during a session are determined by the permissions
granted to that user. If you don't specifically create a workspace,
then DAO creates a default workspace for you.
With Microsoft DAO version 3.5, you can create either of two types
of workspaces for ODBC operations. If you create a Microsoft
Jet workspace, you can use DAO with Microsoft Jet to
access ODBC data. If you create an ODBCDirect workspace,
you can use DAO to work directly with the data in the ODBC data
source, without going through the Microsoft Jet database engine.
Each type of workspace has its own object model. The next section
of this chapter discusses the object model for the Microsoft Jet
workspace. Later sections discuss the advantages of using each
type of workspace and describe the object model for ODBCDirect
workspaces.
Microsoft Jet workspaces include objects that you can use to define the structure of your database, such as the TableDef, QueryDef, Field, Index, Parameter, and Relation objects. Microsoft Jet workspaces also include objects that you can use to manipulate your data, such as the Recordset object. You can use other objects, such as the User, Group, Container, and Document objects, to secure your application. The following diagram shows the object model for Microsoft Jet workspaces.
The DBEngine Object
As previously mentioned, the DBEngine object is the toplevel
object in the DAO object hierarchy. It contains all other DAO
objects and collections. The DBEngine object is the default
object in the object model, so in many cases you don't need to
refer to it explicitly.
The DBEngine object contains two collections: the Workspaces collection and the Errors collection. The Workspaces collection is the default collection of the DBEngine object,
so you don't need to refer to it explicitly. You can return a
reference to the first Workspace object in the Workspaces collection of the DBEngine object in any of the following
ways:
If you don't specifically create a new Workspace object,
DAO automatically creates a default workspace when you need it.
The settings of the DefaultUser and DefaultPassword
properties of the DBEngine object specify the default user
name and password to be used with the default Workspace object.
By default, the DefaultUser property is set to Admin and
the DefaultPassword property is set to a zerolength
string ("").
Set wrk = DBEngine.Workspaces(0)
Set wrk = DBEngine(0)
Set wrk = Workspaces(0)
The setting for the DefaultType property of the DBEngine object determines whether the default workspace is a Microsoft Jet workspace or an ODBCDirect workspace. By default, the DefaultType property is set to dbUseJet, and the default workspace is a Microsoft Jet workspace. When you're creating a workspace, you can override the setting for this property by specifying either dbUseJet or dbUseODBC as the type argument of the CreateWorkspace method. For example, if the DefaultType property is set to dbUseJet and you want to create an ODBCDirect workspace, specify the dbUseODBC constant as the type argument of the CreateWorkspace method. Conversely, if the DefaultType property is set to dbUseODBC and you want to create a Microsoft Jet workspace, specify the dbUseJet constant as the type argument of the CreateWorkspace method.
You can use some of the methods of the DBEngine object to maintain your database. For example, the CompactDatabase method copies your database and compacts it. The RepairDatabase method attempts to repair a database that's been damaged.
For more information about the DBEngine object, search DAO Help for "DBEngine object."
The Workspace Object and the Workspaces
Collection
The DAO Workspace object defines a session for a user,
based on the user's permissions. You use the Workspace object
to manage the current session. The Workspace object contains
open databases and provides mechanisms for simultaneous transactions
and for securing your application. The Workspaces collection
contains all active Workspace objects of the DBEngine object that have been appended to the Workspaces collection.
When you begin working with DAO objects in Visual Basic, DAO automatically
creates a default workspace. To refer to the default workspace,
you can refer to the index number of the first Workspace object
in the Workspaces collection, as shown in the following
example:
Dim wrk As Workspace
Set wrk = Workspaces(0)
DAO workspaces can be shared or hidden. A workspace is hidden until the user marks it as shared by appending the Workspace object to the Workspaces collection. After a workspace has been appended, you can access it throughout your code simply by referring to it within the Workspaces collection. If you need a Workspace object only within a particular procedure, you can create the Workspace object but not append it to the Workspaces collection.
As noted earlier in this chapter, there are two types of Workspace objects: Microsoft Jet workspaces and ODBCDirect workspaces. In a Microsoft Jet workspace, you can use DAO with the Microsoft Jet database engine to access data in Microsoft Jet databases, installable ISAM data sources, and ODBC data sources. In an ODBCDirect workspace, you can use DAO to access data in ODBC data sources, without going through the Microsoft Jet database engine. You can work with both Microsoft Jet and ODBCDirect workspaces from within a single application.
For more information about ODBCDirect workspaces, see "Using DAO with ODBCDirect" later in this chapter.
Creating a New Microsoft Jet Workspace
To create a new Microsoft Jet workspace, use the CreateWorkspace
method of the DBEngine object. The following code creates
a Microsoft Jet workspace. The constant specified for the type
argument, dbUseJet, specifies that the workspace will be
a Microsoft Jet workspace. If the DefaultType property
of the DBEngine object is set to dbUseJet, then
you don't need to specify a value for the type
argument; DAO automatically creates a Microsoft Jet workspace.
Dim wrk As Workspace
Set wrk = CreateWorkspace("JetWorkspace", "Admin", "", dbUseJet)
Newly created Workspace objects those created with the CreateWorkspace method are not automatically appended to the Workspaces collection. You can use the Append method of the Workspaces collection to append a new Workspace object if you want it to be part of the collection. However, you can use the Workspace object even if it's not part of the collection. Append the new Workspace object to the Workspaces collection if you want to use the workspace from procedures other than the one in which you created it.
For more information about creating a workspace, search DAO Help for "CreateWorkspace method."
The Error Object and the Errors Collection
The Error object contains information about an error that
occurred during a DAO operation. More than one error can occur
during a single DAO operation; each individual error is represented
by a separate Error object. The Errors collection
contains all of the Error objects that correspond to a
single DAO operation. When a subsequent DAO operation generates
an error, the Errors collection is cleared, and one or
more new Error objects are placed in the Errors collection.
DAO operations that don't generate any errors have no effect on
the Errors collection.
The first Error object in the Errors collection
represents the lowest level error, the one that occurred closest
to the ODBC data source. The second represents the next higher
level error, and so forth. For example, if an ODBC error occurs
while trying to open a Recordset object, the first Error object, Errors(0),
contains the lowest level ODBC error; other Error objects
contain the ODBC errors returned by the various layers of ODBC,
and the last Error object contains the error returned by
DAO. In this case, the ODBC driver manager, and possibly the driver
itself, return separate Error objects. The index number
of the last Error object in the collection, the DAO error,
is one less than the value returned by the Count property
of the Errors collection. The Visual Basic Err object
contains the same error as the last Error object in the
DAO Errors collection.
The following example tries to insert values into a table that
doesn't exist, causing two DAO errors.
Note The
following example, and other examples in this chapter, use the
Microsoft SQL Server Pubs sample database to illustrate concepts
of client/server programming. This database is included with Microsoft
SQL Server. If you don't have Microsoft SQL Server, you can adapt
the example to your work with your data source, or simply study
it to understand the concepts. Before you can work with any ODBC
data source, you must register it. For information about registering
an ODBC data source, see "Registering an ODBC Data Source" later in this chapter.
The Database Object and the Databases
Collection
The Database object represents an open database. It can
be a Microsoft Jet database or an external data source. The Databases collection contains all currently open databases. The following
table shows the relationship between the Database object
and the Databases collection and other objects and collections
in a Microsoft Jet workspace.
QueryDefs collection
Properties collection
Recordsets collection
Relations collection
TableDefs collection
Opening a Database Object
To open a database and return a reference to the Database object
that represents it in any application other than Microsoft Access,
use the OpenDatabase method of the DBEngine object
or of a Workspace object. When you use the OpenDatabase
method of the DBEngine object, Microsoft DAO opens the
database in the default workspace, as shown in the following example.
If you're working within Microsoft Access, use the Microsoft Access
CurrentDb function to return a reference to the database
that's currently open. Use the OpenDatabase method to open
databases other than the one that's currently open, or to open
databases in an ODBCDirect workspace. The following example uses
the CurrentDb function to return a reference to the database
that is currently open in Microsoft Access.
Creating Database Replicas with DAO
If you need to maintain two or more copies of a database, you
can replicate the database. When you replicate a database, you
designate the database to be the Design Master and create one
or more copies of it that are identical in structure and data;
the copies are called replicas. You can create
multiple replicas of a database and maintain them on the same
computer or over a network. You can add, change, or delete objects
only in the Design Master. You can change data in the Design Master
or in any of the replicas. When a user changes data within one
replica, the users of other replicas can synchronize their replica,
so that the same data is maintained in all replicas.
You can use DAO to make a database replicable, create replicas,
synchronize replicas, and manage a set of replicas. You can also
use DAO to create partial replicas. Partial replicas
are replicas that contain only a subset of records in
a full replica. By using partial replicas, you can synchronize
a replica with only the data that you need, rather than with an
entire database. For more information about partial replicas,
search Microsoft Access Help for "partial replicas."
To replicate a database with DAO, you must first make the database
replicable by setting either the Replicable or the ReplicableBool
property of the Database object. These properties don't
exist on the Database object until you create them and
append them to the Properties collection. After you've
made the database replicable, you can create one or more replicas
of it. The following example backs up a database, makes it replicable
by setting the ReplicableBool property to True,
and creates a replica by using the DAO MakeReplica method.
Note The
Replicable and ReplicableBool properties are functionally identical. The only difference between them is that the Replicable property setting is a string, and the ReplicableBool property setting is a Boolean value.
For more information about database replication and the DAO properties
and methods that you can use for replication, search DAO Help
for "replication."
The TableDef Object and the TableDefs
Collection
A TableDef object represents the stored definition of a
base table or a linked table in a Microsoft Jet workspace. The
TableDefs collection contains all stored TableDef objects
in a database. The following table shows the relationship between
the TableDef object and the TableDefs collection
and other objects and collections in a Microsoft Jet workspace.
Indexes collection
Properties collection
Creating a Table with Code
To create a table with DAO code, use the CreateTableDef
method of a Database object. After you've created a new
TableDef object, but before you append it to the database,
you must define one or more fields for the table. The following
example creates a table that contains some of the error codes
and strings used or reserved by Visual Basic in the Northwind
sample database.
Linking a Table to a Database
To use tables from an external data source in your database, you
can link them to your database. You can link tables that reside
in another Microsoft Jet database, or tables from other programs
and file formats, such as Microsoft Excel, dBASE, Microsoft FoxPro,
Paradox, or previous versions of Microsoft Jet. This is more efficient
than opening the external database directly, especially if the
table comes from an ODBC data source.
To link a table to your database, use the CreateTableDef
method to create a new table. Next, specify settings for the Connect
and SourceTableName properties of the TableDef object.
You can also set the Attributes property of the TableDef object to specify that the object has certain characteristics.
Finally, append the TableDef object to the TableDefs
collection.
For more information about the Connect, SourceTableName,
and Attributes properties, search DAO Help for the name
of the property.
The following example links a Microsoft Excel version 8.0 worksheet
to a database as a table.
Important Before
you run this code, make sure that the Microsoft Excel ISAM driver
(Msexcl35.dll) is installed on your system. If it's not, you need
to run Setup again to install it. The Microsoft Excel ISAM driver
enables Microsoft Excel 97 files to work with the Microsoft
Jet database engine. For more information about working with the
Microsoft Excel ISAM driver, search Microsoft Access Help for
"Microsoft Excel driver."
The Field Object and the Fields Collection
In a Microsoft Jet workspace, the Field object represents
a field in a table, query, index, relation, or recordset. The
Fields collection contains all Field objects associated
with a TableDef, QueryDef, Index, Relation,
or Recordset object. The following table shows the relationship
between the Field object and the Fields collection
and other objects and collections in a Microsoft Jet workspace.
Index object
QueryDef object
Recordset object
Relation object
The Fields collection is the default collection of a TableDef,
QueryDef, Index, Relation, or Recordset
object, which means that you don't need to explicitly refer to
the Fields collection. For example, the following code
fragment returns a reference to the LastName field in the Employees
table in the Northwind sample database.
In the Fields collection of a TableDef, QueryDef,
Index, or Relation object, the Field object
is a structural unit. It represents a column in a table with a
particular data type. If you're creating a database in Microsoft
Access, you can define fields for any of these objects and set
most of their properties in the Microsoft Access user interface,
rather than by programming with DAO.
In a Recordset object, a Field object contains data,
and you can use it to read data from a record or write data to
a record. You can't work with the fields in a Recordset object
in the Microsoft Access user interface; you must use DAO.
The Fields collection of a TableDef object contains
all of the fields defined for a particular table. For a QueryDef object, the Fields collection contains fields that
are included in the QueryDef object from one or more tables.
The Fields collection of an Index object includes
the one or more fields on which the index is defined.
For a Relation object, the Fields collection contains
the fields involved in a relationship. Typically, there are two
fields in the Fields collection of a Relation object.
One is the field that is the primary key in the table, specified
by the Table property of the Relation object; the
other is the field that is the corresponding foreign key in the
table, specified by the ForeignTable property of the Relation object.
The Fields collection of a Recordset object contains
the fields specified in the source argument
of the OpenRecordset method. The source
argument specifies the source of the records for the new Recordset object and can be a table name, a query name, or an SQL statement
that returns records.
The Value property of a Field object applies only
to a Field object in the Fields collection of a
Recordset object. The Value property returns the
value of the data stored in that field for the current record.
Because the Value property is the default property of a
Field object, and the Fields collection is the default
collection of a Recordset object, you can return the value
of a field without explicitly referring to either the Fields collection or the Value property. The following code
shows three ways you can refer to the Value property. It
prints the value of the LastName, FirstName, and Title fields
for the first record in a tabletype Recordset object
based on the Employees table.
The Index Object and the Indexes Collection
The Index object represents an index on a table in your
database in a Microsoft Jet workspace. The Indexes collection
contains all of the Index objects defined for a particular
table. The following table shows the relationship between the
Index object and the Indexes collection and other
objects and collections in a Microsoft Jet workspace.
Properties collection
An index speeds up searching and sorting on a table. You can improve
query performance in your database by indexing fields on both
sides of joins, fields that are sorted, or fields that are used
to specify criteria for a query. However, indexes add to the size
of your database, and they can slow performance when you update
data in indexed fields, or when you add or delete data. They can
also reduce the efficiency of multiuser applications. If you evaluate
your performance needs, you can add or omit indexes appropriately.
An index specifies the order in which records are accessed from
database tables in a tabletype Recordset object.
For example, suppose that you have an index on the LastName field
in the Employees table in the Northwind sample database. If you
create a tabletype Recordset object, then set the
Recordset object's Index property to the name of
the new index, the records returned by the Recordset object
will be ordered alphabetically by last name.
You create an index on one or more fields in the table. When you
create an index with DAO, you must create the field or fields
to be included in the index and append them to the Fields collection
of the Index object, as shown in the following example.
When you create an index, you can also impose certain restrictions
on the data contained in the fields that are indexed. For example,
if you want to designate a particular field in a table as the
primary key, you can create an Index object and set its
Primary and Unique properties to True. A
primary key is a special type of index. Each value in the field
designated as the primary key must be unique. A foreign key is
also an index, although it doesn't require special property settings.
Other indexes are neither primary nor foreign keys and serve only
to speed up searching and sorting operations.
Note If
you're designing a database in the Microsoft Access user interface,
you can add new indexes, change or delete existing indexes, and
set index properties in table Design view. To do so, click Indexes
on the View menu.
For more information about indexes, search DAO Help for "Index object."
The QueryDef Object and the QueryDefs
Collection
The QueryDef object represents a query in DAO. QueryDef objects can be saved with your database, or they can be temporary.
The QueryDefs collection contains all QueryDef objects
that are saved with your database and any temporary QueryDef objects that are currently open. The following table shows
the relationship between the QueryDef object and the QueryDefs collection and other objects and collections in a Microsoft
Jet workspace.
Parameters collection
Properties collection
Creating Persistent Queries
A query that's saved with your database is called a persistent
query. You can create persistent queries in Visual Basic by using
DAO, or you can create them in the Microsoft Access user interface.
To create a persistent query with DAO, use the CreateQueryDef
method of a Database object, as shown in the following
example.
Note In
an ODBCDirect workspace, QueryDef objects are always temporary.
Creating Temporary Queries
You can create a temporary QueryDef object when you need
to run an SQL statement but don't want to store a new QueryDef object in the database. A temporary QueryDef object
is not appended to the database and exists until the variable
that represents it goes out of scope.
The following example creates two temporary QueryDef objects
to return data from the Microsoft SQL Server Pubs sample database.
It first queries the table of titles in the Microsoft SQL Server
Pubs sample database and returns the title and title identifier
of the bestselling book. It then queries the table of authors
and instructs the user to send a bonus check to each author based
on his or her royalty share. The total bonus is $1,000 and each
author should receive a percentage of that amount.
This example uses ODBC through Microsoft Jet. You can apply the
same principles to create a temporary QueryDef object on
a Microsoft Jet database or an installable ISAM data source, or
in an ODBCDirect workspace.
The Parameter Object and the Parameters
Collection
A Parameter object represents a value supplied to a query.
The Parameters collection contains all of the Parameter objects defined for a QueryDef object. The following
table shows the relationship between the Parameter object
and the Parameters collection and other objects and collections
in a Microsoft Jet workspace.
When you want the user or the application to supply a value at
run time that limits the set of records returned by a query, you
can define parameters for the query. For example, you can create
a query on an Orders table that prompts the user to specify the
range of records to return based on a range of order dates.
To create a parameter query, use the SQL PARAMETERS declaration
to define parameters for the query. The syntax for the PARAMETERS
declaration is:
PARAMETERS name datatype [,
name datatype [, ...]]
The PARAMETERS declaration precedes the rest of the SQL statement
and is separated from the SQL statement by a semicolon (;). The
following SQL statement defines two parameters, Beginning OrderDate
and Ending OrderDate, whose datatype is DATETIME.
Each parameter that you define in the SQL statement is represented
by a Parameter object in the Parameters collection
of the QueryDef object based on that SQL statement. You
specify the value of a parameter by setting the Value property
of the Parameter object. The following example creates
a new parameter query.
You can call this function from the Debug window as follows:
The Relation Object and the Relations
Collection
The Relation object represents a relationship between fields
in tables and queries. The Relations collection contains
all stored Relation objects in a database. The following
table shows the relationship between the Relation object
and the Relations collection and other objects and collections
in a Microsoft Jet workspace.
Properties collection
You can use the Relation object to create, delete, or change
relationships between fields in tables and queries in your database.
You can use the properties of the Relation object to specify
the type of relationship, which tables supply the fields that
participate in the relationship, whether to enforce referential
integrity, and whether to perform cascading updates and deletes.
A Relation object has a Fields collection that contains
two fields, one in each of the tables in the relationship. The
fields that make up the relationship must be of the same data
type, and they must have common values. In most cases, a relationship
consists of a field that is the primary key in one table and a
foreign key in another table.
You use the Table and ForeignTable properties of
the Relation object to specify which tables take part in
the relation and how they are related. If you are creating a onetomany
relationship, it is important that you set these properties correctly.
In a onetomany relationship, the table on the "one"
side of the relationship is the table in which the field to be
joined is the primary key. The setting for the Table property
must be the name of this table. The table on the "many"
side of the relationship is the table in which the field to be
joined is the foreign key. The setting for the ForeignTable
property must be the name of this table.
For example, consider the relationship between the Employees table
and the Orders table in the Northwind sample database. The two
tables are joined on the EmployeeID field. In the Employees table,
this field is the primary key; all values in this field must be
unique. In the Orders table, the EmployeeID field is a foreign
key. The same value can occur more than once in this field. For
the Relation object that represents this relationship,
the value of the Table property is the table on the "one"
side of the relationship; the Employees table. The value of the
ForeignTable property is the table on the "many"
side of the relationship; the Orders table.
The following example shows how to create a Relation object
in Visual Basic. The procedure deletes the existing relationship
between the Employees table and the Orders table in the Northwind
sample database, then recreates it.
Note If
you're designing a database in Microsoft Access, you can view
and change the relationships in your database in the Relationships
window. In the Database window, click Relationships on the Tools menu.
For more information about Relation objects, search DAO
Help for "Relation object."
The Recordset Object and the Recordsets
Collection
The Recordset object represents a set of records within
your database. The Recordsets collection contains all open
Recordset objects. The following table shows the relationship
between the Recordset object and the Recordsets
collection and other objects and collections in a Microsoft Jet
workspace.
Properties collection
DAO offers five types of Recordset objects: tabletype,
dynasettype, snapshottype, forwardonlytype,
and dynamictype. Tabletype Recordset objects
are supported only in Microsoft Jet workspaces. Dynamictype
Recordset objects are available only in ODBCDirect workspaces.
For more information, see "DynamicType Recordset Objects"
later in the chapter.
The sections that follow discuss some characteristics of each
of the other four types of Recordset objects. For more
information about each type of Recordset object, search
DAO Help for the name of the particular type of Recordset object.
Note that you should always close a Recordset object after
you have finished working with it, and before you close the Database object in which the recordset was created. Use the Close
method to close a Recordset object.
TableType Recordset Objects
The tabletype Recordset object represents a base
table in your database. All of the fields and records in the table
are included in a tabletype Recordset object. You
can use a tabletype Recordset object to add, delete,
or change records in a table in a Microsoft Jet workspace. You
can open a tabletype Recordset object on base tables
in a Microsoft Jet database, but not on tables in ODBC data sources
or linked tables. You can also use the tabletype Recordset object with installable ISAM databases (such as FoxPro, dBASE,
or Paradox) to open tables directly, rather than linking them
to your database.
The RecordCount property of a tabletype Recordset object returns the number of records in the table. You can
return the value of the RecordCount property as soon as
you've created the recordset; you don't need to use the MoveLast
method to move to the end of the recordset.
The tabletype Recordset object can use the indexes
defined for the table. When you create a tabletype Recordset object, you can set the recordset's Index property
to the name of an index that is defined for the table. You can
then use the Seek method to search for a particular record
based on the ordering criteria specified by the index.
Note You
can't open a tabletype Recordset object on a linked table
from an external data source. Instead, you must use the OpenDatabase
method to open the external data source, and then open a tabletype
Recordset object.
To create a tabletype Recordset object, specify the
dbOpenTable constant for the type argument
of the OpenRecordset method. The following example creates
a tabletype Recordset object and then uses the Seek
method to locate a particular record and make that record the
current record.
DynasetType Recordset Objects
The dynasettype Recordset object represents the result
of a query on one or more tables. A dynasettype Recordset object is a dynamic set of records that you can use to add,
change, or delete records from an underlying database table or
tables. With a dynasettype Recordset object, you
can extract and update data in a multipletable join, including
linked tables from multiple databases. You can create a dynasettype
Recordset object in a Microsoft Jet workspace or an ODBCDirect
workspace. A dynasettype Recordset object on a remote data source
consists of a series of bookmarks. Each bookmark uniquely identifies
one record in the recordset. The actual data in the fields of
the recordset is not returned until you specifically refer to
the record that contains that data. Microsoft DAO uses the bookmark
to find the appropriate record and return the requested data.
To improve performance, Microsoft DAO returns only the records
that you explicitly refer to in your code; it doesn't necessarily
return data from every record in the recordset.
In order to return the value of the RecordCount property
for a dynasettype Recordset object, you must first
use the MoveLast method to move to the end of the recordset.
Moving to the end of the recordset retrieves all of the records
in the recordset.
A dynasettype Recordset object may be updatable,
but not all fields can be updated in all dynasettype Recordset objects. To determine whether you can update a particular
field, check the setting of the DataUpdatable property
of the Field object.
A dynasettype Recordset object may not be updatable
if:
To create a dynasettype Recordset object, specify
the dbOpenDynaset constant for the type
argument of the OpenRecordset method, as shown in the following
example.
SnapshotType Recordset Objects
A snapshottype Recordset object is a static set of
records that represents the results of a query. A snapshottype
Recordset object includes all values for all the requested
fields in your query, whether you refer to them in code or not.
A snapshottype Recordset object requires fewer resources
than the dynasettype Recordset object, but the data
in a snapshottype Recordset object cannot be updated.
As you move through a snapshottype Recordset object
for the first time, all data is copied first into memory and then,
if the recordset is large, into a temporary Microsoft Jet database
on the user's computer. You can scroll forward and backward through
the resulting set of data.
To create a snapshottype Recordset object, specify
the dbOpenSnapshot constant for the type
argument of the OpenRecordset method.
ForwardOnlyType Recordset Objects
A forwardonlytype Recordset object is identical
to a snapshot, except that you can only scroll forward through
its records. This improves performance in situations where you
only need to make a single pass through a result set.
When working with a forwardonlytype Recordset
object, you cannot use the MovePrevious or MoveFirst
methods, or the Move method with a negative integer for
the rows argument. In a forwardonlytype
Recordset object, only one record exists at any given time.
Therefore, you cannot use the MoveLast method because it
implies that you have a set of records. Forwardonlytype
Recordset objects offer less flexibility than other Recordset objects, but they usually provide the greatest speed.
To create a forwardonlytype Recordset object,
specify the dbOpenForwardOnly constant for the type
argument of the OpenRecordset method.
The Group Object and the Groups Collection
The Group object represents a group of user accounts that
have common access permissions in a particular workspace. The
Groups collection contains all Group objects in
a workspace or a user account. The following table shows the relationship
between the Group object and the Groups collection
and other objects and collections in a Microsoft Jet workspace.
Properties collection
Users collection User object
You can use the Group object, along with the User,
Container, Document, and Workspace objects,
to secure your database. The Group object represents a
group of user accounts, and the User object represents
an individual user account. Users can be members of groups. When
you establish security in your database, you secure a particular
object or set of objects by specifying what type of permissions
a user or group has for that object. If a group has certain permissions
for an object, all users in the group have the same permissions.
Conversely, if a user has permissions for an object, the group
to which that user belongs has the same permissions.
Note The
easiest way to secure your database is through the Microsoft Access
user interface. From Microsoft Access, you can manage user and
group accounts and assign permissions for objects with relative
ease. For more information about securing a database in Microsoft
Access, search Microsoft Access Help for "security,"
or see Chapter 14, "Securing Your Application,"
in Building Applications with Microsoft Access 97.
Both a Workspace object and a User object have a
Groups collection. When you create a Group object,
you should first append it to the Groups collection of
a Workspace object. This notifies Microsoft Jet that the
group exists.
After you've created a group and added it to the Groups
collection of the Workspace object, you need to specify
which users belong to that group. To do so, you can append the
new Group object to the Groups collection of a User object. In this way, you specify that a particular user belongs
to this group. Alternatively, you can append a User object
to the Users collection in a Group object to give
a particular user account the permissions held by that group.
In either case, the existing Group object must already
be a member of the Groups collection of the current Workspace object.
The following example creates a new group, the Managers group,
and appends it to the Groups
collection of the default
workspace.
After you've run this example, the Managers group exists, but
no user accounts belong to it. The example in the following section
adds user accounts to the Managers group.
The User Object and the Users Collection
The User object represents a user account with particular
access permissions. The Users collection contains all User objects in a given workspace or group. The following table
shows the relationship between the User object and the
Users collection and other objects and collections in a
Microsoft Jet workspace.
Properties collection
User objects
Group object
Like the Groups collection, the Users collection
is a member of a Workspace object. Each User object
in the Users collection of a Workspace object also
has a Groups collection, in the same way that each Group object in the Groups collection of a Workspace object
has a Users collection. To make a user a member of a particular
group, you can append a User object to the Users collection
of that Group object. You can achieve the same result by
appending the Group object to the Groups collection
of that User object. In either case, the existing User object must already be a member of the Users collection
of the current Workspace object.
The following example creates a new User object and appends
it to the Users collection of the default workspace. Next,
it appends the User object to the Users collection
of the Managers group created in the previous example. Note that
because the User object doesn't already exist in the Users collection of the Group object, you must use the CreateUser
method a second time to create the object there. However, you
don't need to specify the pid and password
arguments a second time.
The Container Object and the Containers
Collection
The Container object represents a particular set of objects
in a database for which you can assign permissions in a secure
workgroup. The Containers collection contains all the Container objects in the database. The following table shows the relationship
between the Container object and the Containers collection
and other objects and collections in a Microsoft Jet workspace.
Properties collection
DAO provides three types of Container objects; every database
contains at least these three Container objects. The following
table describes the types of Container objects provided
by DAO.
Each Container object can contain a Documents collection.
The Documents collection contains individual Document objects, each of which represents a document in your database.
For more information about Document objects, see the following
section, "The Document Object and the Documents Collection."
In addition to the Container objects provided by DAO, an
application may define its own Container objects. For example,
the following table lists the Container objects defined
by Microsoft Access.
You use Container objects to establish permissions on a
set of objects for a user or group. The following example establishes
permissions for a group, and any users that belong to it, for
the Tables container. To establish permissions, the function first
sets the UserName property of the Tables container to the
name of a group, then sets the Permissions property to
the appropriate permissions.
The Document Object and the Documents
Collection
The Document object represents an individual object in
a database for which you can assign permissions in a secure workgroup.
The Documents collection contains all of the Document objects in a given Container object. The following
table shows the relationship between the Container object
and the Containers collection and other objects and collections
in a Microsoft Jet workspace.
The following table describes the Document objects provided
by DAO. It lists the type of object each Document
object describes, the name of its Container object, and
what type of information it contains.
Other applications can define additional Document objects.
For example, the following table lists the Document objects
defined by Microsoft Access.
The following example establishes permissions for a particular
user on all the existing Table Document objects in the
Documents collection of the Tables Container object.
Table Document objects represent either tables or queries.
The Properties Collection
Most DAO objects contain a Properties collection. Each
Property object in the Properties collection corresponds
to a property of the object. You can use an object's Properties collection either to determine which properties apply to a
particular object or to return their settings. For example, the
following procedure loops through the properties that apply to
the Database object, which represents the current database.
The procedure displays the name of each property in the Debug
window.
Some properties of DAO objects don't automatically exist in the
Properties collection for that object. Before you can set
a property of this type, you must create a Property object
to represent the property and append the new Property object
to the Properties collection. After you create the property
and append it to the collection, you can set or read it as you
would any other property.
When you're writing code that uses this type of property, it's
a good idea to implement error handling in case the property does
not yet exist in the collection. The following function is a generic
procedure that you can use to set any property that doesn't automatically
exist in an object's Properties collection. It implements
error handling. The first time you call the procedure, an error
occurs because the property does not yet exist within the Properties collection. Within the error handler, the procedure creates
the new Property object and appends it to the collection.
The next time you call the procedure, the error does not occur
because the property already exists, and the property is set with
the value you've specified.
To set the ReplicableBool property of a Database object,
you can call the preceding function as follows.
The SetProperty function shown in the previous example is a generic
procedure that you can use to set any property, including those
that must first be appended to the Properties collection.
You can compare this function to the ReplicateDatabase function
shown earlier in this chapter, in "Creating Database Replicas
with DAO." Both functions achieve the same end, but the SetProperty
function can be used to set any property, while the ReplicateDatabase
function sets only the ReplicableBool property.
Each time you set or read a property that doesn't automatically
exist in the Properties collection for an object, you must
refer to the Properties collection explicitly. For example,
each time you refer to the ReplicableBool property after
it has been set, you must refer to it within the Properties collection, as shown in the following example.
Some applications define their own properties for DAO objects.
For example, Microsoft Access defines properties for DAO TableDef,
QueryDef, Field, and Document objects. If
you're working with a database that has been opened in Microsoft
Access, some of these properties may be defined for DAO objects.
For more information about the Properties collection, search
DAO Help for "properties, collection" and "CreateProperty
method."
Private Sub CauseODBCError()
Dim dbs As Database, errObj As Error
On Error GoTo Err_CauseODBCError
Set dbs = OpenDatabase("", 0, 0, "ODBC;UID=sa;PWD=;DATABASE=Pubs;DSN=Publishers")
dbs.Execute "INSERT INTO SomeTable VALUES (1,2,3)", dbSQLPassThrough
Exit Sub
Err_CauseODBCError:
For Each errObj In Errors
Debug.Print errObj.Number, errObj.Description
Next
Resume Next
End Sub
Object or collection
Is contained by
Contains Database object
Databases collection
Containers collection
Databases collection
Workspace object
Database objects
Function RetrieveRecordset(strDbName As String, strSource As String) As Boolean
Dim dbs As Database
Dim rst As Recordset
On Error GoTo Err_RetrieveRecordset
Set dbs = OpenDatabase(strDbName)
Set rst = dbs.OpenRecordset(strSource, dbOpenDynaset)
' Perform some operation with recordset.
.
.
.
RetrieveRecordset = True
Exit_RetrieveRecordset:
rst.Close
dbs.Close
Exit Function
Err_RetrieveRecordset:
MsgBox "Error " & Err & ": " & Err.Description
RetrieveRecordset = False
Resume Exit_RetrieveRecordset
End Function
Dim dbs As Database
Set dbs = CurrentDb
Debug.Print dbs.Name
Function ReplicateDatabase(strDBName As String) As Boolean
Dim dbs As Database, prp As Property
Dim strBackup As String, strReplica As String
Const conPropNotFound As Integer = 3270
On Error GoTo Err_ReplicateDatabase
If InStr(strDBName, ".mdb") > 0 Then
strBackup = Left(strDBName, Len(strDBName) - 4)
Else
strBackup = strDBName
End If
strReplica = strBackup & "Replica" & ".mdb"
If MsgBox("Make backup copy of file?", vbOKCancel) = vbOK
Then
strBackup = strBackup & ".bak"
FileCopy strDBName, strBackup
MsgBox "Copied file to " & strBackup
End If
Set dbs = OpenDatabase(strDBName, True)
dbs.Properties("ReplicableBool") = True
dbs.MakeReplica strReplica, "Replica of " & strDBName
MsgBox "Created replica '" & strReplica & "'."
dbs.Close
ReplicateDatabase = True
Exit_ReplicateDatabase:
Exit Function
Err_ReplicateDatabase:
If Err = conPropNotFound Then
Set prp = dbs.CreateProperty("ReplicableBool", dbBoolean, True)
dbs.Properties.Append prp
Resume
Next
Else
MsgBox "Error " & Err & ": " & Err.Description
End If
ReplicateDatabase = False
Resume Exit_ReplicateDatabase
End Function
Object or collection
Is contained by
Contains TableDef object
TableDefs collection
Fields collection
TableDefs collection
Database object
TableDef objects
Function CreateErrorsTable() As Boolean
Dim dbs As Database, tdf As TableDef, fld As Field, idx As Index
Dim rst As Recordset, intCode As Integer, strErr As String
Const conAppObjErr = "Application-defined or object-defined error"
' Create Errors table with ErrorCode and ErrorString fields.
Set dbs = CurrentDb
On Error Resume Next
' Delete any existing Errors table.
dbs.TableDefs.Delete "Errors"
On Error GoTo Error_CreateErrorsTable
' Create table.
Set tdf = dbs.CreateTableDef("Errors")
' Create fields.
Set fld = tdf.CreateField("ErrorCode", dbInteger)
tdf.Fields.Append fld
Set fld = tdf.CreateField("ErrorString", dbMemo)
tdf.Fields.Append fld
dbs.TableDefs.Append tdf
' Create index.
Set idx = tdf.CreateIndex("ErrorCodeIndex")
Set fld = idx.CreateField("ErrorCode")
With idx
.Primary = True
.Unique = True
.Required = True
End With
idx.Fields.Append fld
tdf.Indexes.Append idx
' Open recordset on Errors table.
Set rst = dbs.OpenRecordset("Errors")
' Set recordset's index.
rst.Index = "ErrorCodeIndex"
' Show hourglass pointer.
DoCmd.Hourglass True
' Loop through error codes.
For intCode = 1 To 32767
On Error Resume Next
strErr = ""
' Attempt to raise each error.
Err.Raise intCode
' Check whether error is VBA, DAO, or Access error.
' If error is not a VBA error, the Description property
' of the Err object contains "Application-defined or object-defined error".
If Err.Description <> conAppObjErr Then
strErr = Err.Description
' Use AccessError method to return descriptive string for
' DAO and Access errors.
ElseIf AccessError(intCode) <> conAppObjErr Then
strErr = AccessError(intCode)
End If
' If error number has associated descriptive string, add to table.
If Len(strErr) > 0 Then
' Add new record to recordset.
rst.AddNew
' Add error number to table.
rst!errorcode = intCode
' Add descriptive string to table.
rst!ErrorString.AppendChunk strErr
' Update record.
rst.Update
End If
Next intCode
DoCmd.Hourglass False
' Close recordset.
rst.Close
MsgBox "Errors table created."
' Show new table in Database window.
RefreshDatabaseWindow
CreateErrorsTable = True
Exit_CreateErrorsTable:
Exit Function
Error_CreateErrorsTable:
MsgBox Err & ": " & Err.Description
CreateErrorsTable = False
Resume Exit_CreateErrorsTable
End Function
Function LinkExcelTable() As Boolean
Dim dbs As DAO.Database, tdf As DAO.TableDef
Const errNoISAM As Integer = 3170
Const conPath As String = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
On Error GoTo Err_LinkExcelTable
' Return a reference to Northwind database.
Set dbs = OpenDatabase(conPath)
' Create new TableDef object.
Set tdf = dbs.CreateTableDef("LinkedTable")
' Specify range that is source table.
tdf.SourceTableName = "DataRange"
' Specify connect string.
tdf.Connect = "EXCEL 8.0; DATABASE=C:\My Documents\XLTable.xls"
' Append new TableDef object.
dbs.TableDefs.Append tdf
LinkExcelTable = True
Exit_LinkExcelTable:
Exit Function
Err_LinkExcelTable:
If Err = errNoISAM Then
Dim strErr As String
strErr = Err & ": " & Err.Description
strErr = strErr _
& "You may not have the ISAM driver installed properly on your computer, " _
& "or you may have specified the Connect string incorrectly." _
& " Check the Connect string and the ISAM driver."
MsgBox strErr, vbOKOnly, "Error!"
Else
MsgBox "Error " & Err & ": " & Err.Description
End If
End Function
Object or collection
Is contained by
Contains Field object
Fields collection
Properties collection
Fields collection
TableDef object
Field objects
Dim dbs As Database, tdf As TableDef, fld As Field
Const conPath As String = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
Set dbs = OpenDatabase(conPath)
Set tdf = dbs.TableDefs("Employees")
Set fld = tdf!LastName
Dim dbs As Database, rst As Recordset
Const conPath As String = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
Set dbs = OpenDatabase(conPath)
Set rst = dbs.OpenRecordset("Employees")
' Explicitly reference Fields collection and Value property.
Debug.Print rst.Fields("LastName").Value
' Implicitly reference Fields collection, explicitly reference Value property.
Debug.Print rst!FirstName.Value
' Implicitly reference Fields collection and Value property.
Debug.Print rst!Title
Object or collection
Is contained by
Contains Index object
Indexes collection
Fields collection
Indexes collection
TableDef object
Index objects
Sub SeekRecord()
Const conPath As String = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
Dim dbs As Database, tdf As TableDef, idx As Index
Dim fld As Field, fldLast As Field, fldFirst As Field
Dim rst As Recordset
' Return a reference to Northwind database.
Set dbs = DBEngine(0).OpenDatabase(conPath)
' Return a reference to Employees table.
Set tdf = dbs.TableDefs("Employees")
' Create new index on LastName and FirstName fields.
Set idx = tdf.CreateIndex("FirstLastName")
' Create fields in Fields collection of new index.
Set fldLast = idx.CreateField("LastName", dbText)
Set fldFirst = idx.CreateField("FirstName", dbText)
' Append Field objects.
idx.Fields.Append fldLast
idx.Fields.Append fldFirst
' Set Required property.
idx.Required = True
' Append new Index object.
tdf.Indexes.Append idx
' Open table-type recordset.
Set rst = dbs.OpenRecordset("Employees")
' Set Index property of Recordset object.
rst.Index = idx.Name
' Perform seek operation.
rst.Seek "=", "King", "Robert"
' Print values of all fields except Photo.
For Each fld In rst.Fields
If fld.Type <> dbLongBinary Then
Debug.Print fld
End If
Next fld
End Sub
Object or collection
Is contained by
Contains QueryDef object
QueryDefs collection
Fields collection
QueryDefs collection
Database object
QueryDef objects
You don't need to append a QueryDef object to the QueryDefs collection. If you specify a value for the name
argument of the CreateQueryDef method in a Microsoft Jet
workspace, DAO automatically appends the new QueryDef object
to the QueryDefs collection of the Database object.
If you specify a zerolength string ("") for the
name argument, DAO creates a temporary QueryDef object.
Const conPath As String = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
Dim dbs As Database, qdf As QueryDef, rst As Recordset
Dim strSQL As String
strSQL = "SELECT FirstName, LastName, HireDate FROM Employees " _
& "WHERE Title = 'Sales Representative' ORDER BY HireDate;"
Set dbs = OpenDatabase(conPath)
Set qdf = dbs.CreateQueryDef("Sales Representatives", strSQL)
Set rst = qdf.OpenRecordset
Function DetermineBonuses()
Const conPath As String = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
Dim dbsCurrent As Database, qdfBestSellers As QueryDef
Dim qdfBonusEarners As QueryDef, rstTopSeller As Recordset
Dim rstBonusRecipients As Recordset, strAuthorList As String
' Open database from which QueryDef objects can be created.
Set dbsCurrent = OpenDatabase(conPath)
' Create temporary QueryDef object to retrieve data from
' Microsoft SQL Server database.
Set qdfBestSellers = dbsCurrent.CreateQueryDef("")
qdfBestSellers.Connect = "ODBC;DATABASE=Pubs;UID=sa;PWD=;DSN=Publishers"
qdfBestSellers.SQL = "SELECT title, title_id FROM titles ORDER BY ytd_sales DESC;"
Set rstTopSeller = qdfBestSellers.OpenRecordset()
rstTopSeller.MoveFirst
' Create temporary QueryDef to retrieve data from SQL Server database
' based on results from first query.
Set qdfBonusEarners = dbsCurrent.CreateQueryDef("")
qdfBonusEarners .Connect = "ODBC;DATABASE=Pubs;UID=sa;PWD=;DSN=Publishers"
qdfBonusEarners.SQL = "SELECT * FROM titleauthor WHERE title_id = '" & _
rstTopSeller!title_id & "'"
Set rstBonusRecipients = qdfBonusEarners.OpenRecordset()
' Build string containing names of authors to whom bonuses are owed.
Do While Not rstBonusRecipients.EOF
strAuthorList = strAuthorList & rstBonusRecipients!au_id & ": $" & _
CStr(10* rstBonusRecipients!royaltyper) & vbCr
rstBonusRecipients.MoveNext
Loop
' Display results.
MsgBox "Please send a check to the following " & _
"authors in the amounts shown: " & vbCr & _
strAuthorList & " for outstanding sales of " & _
rstTopSeller!Title & "."
rstBonusRecipients.Close
rstTopSeller.Close
dbsCurrent.Close
End Function
Object or collection
Is contained by
Contains Parameter object
Parameters collection
Properties collection
Parameters collection
QueryDef object
Parameter objects
For a list of data types you can use for parameters,
search Microsoft Access Help for "data types, SQL."
PARAMETERS [Beginning OrderDate] DATETIME,[Ending OrderDate] DATETIME;
SELECT * FROM Orders
WHERE (OrderDate Between [Beginning OrderDate] And [Ending OrderDate]);
Function NewParameterQuery(dteStart As Date, dteEnd As Date) As Boolean
Dim dbs As Database, qdf As QueryDef, rst As Recordset
Dim strSQL As String
On Error Resume Next
' Return reference to current database.
Set dbs = CurrentDb
' Construct SQL string.
strSQL = "PARAMETERS [Beginning OrderDate] DateTime, " _
& "[Ending OrderDate] DateTime; SELECT * FROM Orders " & _
"WHERE (OrderDate Between [Beginning OrderDate] " _
& "And [Ending OrderDate]);"
' Delete query if it already exists.
dbs.QueryDefs.Delete "ParameterQuery"
On Error GoTo Err_NewParameterQuery
' Create new QueryDef object.
Set qdf = dbs.CreateQueryDef("ParameterQuery", strSQL)
' Supply values for parameters.
If dteStart > dteEnd Then
MsgBox "Start date is later than end date."
Exit Function
End If
qdf.Parameters("Beginning OrderDate") = dteStart
qdf.Parameters("Ending OrderDate") = dteEnd
' Open recordset on QueryDef object.
Set rst = qdf.OpenRecordset
rst.MoveLast
MsgBox "Query returned " & rst.RecordCount & " records."
NewParameterQuery = True
Exit_NewParameterQuery:
rst.Close
Set dbs = Nothing
Exit Function
Err_NewParameterQuery:
MsgBox "Error " & Err & ": " & Err.Description
NewParameterQuery = False
Resume Exit_NewParameterQuery
End Function
Note If
you're creating a database in Microsoft Access, you can define
parameters for a query in query Design view. For more information,
search Microsoft Access Help for "parameter queries."
? NewParameterQuery(#6-30-95#, #6-30-96#)
Object or collection
Is contained by
Contains Relation object
Relations collection
Fields collection
Relations collection
Database object
Relation objects
Function NewRelation() As Boolean
Dim dbs As Database
Dim fld As Field, rel As Relation
Const conPath As String = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
On Error GoTo Err_NewRelation
' Return reference to current database.
Set dbs = OpenDatabase(conPath)
' Find existing EmployeesOrders relation.
For Each rel In dbs.Relations
If rel.Table = "Employees" And rel.ForeignTable = "Orders" Then
' Prompt user before deleting relation.
If MsgBox(rel.Name & " already exists. " & vbCrLf _
& "This relation will be deleted and re-created.", vbOK) = vbOK Then
dbs.Relations.Delete rel.Name
' If user chooses Cancel, exit procedure.
Else
Exit Function
End If
End If
Next rel
' Create new relationship and set its properties.
Set rel = dbs.CreateRelation("EmployeesOrders", "Employees", "Orders")
' Set Relation object attributes to enforce referential integrity.
rel.Attributes = dbRelationDeleteCascade + dbRelationUpdateCascade
' Create field in Fields collection of Relation object.
Set fld = rel.CreateField("EmployeeID")
' Provide name of foreign key field.
fld.ForeignName = "EmployeeID"
' Append field to Relation object and Relation object to database.
rel.Fields.Append fld
dbs.Relations.Append rel
MsgBox "Relation '" & rel.Name & "' created."
Set dbs = Nothing
NewRelation = True
Exit_NewRelation:
Exit Function
Err_NewRelation:
MsgBox "Error " & Err & ": " & Err.Description
NewRelation = False
Resume Exit_NewRelation
End Function
Object or collection
Is contained by
Contains Recordset object
Recordsets collection
Fields collection
Recordsets collection
Database object
Recordset objects
Function ReturnEmployeesRecord(strKey As String) As Boolean
Dim dbs As Database, rst As Recordset
Const conPath As String = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
On Error GoTo Err_ReturnEmployeesRecord
' Return reference to Northwind database.
Set dbs = OpenDatabase(conPath)
' Open table-type recordset on Employees table.
Set rst = dbs.OpenRecordset("Employees", dbOpenTable)
' Set Index property of recordset.
rst.Index = "LastName"
' Perform seek operation.
rst.Seek "=", strKey
' Check whether match is found.
If rst.NoMatch = False Then
' Print values of fields in first record found.
Debug.Print rst!EmployeeID, rst!FirstName & " " & rst!LastName, rst!Title
ReturnEmployeesRecord = True
Else
ReturnEmployeesRecord = False
End If
Exit_ReturnEmployeesRecord:
' Close recordset and database.
rst.Close
dbs.Close
Exit Function
Err_ReturnEmployeesRecord:
MsgBox "Error " & Err & ": " & Err.Description
ReturnEmployeesRecord = False
Resume Exit_ReturnEmployeesRecord
End Function
Sub PrintHireDates()
Dim dbs As Database, rst As Recordset
Dim strSQL As String
Const conPath = "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
' Open database and return reference to Database object.
Set dbs = DBEngine.Workspaces(0).OpenDatabase(conPath)
' Initialize SQL string.
strSQL = "SELECT FirstName, LastName, HireDate FROM Employees " & _
"WHERE HireDate <= #1-1-93# ORDER BY HireDate;"
' Open dynaset-type recordset.
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
' Print records in recordset.
Do Until rst.EOF
Debug.Print rst!FirstName, rst!LastName, rst!HireDate
rst.MoveNext
Loop
' Close recordset and database.
rst.Close
dbs.Close
End Sub
Object or collection
Is contained by
Contains Group object
Groups collection
Group objects
Groups collection
Workspace object
Group objects
Function AddNewGroup() As Boolean
Dim wrk As Workspace, grp As Group
Const conAccountExists As Integer = 3390
On Error GoTo Err_AddNewGroup
Set wrk = DBEngine.Workspaces(0)
Set grp = wrk.CreateGroup("Managers", "123abc")
wrk.Groups.Append grp
AddNewGroup = True
Exit_AddNewGroup:
Exit Function
Err_AddNewGroup:
If Err <> conAccountExists Then
MsgBox "Error " & Err & ": " & Err.Description
AddNewGroup = False
Else
AddNewGroup = True
End If
Resume Exit_AddNewGroup
End Function
Object or collection
Is contained by
Contains User object
Users collection
Groups collection
Users collection
Workspace object
User objects
Function AddNewUser() As Boolean
Dim wrk As Workspace, grp As Group, usr As User
Const conAccountExists As Integer = 3390
On Error GoTo Err_AddNewUser
Set wrk = DBEngine.Workspaces(0)
Set usr = wrk.CreateUser("Joe Manager", "efg456", "")
wrk.Users.Append usr
Set grp = wrk.Groups("Managers")
Set usr = grp.CreateUser("Joe Manager")
grp.Users.Append usr
AddNewUser = True
Exit_AddNewUser:
Exit Function
Err_AddNewUser:
If Err <> conAccountExists Then
MsgBox "Error " & Err & ": " & Err.Description
AddNewUser = False
Else
AddNewUser = True
End If
Resume Exit_AddNewUser
End Function
Object or collection
Is contained by
Contains Container object
Containers collection
Documents collection
Containers collection
Database object
Container objects
Container name
Contains information about
Databases Saved databases
Tables Saved tables and queries
Relationships Saved relationships
Container name
Contains information about
Forms Saved forms
Modules Saved modules
Reports Saved reports
Scripts Saved macros
To establish permissions for the Managers group on the Tables
container, you can call the SetGroupPermissions function as follows.
Function SetGroupPermissions(strGroupName As String) As Boolean
Dim dbs As Database, ctr As Container
Const conPath As String = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
On Error GoTo Err_SetGroupPermissions
Set dbs = DBEngine(0).OpenDatabase(conPath)
' Return a reference to the Databases container.
Set ctr = dbs.Containers("Databases")
' Set UserName property to name of group.
ctr.UserName = strGroupName
' Set permissions for the group on the Databases container.
ctr.Permissions = dbSecDBOpen
' Return a reference to the Tables container.
Set ctr = dbs.Containers("Tables")
' Set UserName property to name of group.
ctr.UserName = strGroupName
' Set permissions for the group on the Tables container.
ctr.Permissions = dbSecRetrieveData or dbSecInsertData or _
dbSecReplaceData or dbSecDeleteData
SetGroupPermissions = True
Exit_SetGroupPermissions:
Exit Function
Err_SetGroupPermissions:
MsgBox "Error " & Err & ": " & Err.Description
SetGroupPermissions = False
Resume Exit_SetGroupPermissions
End Function
Sub SetManagerPermissions()
If SetGroupPermissions("Managers") = True Then
MsgBox "Permissions for Managers group set successfully."
Else
MsgBox "Permissions for Managers group not set."
End If
End Sub
Object or collection
Is contained by
Contains Document object
Documents collection
Properties collection
Documents
collection
Container object
Document objects
Document Container
Contains information about
Database Databases
Saved database Table or query Tables
Saved table or query
Relationship Relationships
Saved relationship
Document Container
Contains information about
Form Forms
Saved form Macro Scripts
Saved macro Module Modules
Saved module Report Reports
Saved report SummaryInfo Databases
Database document summary
UserDefined Databases
User-defined properties
Function SetPermissionsOnDocument(strUserName As String) As Boolean
Dim dbs As Database, ctr As Container, doc As Document
Const conPath As String = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
On Error GoTo Err_SetPermissionsOnDocument
' Return reference to Northwind sample database.
Set dbs = DBEngine(0).OpenDatabase(conPath)
' Return reference to Tables container.
Set ctr = dbs.Containers("Tables")
' Enumerate through documents in Tables container.
For Each doc In ctr.Documents
' Set UserName property to name of user.
doc.UserName = strUserName
' Set permissions for that user on the document.
doc.Permissions = dbSecRetrieveData or dbSecInsertData or _
dbSecReplaceData or dbSecDeleteData
Next doc
SetPermissionsOnDocument = True
Exit_SetPermissionsOnDocument:
Exit Function
Err_SetPermissionsOnDocument:
MsgBox "Error " & Err & ": " & Err.Description
SetPermissionsOnDocument = False
Resume Exit_SetPermissionsOnDocument
End Function
Sub DisplayProperties()
Dim dbs As Database, prp As Property
Const conPath As String = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
' Open database and return reference.
Set dbs = OpenDatabase(conPath)
Debug.Print "Current Database Properties"
' Enumerate Properties collection.
For Each prp In dbs.Properties
Debug.Print prp.Name
Next prp
dbs.Close
End Sub
Function SetProperty(obj As Object, strName As String, _
intType As Integer, varSetting As Variant) As Boolean
Dim prp As Property
Const conPropNotFound As Integer = 3270
On Error GoTo Error_SetProperty
' Explicitly refer to Properties collection.
obj.Properties(strName) = varSetting
SetProperty = True
Exit_SetProperty:
Exit Function
Error_SetProperty:
If Err = conPropNotFound Then
' Create property, denote type, and set initial value.
Set prp = obj.CreateProperty(strName, intType, varSetting)
' Append Property object to Properties collection.
obj.Properties.Append prp
obj.Properties.Refresh
SetProperty = True
Resume Exit_SetProperty
Else
MsgBox Err & ": " & vbCrLf & Err.Description
SetProperty = False
Resume Exit_SetProperty
End If
End Function
Sub ReplicateDatabase()
Dim dbs As Database
Const conPath As String = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
Set dbs = OpenDatabase(conPath, True)
If SetProperty(dbs, "ReplicableBool", dbBoolean, True) Then
Debug.Print "Database replicated successfully."
Else
Debug.Print "Database not replicated."
End If
End Sub
You can also use the SetProperty function shown in the previous
example to define custom properties on DAO objects. For example,
you may want to define a property that stores the name of the
user who last modified a particular table. When you set or read
a custom property, you must refer to the Properties collection
explicitly, as shown in the previous examples.
Dim dbs As Database
Const conPath As String = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
Set dbs = OpenDatabase(conPath)
Debug.Print dbs.Properties("ReplicableBool")
When you're working with an ODBC data source, you'll need to decide whether you should use ODBC with Microsoft Jet, ODBCDirect, or both. This section discusses the advantages of both ODBC with Microsoft Jet and ODBCDirect. It also explains how to register an ODBC data source, whether you're working with a Microsoft Jet workspace or with an ODBCDirect workspace.
Accessing ODBC Data with Microsoft Jet
The following capabilities are supported in Microsoft Jet workspaces,
but not in ODBCDirect workspaces:
Accessing ODBC Data with ODBCDirect
With ODBCDirect, you can access server data by using the existing
DAO object model directly on top of the ODBC application programming
interface (API). ODBCDirect implements a thin code layer over
the ODBC API that establishes connections, creates cursors, and
runs complex procedures using minimal workstation resources, without
going through Microsoft Jet. ODBCDirect offers the following advantages:
Note You
can't perform DDL operations with DAO in an ODBCDirect workspace,
but you can run SQL DDL statements to modify the structure of
the database.
Registering an ODBC Data Source
Before you can use ODBC in a Microsoft Jet workspace or in an
ODBCDirect workspace, you must register the ODBC data source.
Registering the data source stores information about the data
source in the Windows Registry and makes this information available
to applications. You can register a data source from the ODBC
data source manager or from Visual Basic.
To
register a SQL Server data source by using the ODBC data source
manager
Note This
procedure describes the steps for registering a Microsoft SQL
Server data source. The steps for registering other ODBC data
sources may vary because each data source driver requires a different
set of information. If the dialog box for the data source you
selected has values not described in the preceding steps, click
the Help button for more information.
In some cases, you may want to register the data source in Visual
Basic code instead of relying on users to register it with the
ODBC data source manager. To do this, use the RegisterDatabase
method of the DBEngine object. The following example registers
a data source named Pubs.
Function RegisterDB() As Boolean
Dim str As String
On Error GoTo Err_RegisterDB
' Build keywords string.
str = "Description=SQL Server on Server Publishers" & _
vbCr & "OemToAnsi=No" & _
vbCr & "Network=(Default)" & _
vbCr & "Address=(Default)" & _
vbCr & "Server=Publishers" & _
vbCr & "Database=Pubs"
' Register database.
DBEngine.RegisterDatabase "Pubs", "SQL Server", True, str
RegisterDB = True
Exit_RegisterDB:
Exit Function
Err_RegisterDB:
MsgBox "Error " & Err & ": " & Err.Description
RegisterDB = False
Resume Exit_RegisterDB
End Function
The object model for an ODBCDirect workspace includes a subset of the objects in a Microsoft Jet workspace, with the addition of a new object, the Connection object. The following diagram shows the object model for ODBCDirect workspaces; the subsequent sections describe the objects themselves, to the extent that they differ from the objects in the Microsoft Jet object model.
The DBEngine Object
The DBEngine object contains both Microsoft Jet and ODBCDirect
workspaces. As mentioned earlier in this chapter, the DefaultType
property of the DBEngine object determines what type of
Workspace object is created by default when you use the
CreateWorkspace method. If you set the DefaultType
property to dbUseODBC, then the default workspace is an
ODBCDirect workspace. When you're creating a workspace, you can
override the setting for this property by specifying either dbUseJet
or dbUseODBC as the type argument of
the CreateWorkspace method. For example, if the DefaultType
property is set to dbUseJet and you want to create an ODBCDirect
workspace, specify the dbUseODBC constant as the type
argument of the CreateWorkspace method. Conversely, if
the DefaultType property is set to dbUseODBC and
you want to create a Microsoft Jet workspace, specify the dbUseJet
constant as the type argument of the CreateWorkspace
method.
Note If
you're programming in Microsoft Access, avoid setting the DefaultType
property to dbUseODBC. Because Microsoft Access uses DAO and Microsoft
Jet for many types of operations, setting the DefaultType property
to dbUseODBC may cause unexpected results.
The following example creates an ODBCDirect workspace.
Dim wrkODBC As Workspace
Set wrkODBC = DBEngine.CreateWorkspace("NewODBCWrk", "Admin", "", dbUseODBC)
Because you can use both Microsoft Jet and ODBCDirect workspaces in your code, you may need to determine the type of a Workspace object after it is created. You can do this by using the Type property of the Workspace object. The Type property is readonly once it is set and returns either dbUseJet or dbUseODBC.
The Workspace Object and the Workspaces
Collection
The Workspace object represents an ODBCDirect workspace.
The Workspaces collection contains the set of all active
ODBCDirect workspaces. The following table shows the relationship
between the Workspace object and the Workspaces
collection and other objects and collections in an ODBCDirect
workspace.
Databases collection
Properties collection
The first step in using ODBCDirect is to create an ODBCDirect
workspace with the CreateWorkspace method. The ODBCDirect
workspace routes calls directly to the ODBC application programming
interface (API), as opposed to the Microsoft Jet workspace, which
first routes calls to the Microsoft Jet database engine, and then
to the ODBC API if you're using ODBC.
The Connection Object and the Connections
Collection
After you've created an ODBCDirect workspace, you can connect
to an ODBC data source. To connect to an ODBC data source, you
can use the OpenConnection method to open a new Connection object, or you can use the OpenDatabase method to open
a new Database object. This section explains how to use
the Connection object. For information on how to use a
Database object, see the following section, "The Database Object and the Databases Collection."
A Connection object represents a connection to an ODBC
database in an ODBCDirect workspace. The Connections collection
contains all currently open Connection objects. When you
open a Connection object, it is automatically appended
to the Connections collection of the Workspace object.
When you close a Connection object with the Close
method, it is removed from the Connections collection.
The Connection object provides the following advantages
for accessing ODBC data:
You can use the OpenConnection method to create a Connection
object. The syntax of the OpenConnection method is:
Set connection = workspace.OpenConnection
(name, options, readonly,
connect)
The connection argument is the name of the
new Connection object. The workspace
argument is the name of an ODBCDirect Workspace object
from which you're creating the new Connection object.
The name argument indicates the name of the
registered data source. You can reference the new Connection
object by using either the data source name (DSN) or the Connection
object's ordinal position within its collection. The options
argument determines if and when to prompt the user to establish
the connection, and whether or not to open the connection asynchronously.
The readonly argument controls the updatability
of the data accessed through the connection. Set this argument
to True to prevent updates; set it to False to allow
updates.
The connect argument is a valid connect string
that supplies parameters to the ODBC driver manager. These parameters
can include user name, password, default database, and data source
name (DSN), which overrides the value provided in the name
argument.
The connect string must start with "ODBC;",
and must contain a series of values needed by the driver to access
the data. The actual connect string can vary depending on the
data source you're trying to access; different ODBC data sources
require different parameters in the connect
argument. Usually, the minimum requirement is a user ID, a password,
and a DSN, as shown in the following example:
Note If
you are trying to connect to a Microsoft SQL Server database that
uses integrated security, omit the user ID (UID) and password
(PWD) values because your Windows NT® user name and password
are automatically used. For example, the connect string may look
something like the following:
Object or collection
Is contained by
Contains Workspace object
Workspaces collection
Connections collection
Workspaces collection
DBEngine object
Workspace objects
When the ODBC driver processes the connect string and one or more
of the parameters required by the data source is missing, the
driver displays a dialog box that asks for the information. If
you don't want this dialog box displayed, you must make sure that
the connect string has all the required information.
ODBC;UID=JamesK;PWD=OpenSesame;DSN=MasterData
For more information about parameters that can be included in
a connect string, search DAO Help for "Connect property."
The following example illustrates how to use the OpenConnection
method to open a new Connection object.
ODBC;UID=;PWD=;DATABASE=Pubs;DSN=Pubs
Function OpenPubsConnection() As Boolean
Dim wrk As Workspace, cnn As Connection, rst As Recordset, fld As Field
Dim strConnect As String, strSQL As String
On Error GoTo Err_OpenPubsConnection
' Create connnect string.
strConnect = "ODBC;DSN=Pubs;UID=SA;PWD=;DATABASE=Pubs"
' Create SQL string.
strSQL = "SELECT * FROM Authors WHERE State = 'MD';"
' Create ODBCDirect workspace.
Set wrk = DBEngine.CreateWorkspace("NewODBCDirect", "sa", "", dbUseODBC)
' Open connection.
Set cnn = wrk.OpenConnection("Pubs", dbDriverNoPrompt, False, strConnect)
' Open recordset on connection.
Set rst = cnn.OpenRecordset(strSQL, dbOpenDynaset)
' Print values in recordset.
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Name, fld.Value
Next fld
Debug.Print
rst.MoveNext
Loop
OpenPubsConnection = True
Exit_OpenPubsConnection:
rst.Close
cnn.Close
Exit Function
Err_OpenPubsConnection:
MsgBox "Error " & Err & ": " & Err.Description
OpenPubsConnection = False
Resume Exit_OpenPubsConnection
End Function
After you've created a Connection object, you can open Recordset objects and run queries on the Connection object.
When you open a Connection object, a corresponding Database object is created and appended to the Databases collection in the same workspace. When you open a database in an ODBCDirect workspace, a Connection object is likewise created and appended to the Connections collection. When you close either the Connection object or the Database object, the corresponding object is also closed.
Note Before
you close a Connection object, close all open Recordset objects
within it.
Opening Connections Asynchronously
In some cases, opening connections to data sources can take a
long time, making it necessary for users to wait until the connection
completes or an error occurs. To reduce the amount of time users
must wait, you can open a connection asynchronously. This means
that your application can complete other tasks while the connection
is being established. To open a connection asynchronously, specify
the dbRunAsync constant for the options
argument of the OpenConnection method, as shown in the
following example.
You can use the StillExecuting property of the Connection
object to see if the connection has been established, or use the
Cancel property of the Connection object to cancel
the connection attempt if it takes too long.
The Database Object and the Databases
Collection
You can also connect to an ODBC data source by using the OpenDatabase
method to open a Database object. However, the Database object in an ODBCDirect workspace doesn't support all of the
functionality of a Connection object. Specifically, if
you're using a Database object, you can't connect asynchronously,
run queries asynchronously, or define QueryDef objects
that represent queries in the ODBC data source.
To connect to an ODBC data source with the OpenDatabase
method in an ODBCDirect workspace, specify a valid connect string
for the connect argument of the OpenDatabase
method, as shown in the following example.
Switching Between Connection and Database Objects
With ODBCDirect, you can open a Database object and a Connection
object against the same ODBC data source, and use both in your
code. You can then take advantage of each object for its different
capabilities.
Alternatively, you may want to create a single object and then
switch to the other type when needed. To do this, use the Connection
property of the Database object or the Database
property of the Connection object. You can use these properties
to create Connection objects from Database objects
and to create Database objects from Connection objects.
This is especially useful for adding ODBCDirect capabilities to
existing applications that only use Database objects.
For example, you can use a Database object for most of
your ODBC data access needs, but when you need to run an asynchronous
query, you can create a Connection object from the Database
object and then run the query on the Connection object.
The following example illustrates this technique.
The QueryDef Object and the QueryDefs
Collection
The QueryDef object represents a temporary definition of
a query in an ODBCDirect workspace. The QueryDefs collection
contains all QueryDef objects that currently exist in the
workspace. The following table shows the relationship between
the QueryDef object and the QueryDefs collection
and other objects and collections in an ODBCDirect workspace.
Properties collection
Unlike QueryDef objects created in a Microsoft Jet workspace,
QueryDef objects created in an ODBCDirect workspace are
always temporary they are not saved within the
data source before they run, even if you assign them a name.
Running Asynchronous Queries
Creating and running queries in an ODBCDirect workspace is similar
to creating and running queries in a Microsoft Jet workspace.
You create the query by invoking the CreateQueryDef method
on a Connection object, and then use the Execute
or OpenRecordset methods on the resulting query.
You can use asynchronous queries so that users can continue using
your application while the query runs. You can also give users
the ability to cancel asynchronous queries if they are taking
too long. The following example runs an asynchronous query.
The preceding example uses a QueryDef object on a Connection
object to run an asynchronous query. You can also use the Execute
method directly on the Connection object, as shown in the
following example.
Dim wrk As Workspace, cnn As Connection, strConnect As String
Set wrk = DBEngine.CreateWorkspace("NewODBCDirect", "sa", "", dbUseODBC)
strConnect = "ODBC;DSN=Pubs;UID=SA;PWD=;DATABASE=Pubs"
Set cnn = wrk.OpenConnection("",dbDriverNoPrompt + dbRunAsync, False, strConnect)
Dim wrk As Workspace, dbs As Database
Dim strConnect As String
strConnect = "ODBC;DSN=Pubs;UID=SA;PWD=;DATABASE=Pubs"
Set wrk = DBEngine.CreateWorkspace("NewODBCDirect", "sa", "", dbUseODBC)
Set dbs = wrk.OpenDatabase("Pubs", dbDriverNoPrompt, False, strConnect)
Sub DeleteRecords()
Dim dbs As Database, strConnect As String
Dim cnn As Connection
' Open database in default workspace.
strConnect = "ODBC;DSN=Pubs;DATABASE=Pubs;UID=sa;PWD=;"
Set dbs = OpenDatabase("", False, False, strConnect)
' Try to create Connection object from a Database object. If workspace is an
' ODBCDirect workspace, the query runs asynchronously. If workspace is a
' Microsoft Jet workspace, an error occurs and the query runs synchronously.
Err = 0
On Error Resume Next
Set cnn = dbs.Connection
If Err = 0 Then
cnn.Execute "DELETE FROM Authors", dbRunAsync
Else
dbs.Execute "DELETE FROM Authors"
End If
End Sub
Object or collection
Is contained by
Contains QueryDef object
QueryDefs collection
Parameters collection
QueryDefs collection
Connection object
QueryDef objects
Function DeleteLargeSales() As Boolean
Dim wrk As Workspace, rst As Recordset
Dim cnn As Connection, qdf As QueryDef
Dim strConnect As String, strSQL As String
Dim errObj As Error
On Error GoTo Err_DeleteLargeSales
' Create ODBCDirect workspace.
Set wrk = DBEngine.CreateWorkspace("ODBC", "sa", "", dbUseODBC)
' Create connect string.
strConnect = "ODBC;DSN=Publishers;UID=SA;PWD=;DATABASE=Pubs"
' Open connection on workspace.
Set cnn = wrk.OpenConnection("", dbDriverNoPrompt, False, strConnect)
' Delete existing QueryDef named DeleteLargeSales.
For Each qdf In cnn.QueryDefs
If qdf.Name = "DeleteLargeSales" Then
cnn.QueryDefs.Delete "DeleteLargeSales"
End If
Next qdf
' Create QueryDef.
Set qdf = cnn.CreateQueryDef("DeleteLargeSales")
strSQL = "DELETE FROM sales WHERE qty = 100"
qdf.SQL = strSQL
' Run query asynchronously.
qdf.Execute dbRunAsync
While qdf.StillExecuting
' Additional code runs here while query runs.
' Check StillExecuting property to determine whether query has finished.
Wend
DeleteLargeSales = True
Exit_DeleteLargeSales:
cnn.Close
wrk.Close
Exit Function
Err_DeleteLargeSales:
For Each errObj In Errors
Debug.Print errObj.Number, errObj.Description
Next errObj
DeleteLargeSales = False
Resume Exit_DeleteLargeSales
End Function
When you run a query asynchronously, you can use the StillExecuting
property to determine if the query has completed. If the value
of the StillExecuting property is True, the query
has not yet completed. If you want to cancel an asynchronous query,
use the Cancel method, as shown in the following example.
Dim cnn As Connection, strConnect As String
strConnect = "ODBC;DSN=Pubs;UID=SA;PWD=;DATABASE=Pubs"
Set cnn = OpenConnection("", dbDriverNoPrompt, False, strConnect)
cnn.Execute "DELETE FROM sales WHERE qty = 100", dbRunAsync
cnn.Close
Function CancelAsynchQuery() As Boolean
Dim wrk As Workspace, cnn As Connection, strConnect As String
Dim errObj As Error
On Error GoTo Err_CancelAsynchQuery
Set wrk = DBEngine.CreateWorkspace("ODBCDirect", "Admin", "", dbUseODBC)
strConnect = "ODBC;DSN=Pubs;UID=SA;PWD=;DATABASE=Pubs"
Set cnn = wrk.OpenConnection("", dbDriverNoPrompt, False, strConnect)
' Start transaction in order to roll back if needed.
wrk.BeginTrans
cnn.Execute "DELETE FROM sales WHERE qty = 100", dbRunAsync
' Perform other operations.
.
.
.
' If query is still running, cancel and roll back.
If cnn.StillExecuting Then
cnn.Cancel
wrk.Rollback
' If query is complete, commit transaction.
Else
wrk.CommitTrans
End If
CancelAsynchQuery = True
Exit_CancelAsynchQuery:
cnn.Close
wrk.Close
Exit Function
Err_CancelAsynchQuery:
For Each errObj In Errors
Debug.Print errObj.Number, errObj.Description
Next errObj
CancelAsynchQuery = False
Resume Exit_CancelAsynchQuery
End Function
You can use the StillExecuting property and the Cancel method with QueryDef, Connection, and Recordset objects.
A Connection object can support only one asynchronous operation at a time. Also, you can't perform another DAO operation, such as recordset manipulation, on a Connection object while an asynchronous query runs on the same Connection object. After an asynchronous query is complete, you can then begin running another asynchronous query on the same Connection object. You must first test the value of the StillExecuting property to determine whether you can start the next asynchronous operation. To run multiple asynchronous queries at the same time, you must create separate Connection objects and run each asynchronous query on its own Connection object.
In most cases, you'll want to run an asynchronous query as part of a transaction. Be aware, however, that if you call the CommitTrans method while the asynchronous query is still running, your code will pause at the CommitTrans method until the query finishes. For this reason, it is more efficient to periodically check the StillExecuting property and continue to perform other work while the query runs. Once the StillExecuting property returns False, you can then call the CommitTrans method. This prevents your code from pausing at the CommitTrans method.
Note If
you cancel an action query that is not part of a transaction,
the query updates records up to the point where you called the
Cancel method. The operation will be partially complete and will
not be rolled back. For this reason, you should use the Cancel
method only within the scope of a transaction. Additionally, if
you start an asynchronous query in a procedure and the procedure
exits before the query has completed, the query will continue
to run.
To improve performance when you're retrieving data from an ODBC
data source, you can cache records locally. A cache
is a space in local memory that holds the data most recently retrieved
from the server. If you're performing repeated operations on a
set of data, caching that data makes those operations faster because
you don't have to retrieve the data from the server each time
you need it.
In ODBCDirect queries, use the CacheSize property of the
QueryDef object to specify the number of records to cache.
The default cache size is 100 records. The following example shows
how to reset the cache size to 200 records.
Sub SetCacheSize()
Dim
wrk As Workspace, qdf As QueryDef, rst As Recordset
Dim cnn As Connection, strConnect As String
Set wrk = CreateWorkspace("ODBCDirect", "Admin", "", dbUseODBC)
Set cnn = OpenConnection("", dbDriverNoPrompt, False, strConnect)
strConnect = "ODBC;DSN=Pubs;UID=SA;PWD=;DATABASE=Pubs"
Set qdf = cnn.CreateQueryDef("tempquery")
qdf.SQL = "SELECT * FROM roysched"
qdf.CacheSize = 40
Set rst = qdf.OpenRecordset()
' Perform some operations on recordset.
rst.Close
cnn.Close
End Sub
The Parameter Object and the Parameters
Collection
The Parameter object in an ODBCDirect workspace is similar
to the Parameter object in a Microsoft Jet workspace, with
a few differences. In an ODBCDirect workspace, you can change
the setting of the Type property, which is readonly
in a Microsoft Jet workspace. You can also use the Direction
property to indicate whether a parameter is an input parameter,
an output parameter, or both, or the return value from the procedure.
The following example specifies parameters for a query in an ODBCDirect
workspace.
Function RunStoredProc() As Boolean
Dim wrk As Workspace
Dim qdf As QueryDef, rst As Recordset, fld As Field
Dim cnn As Connection, strConnect As String, strSQL As String
Set wrk = CreateWorkspace("ODBCDirect", "sa", "", dbUseODBC)
strConnect = "ODBC;DSN=Pubs;UID=sa;PWD=;DATABASE=Pubs"
Set cnn = wrk.OpenConnection("", dbDriverNoPrompt, False, strConnect)
strSQL = "CREATE PROCEDURE tamram @lolimit money AS " _
& "SELECT pub_id, type, title_id, price " _
& "FROM titles WHERE price >@lolimit"
cnn.Execute strSQL
Set qdf = cnn.CreateQueryDef("RunStoredProc")
qdf.SQL = "{ call tamram (?) }"
qdf.Parameters(0).Value = CCur(10)
Set rst = qdf.OpenRecordset()
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Name, fld.Value
Next fld
rst.MoveNext
Loop
End Function
The Recordset Object and the Recordsets
Collection
The Recordset object represents the records that result
from running a query on a Connection object or a Database object in an ODBCDirect workspace. The Recordsets collection
contains all currently open Recordset objects on a Connection object or a Database object. The following table shows
the relationship between the Recordset object and the Recordsets
collection and other objects and collections in an ODBCDirect
workspace.
Properties collection
Database object
The types of Recordset objects supported in an ODBCDirect
workspace include the dynasettype, snapshottype, forwardonlytype, and dynamictype Recordset objects. For more information on all of these Recordset objects except the dynamictype Recordset object, see "TableType Recordset Objects,"
"DynasetType Recordset Objects," "SnapshotType Recordset Objects," "ForwardOnlyType Recordset
Objects" earlier in this chapter. The following section describes
dynamictype Recordset objects.
DynamicType Recordset Objects
An additional type of Recordset object, the dynamictype
Recordset object, is available in ODBCDirect workspaces.
Dynamictype Recordset objects behave like dynasettype
Recordset objects, but they are updated dynamically as
other users make modifications to the underlying tables. To create
a dynamictype Recordset object, specify the dbOpenDynamic
constant for the type argument of the OpenRecordset
method.
Dynamictype Recordset objects are available only
if you're using an ODBC driver that supplies its own cursors.
Because not all ODBC drivers supply their own cursors, you need
to determine whether yours does before you try to open a dynamictype
Recordset object. If your ODBC driver doesn't supply its
own cursors, then you should open a snapshottype or forwardonlytype
Recordset object instead. For more information on cursors,
see "Using Cursors in ODBCDirect Workspaces" later in
this chapter.
The advantage of using a dynamictype Recordset object
is that the recordset will immediately reflect any changes to
the data, including added or deleted records. For example, if
you open a dynamictype Recordset object and another
user edits a record in one of the underlying tables, that change
will be reflected in the Recordset you opened. In order
to do this, however, DAO must constantly requery the data source,
which may slow performance considerably. Therefore, avoid using
dynamictype Recordset objects except in situations
where it's crucial to have the most uptodate data
at all times.
Opening Recordset Objects Asynchronously
In addition to running queries asynchronously, you can open Recordset
objects asynchronously. To do so, specify the dbRunAsync
constant for the options argument of the OpenRecordset
method. You can then use the Cancel method and the StillExecuting
property directly on the Recordset object. For example,
if you open a Recordset object asynchronously, and it takes
a long time to open because more records are returned than expected,
you can give users the option of canceling the operation in order
to specify more restrictive criteria that returns fewer records.
If you cancel an OpenRecordset method, the Recordset
object becomes invalid and you must reopen it to retrieve a valid
Recordset object.
Because moving to the last record in a recordset can take a long
time, the MoveLast method of a Recordset object
supports asynchronous operation. To perform an asynchronous MoveLast
operation, use the dbRunAsync constant with the MoveLast
method. Be sure to check the StillExecuting property to
determine when this operation is complete.
The Field Object and the Fields Collection
In an ODBCDirect workspace, the Field object represents
a field in a QueryDef object or a Recordset object.
When you're performing batch updates, you can use the Value,
VisibleValue, and OriginalValue properties of a
Field object to verify successful completion of a batch
update. For more information, see "Using Batch Optimistic
Updating" in the following section.
Object or collection
Is contained by
Contains Recordset object
Recordsets collection
Field objects
Recordsets collection
Connection object
Recordset objects
The following sections explain how to perform some common operations in an ODBCDirect workspace: using batch optimistic updating, working with cursors, and working with stored procedures.
Using Batch Optimistic Updating
In many client/server applications, optimistic updates occur on
a recordbyrecord basis. This usually happens with
the following series of events:
Although this process works well for many applications, it is
often more efficient to have the user edit multiple records that
are cached locally and then submit these records to the server
in a single batch for updating. This process is called batch
optimistic updating.
To
use batch optimistic updating
Note
If you attempt a batch update while a record in that Recordset
object is being edited by the user, the record being edited will
automatically be updated before the batch update begins.
The following example illustrates how to use batch optimistic
updating.
Function RunInBatch()
Dim wrk As Workspace, cnn As Connection, rst As Recordset
Dim strConnect As String
' Create ODBCDirect workspace.
Set wrk = DBEngine.CreateWorkspace("ODBCDirect", "Admin", "", dbUseODBC)
' Set default cursor driver to dbUseClientBatchCursor.
wrk.DefaultCursorDriver = dbUseClientBatchCursor
' Create connect string.
strConnect = "ODBC;DSN=Pubs;DATABASE=Pubs;UID=sa;PWD=;"
' Open connection.
Set cnn = wrk.OpenConnection("", dbDriverNoPrompt, False, strConnect)
' Open recordset on connection.
Set rst = _
cnn.OpenRecordset("SELECT * FROM sales", dbOpenDynaset, 0, dbOptimisticBatch)
' Change all records in local recordset.
While Not rst.EOF
rst.Edit
rst!qty = rst!qty + 1
rst.Update
rst.MoveNext
Wend
' Update all records in data source.
rst.Update dbUpdateBatch
End Function
If multiple records have been edited locally, and you want to update the current record before you perform the batch update, you can call the Update method and specify the dbUpdateCurrentRecord constant for the type argument. This writes the current record to the data source without writing any other batch updates. This is illustrated in the following example.
' Edit and update first record.
' Only first record is written back to data source.
rst.MoveFirst
rst.Edit
rst!qty = rst!qty + 2
rst.Update dbUpdateCurrentRecord
' Update remaining records in data source.
rst.Update dbUpdateBatch
Handling Collisions
When you attempt to update a group of records in a single batch
operation, it is possible that other users are editing one or
more records you are trying to update, causing a collision.
A collision occurs when a batch update attempts to update a record
at the same time another user is updating the record.
To handle collisions, examine the BatchCollisions property
on the Recordset object. The BatchCollisions property
returns an array that stores bookmarks pointing to records in
the Recordset object on which a collision occurred. Each
time a collision occurs during a batch update, a bookmark for
the record is added to the array returned by the BatchCollisions
property. You can then move to each of these bookmarks and examine
the following properties of the Field object of the current
record.
After examining these properties, you can choose one of the following
options:
The following example shows how to use the array returned by the
BatchCollisions property to force all changes made to a
local Recordset object into the database.
Property Description
Value
The current value of the field in your Recordset object. This corresponds to the value of the field after the Update method was called.
OriginalValue
The value of the field in your Recordset object before the Update method was called.
VisibleValue
The value of the field as it is stored in the database.
Caution Calling
the Update method and specifying the dbUpdateBatch
constant for the type argument and True
for the force argument forces all your changes
into the data source and overwrites any changes that other users
made to the records. For this reason, it is safer to call the
Update method without specifying the force
argument, and then resolve collisions individually by using the
array returned by the BatchCollisions property along with
the Value, OriginalValue, and VisibleValue
properties.
In the preceding example, modifications to the Recordset
object are written back to the database one record at a time.
In the following example, all records are saved in a batch instead
of writing one record at a time.
Function BatchForceChanges()
Dim rst As Recordset, cnn As Connection, varCollision As Variant
' Open recordset for batch optimistic updating.
Set rst = _
cnn.OpenRecordset("SELECT * FROM sales", dbOpenDynaset, 0, dbOptimisticBatch)
' Change all records in local recordset.
While Not rst.EOF
rst.Edit
rst!qty = rst!qty + 1
rst.Update
rst.MoveNext
Wend
rst.Update dbUpdateBatch
' Check for collisions and force all changes to recordset
' into database one record at a time.
For j = 0 to rst.BatchCollisionCount - 1
varCollision = rst.BatchCollisions(j)
rst.BookMark = varCollision
rst.Update dbUpdateCurrentRecord, True
Next j
End Function
' Open recordset.
Set rst = _
cnn.OpenRecordset("SELECT * FROM sales", dbOpenDynaset, 0, dbOptimisticBatch)
' Change all records in local recordset.
While Not rst.EOF
rst.Edit
rst!qty = rst!qty + 1
rst.Update
rst.MoveNext
Wend
rst.Update dbUpdateBatch, True
Using Cursors in ODBCDirect Workspaces
A cursor indicates the current record position
in a result set. Most types of cursors contain a representation
of the data in the data source, and are not updatable. Keysets
are cursors that contain actual data, and are updatable.
You work with a cursor through the DAO Recordset object.
When you open a Recordset object through DAO, ODBCDirect
creates the corresponding cursor. Each type of Recordset
object, except for the tabletype Recordset object,
corresponds to a different type of cursor.
Characteristics of Cursors
You can use cursors to work with sets of data on an ODBC data
source. Cursors can:
ClientSide Cursors vs. ServerSide Cursors
A cursor requires temporary resources to hold its data. These
resources can be in the form of RAM, a paging file such as the
virtual memory feature of Microsoft Windows, or temporary files
or databases. If these resources are stored on the client machine,
the cursor is called a clientside cursor.
With this type of cursor, the server sends the data that the cursor
represents across the network to the client, along with the data
required by the cursor itself. The client manages the temporary
resources needed by the cursor.
Some server database engines, such as Microsoft SQL Server version
6.0, support an additional type of cursor known as serverside
cursors. With this cursor type, the server manages the result
set with resources located on the server itself. The server returns
only the requested data to the client over the network. Using
this type of cursor can result in significant performance improvements
compared to clientside cursors, especially in situations
where excessive network traffic or inadequate network bandwidth
is a problem. However, because RAM and disk space resources are
needed at the server, you must plan accordingly and ensure that
your server hardware is capable of managing all cursors requested
by clients.
Choosing a Cursor Type
When you open a Recordset object on a nonODBC data
source, you can specify a constant for the type
argument of the OpenRecordset method that determines what
type of recordset is opened. When you open a Recordset
object on an ODBC data source, you use this same argument to specify
the type of cursor that the Recordset object represents.
Each type of cursor corresponds to a type of recordset. The following
table shows the four constants you can use for the type
argument, the type of Recordset object that is created
on a nonODBC data source, and the type of cursor that is
created on an ODBC data source.
For more information about ODBC cursors, see the ODBC
3.0 Programmer's Reference.
Note Tabletype
Recordset objects aren't supported in ODBCDirect workspaces, so
they have no corresponding cursor.
The DefaultCursorDriver property of a Workspace
object specifies where ODBCDirect creates the cursor
on the client or on the server. You can set the DefaultCursorDriver
property to any of the constants listed in the following table.
Record Locking
When you open a Recordset object, you can also specify
the type of record locking you want to use by setting the lockedits
argument of the OpenRecordset method to the appropriate
constant. The following table lists the five constants you can
use for the lockedits argument of the OpenRecordset
method, and describes the ODBC cursor lock type to which they
correspond.
Some combinations of cursors and lock types will not work together.
For example, with Microsoft SQL Server version 6.0 cursors, if
you specify the dbOpenSnapshot constant for the type
argument of the OpenRecordset method, you must specify the dbReadOnly constant for the lockedits argument. Static
cursors do not support the other types of record locking. Which
combinations work together depends on the cursor driver. For specific
information about compatible lock types, refer to your cursor
driver documentation.
Your cursor driver can handle different combinations of cursor
types and lock types in different ways. In some cases, it may
return an error if it does not handle a specific combination.
In other cases, it may switch to the nearest possible combination
that it supports. If an error occurs, DAO places the error information
in the Errors collection.
Cursor Limitations
In an ODBCDirect workspace, the default recordset is a readonly,
forwardonlytype Recordset object. Therefore,
if you create the default Recordset object by opening it
without specifying a value for the type argument,
you won't be able to edit data on the server. If you want to edit
data on the server, you need to explicitly specify a lock type
other than dbReadOnly for the lockedits
argument of the OpenRecordset method.
Because you can't open a tabletype Recordset object
in an ODBCDirect workspace, you can't use the Index property
or the Seek method to retrieve data. Also, recordsets opened
against ODBC data sources do not support any of the Find methods:
FindFirst, FindNext, FindPrevious, and FindLast.
In a client/server environment, it's more efficient to fetch only
the data that you need, rather than retrieving more records than
you need and then searching through those records for the data
that you want. Therefore, design your queries to return only the
records that you need.
Retrieving Multiple Result Sets
Any SQL statement can include multiple SELECT statements or stored
procedures that invoke one or more SELECT statements. Each SELECT
statement generates a result set that must be processed by your
code or discarded before the resources are released and the next
result set is made available. Because you don't necessarily know
how many results sets will be generated by a stored procedure,
your code must be prepared to process an unknown number of result
sets. Note that when a stored procedure returns multiple result
sets, none of the result sets can be updated.
You can use either clientside cursors or serverside
cursors to retrieve multiple result sets. If you use clientside
cursors, multiple result sets are returned no matter what type
of Recordset object you open. If you use serverside
cursors to retrieve multiple result sets, you must open a forwardonlytype
Recordset object.
To
retrieve multiple results sets
The following example prints the values of each field for each
record in each result set.
Working with Stored Procedures
You can use ODBCDirect QueryDef objects to run stored procedures.
ODBCDirect QueryDef objects support stored procedures that
have both input parameters and return values. Input parameters
are the parameter values supplied to the procedure at run time.
The procedure's return value is the value that it returns when
it has finished running. For example, a stored procedure may return
the number of records that have been affected.
The following example creates a stored procedure named GetEmps
on the server.
If there is already a stored procedure named GetEmps on the server,
you can use the DROP statement to delete it before creating a
new one, as shown in the following example.
You can run the stored procedure by using the Execute method
of a Connection object. To retrieve the return value, create
a QueryDef object and open a recordset on it.
Use the Parameter object to work with parameters. The Direction
property of a Parameter object tells DAO how the parameter
will function. The ODBC driver tries to determine the parameter
direction, but the Direction property is read/write, so
you can set it if you need to. The following example creates a
simple stored procedure with an input parameter and a return value.
It then runs the procedure and retrieves the return value.
Constant Recordset type
Cursor type
dbOpenDynamic
Dynamic-type Dynamic
dbOpenDynaset
Dynaset-type Keyset
dbOpenSnapshot
Snapshot-type Static
dbOpenForwardOnly
Forward-only-type
Forward-only scrolling (this is the default)
Constant Description
dbUseODBCCursor
Use clientside cursors. Clientside cursors give better performance for small result sets, but degrade quickly for larger result sets.
dbUseServerCursor
Use serverside cursors. For most large operations, serverside cursors provide better performance, but may cause more network traffic. Not all ODBC data sources support serverside cursors.
dbUseDefaultCursor
Use serverside cursors if the server supports them; otherwise, use clientside cursors.
dbUseClientBatchCursor
Use client batch cursors. Required for batch updates.
dbUseNoCursor
Open all Recordset objects as forwardonlytype, readonly, with a rowset size of 1.
Constant ODBC cursor lock type
dbOptimistic
Uses optimistic locking to determine how changes are made to the Recordset object in a multiuser environment. The page containing the record that is being edited is locked only while the record is being updated by the Update method.
DbPessimistic
Uses pessimistic locking to determine how changes are made to the Recordset object in a multiuser environment. The page containing the record that is being edited is locked as soon as you use the Edit method.
DbOptimisticValue
Uses optimistic concurrency based on record values.
DbOptimisticBatch
Uses batch optimistic updating.
DbReadOnly
Default for ODBCDirect workspaces. Prevents users from making changes to the data in the Recordset object.
Function GetMultipleResults()
Dim wrk As Workspace, rst As Recordset, cnn As Connection, qdf As QueryDef
Dim fld As Field, strSQL As String, strConnect As String, fDone As Boolean
' Create ODBCDirect workspace.
Set wrk = DBEngine.CreateWorkspace("ODBCDirect", "Admin", "", dbUseODBC)
' Create connect string.
strConnect = "ODBC;DSN=Pubs;DATABASE=Pubs;UID=sa;PWD=;"
' Open connection.
Set cnn = wrk.OpenConnection("", dbDriverNoPrompt, False, strConnect)
' Create SQL statement.
strSQL = "SELECT au_lname, au_fname FROM Authors; SELECT title FROM Titles;"
' Set default cursor driver.
wrk.DefaultCursorDriver = dbUseServerCursor
' Open recordset.
Set qdf = cnn.CreateQueryDef("", strSQL)
qdf.CacheSize = 1
' Open recordset on QueryDef.
Set rst = qdf.OpenRecordset(dbOpenForwardOnly)
Do Until fDone = True
' Print values for each field in each record of recordset.
While Not rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value
Next fld
rst.MoveNext
Wend
fDone = Not rst.NextRecordset()
Loop
rst.Close
cnn.Close
wrk.Close
End Function
strSQL = "CREATE PROCEDURE GetEmps AS "
strSQL = strSQL & "SELECT * FROM EMPLOYEE;"
cnn.Execute strSQL
strSQL = "DROP PROCEDURE GetEmps;"
cnn.Execute strSQL
Set qdf = cnn.CreateQueryDef("qry", "{ call GetEmps() }")
Set rst = qdf.OpenRecordset
' Create stored procedure on the server.
strSQL = "CREATE PROCEDURE UpdateEmps (@invar int) AS RETURN @invar;"
cnn.Execute strSQL
' Create QueryDef object to run stored procedure.
Set qdf = cnn.CreateQueryDef("qry", "{ ? = call UpdateEmps(?) }")
' Handle parameters.
qdf.Parameters(0).Direction = dbParamReturnValue
qdf.Parameters(1) = 10
qdf.Execute
' Get return value.
var = qdf.Parameters(0).Value