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:
Set wrk = DBEngine.Workspaces(0)
Set wrk = DBEngine(0)
Set wrk = Workspaces(0)
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 ("").
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.
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
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.
Object or collection
| Is contained by
| Contains |
Database object
| Databases collection
| Containers collection
QueryDefs collection
Properties collection
Recordsets collection
Relations collection
TableDefs collection
|
Databases collection
| Workspace object
| Database objects
|
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.
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
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.
Dim dbs As Database
Set dbs = CurrentDb
Debug.Print dbs.Name
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.
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
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.
Object or collection
| Is contained by
| Contains |
TableDef object
| TableDefs collection
| Fields collection
Indexes collection
Properties collection
|
TableDefs collection
| Database object
| TableDef objects
|
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.
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
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."
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
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.
Object or collection
| Is contained by
| Contains |
Field object
| Fields collection
| Properties collection
|
Fields collection
| TableDef object
Index object
QueryDef object
Recordset object
Relation object
| Field objects
|
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.
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
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.
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
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.
Object or collection
| Is contained by
| Contains |
Index object
| Indexes collection
| Fields collection
Properties collection
|
Indexes collection
| TableDef object
| Index objects
|
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.
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
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.
Object or collection
| Is contained by
| Contains |
QueryDef object
| QueryDefs collection
| Fields collection
Parameters collection
Properties collection
|
QueryDefs collection
| Database object
| QueryDef objects
|
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.
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
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.
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.
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
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.
Object or collection
| Is contained by
| Contains |
Parameter object
| Parameters collection
| Properties collection
|
Parameters collection
| QueryDef object
| Parameter objects
|
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.
PARAMETERS [Beginning OrderDate] DATETIME,[Ending OrderDate] DATETIME;
SELECT * FROM Orders
WHERE (OrderDate Between [Beginning OrderDate] And [Ending OrderDate]);
For a list of data types you can use for parameters,
search Microsoft Access Help for "data types, SQL."
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.
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
You can call this function from the Debug window as follows:
? NewParameterQuery(#6-30-95#, #6-30-96#)
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."
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.
Object or collection
| Is contained by
| Contains |
Relation object
| Relations collection
| Fields collection
Properties collection
|
Relations collection
| Database object
| Relation objects
|
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.
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
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.
Object or collection
| Is contained by
| Contains |
Recordset object
| Recordsets collection
| Fields collection
Properties collection
|
Recordsets collection
| Database object
| Recordset objects
|
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.
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
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:
- The data page the user is
trying to update is locked by another user.
- The
record has changed since it was last read.
- The
user doesn't have permission to update the recordset.
- One
or more of the tables or fields are readonly.
- The
database is opened for readonly access.
- The
Recordset object was created from multiple tables without
a JOIN statement.
- The Recordset object
includes fields from an ODBC data source, or Paradox table or
tables, and there isn't a unique index on those table or tables.
To create a dynasettype Recordset object, specify
the dbOpenDynaset constant for the type
argument of the OpenRecordset method, as shown in the following
example.
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
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.
Object or collection
| Is contained by
| Contains |
Group object
| Groups collection
| Group objects
Properties collection
Users collection
|
Groups collection
| Workspace object
User object
| Group objects
|
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.
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
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.
Object or collection
| Is contained by
| Contains |
User object
| Users collection
| Groups collection
Properties collection
User objects
|
Users collection
| Workspace object
Group object
| User objects
|
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.
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
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.
Object or collection
| Is contained by
| Contains |
Container object
| Containers collection
| Documents collection
Properties collection
|
Containers collection
| Database object
| Container objects
|
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.
Container name |
Contains information about
|
Databases | Saved databases
|
Tables | Saved tables and queries
|
Relationships | Saved relationships
|
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.
Container name |
Contains information about
|
Forms | Saved forms
|
Modules | Saved modules
|
Reports | Saved reports
|
Scripts | Saved macros
|
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.
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
To establish permissions for the Managers group on the Tables
container, you can call the SetGroupPermissions function as follows.
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
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.
Object or collection
| Is contained by
| Contains |
Document object
| Documents collection
| Properties collection
|
Documents
collection
| Container object
| Document objects
|
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.
Document | Container
| Contains information about
|
Database | Databases
| Saved database |
Table or query | Tables
| Saved table or query |
Relationship | Relationships
| Saved relationship |
Other applications can define additional Document objects.
For example, the following table lists the Document objects
defined by Microsoft Access.
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
|
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.
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
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.
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
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.
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
To set the ReplicableBool property of a Database object,
you can call the preceding function as follows.
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
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.
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")
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.
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."