Working with Data Access Objects (DAO)

So far, we have used a few objects of Data Access Objects (DAO). In this chapter, we will understand what are the other objects in DAO.

What is Jet Engine?
Jet Engine is the database engine that accesses the data. Jet Engine is a collection of programs that work together. Jet Engine makes use of some other translators to access various types of databases. Each type of database such as dBase, FoxPro, and Paradox etc has its own translator. These translators are to be available to access the particular type of database.

Jet Engine can also access ODBC data sources using ODBC drivers.

Jet Engine also has its own query engine, which is used to search, order and filter the data without using the query engine of the database. This may be an advantage with small databases such as MS-Access and FoxPro, but for large databases such as Oracle database, using Jet query engine may be inefficient.

Also remember DAO and Jet Engine are not designed for Internet or Intranet. To access data in either Internet or Intranet use ADO (ActiveX data objects) with OLE DB.

Objects in DAO object model
The following are the various objects in DAO object model. Here is the object model’s hierarchy . Later we will understand each object’s properties and methods.

Each object contains collections, methods and properties.

A collection is a collection of objects of the same type.

A method performs an operation on the object.

A property contains a single attribute of the object.

DBEngine object
This object is at the top level of the hierarchy. This object is automatically made available to your program. You do not have to create this object. The following are collections, methods and properties of this object.

Type Name Meaning
Collections Workspaces A collection of workspaces defined currently.
Errors Contains list of most recently occurred errors in the current session.
Properties Collection of properties of DBEngine object.
Methods RepairDatabase Is used to fix a corrupted Jet database file (.MDB).
CompactDatabase Is used to remove empty space and perform other operations that will improve performance. It can also be used to convert old version’s database to current version.
RegisterDatabase Registers an ODBC data source for Jet access.
Idle The Idle method allows the Microsoft Jet database engine to perform background tasks that may not be up-to-date because of intense data processing.
SetOption Used to change registry settings of Jet. These changes are in effect only during runtime. They do not change registry or INI file permanently.
CreateWorkspace Creates a workspace for accessing one or more databases.
Property Version Returns the version of DAO that is currently in use.
DefaultType Sets or returns a value that indicates what type of workspace (Microsoft Jet or ODBC Direct) will be used by the next workspace object created.
InitPath Returns information about the Windows Registry key that contains values for the Jet Engine.
LoginTimeout representing the number of seconds before a login timeout error occurs. Default value is 20.
Systemdb Specifies the location for workgroup information file, which allows you to define different workgroups and set permission to each object in the database for each user in workgroup.
DefaultPassword Sets the password to be used when creating default workspace.

DefaultUser Sets the name of the user to be used when creating default workspace.
Table 17.1 : Members of DB Engine object.

Data Access Object Model(DAO).

Here are a few examples using methods, properties and collections of DBEngine object. To display all properties write the following code.

Public Sub cmdDispProperties()

Dim p As Property
On Error GoTo errlbl

For Each p In DBEngine.Properties
Print p.Name;
Print " = ";
Print p.Value
Next
Exit Sub
errlbl:
Print "UnKnown"
Resume Next

End sub
Listing 17.1 : Code to display all properties.

Note: The above procedure to display properties can be used for any object by changing the name of DBEngine to the required object.

To repair a database:

DBEngine.repairdatabsae “C:\SRIKANTH\STUDENTS\STUDENTS.MDB”

Workspace Object
Workspace object creates a session for the user. By default, a workspace object is automatically created. If you want you can use that workspace object without creating any other workspace objects. But if you want the database to be accessed as a different user from default workspace object or create a separate session for transaction processing, you have to create a workspace object explicitly.

To create a workspace for user srikanth and type Jet explicitly, enter:

Dim ws As Workspace

Set ws = DBEngine.CreateWorkspace("W1", "srikanth", "praneeth", dbUseJet)

The following are the collections, methods and properties of the workspace object.

Type Name Meaning
Collections Databases Collection of all open databases.
Groups Collection of defined groups.
Users Collection of users.
Connections Contains the list of connections. Valid only for ODBC workspace.
Method OpenDatabase Opens a database in the workspace.
OpenConnection Opens a connection to the specified database. Valid only for ODBC workspace.
CreateDatabase Creates a new database with the given name.
CreateGroup Creates a new workgroup.
CreateUser Creates a new user.
BeginTrans Begins a new transaction
CommitTrans Ends the current transactions and saves the changes made in the transaction.
Rollback Ends the current transaction and restores the databases in the Workspace object to the state they were in when the current transaction began.
Close Closes the current workspace.
Properties Type Returns the type of workspace – Jet or ODBCDirect.
DefaultCursorDrive Specifies the type of cursor drive to be used. This effects only the new connections created after this property is set.
UserName Returns the name of the current user.
LoginTimeout Returns or sets the login timeout. For details see DBEngine object above.
Table 17.2 : Members of workspace object.

Here are a few examples regarding how to use Workspace object:

To open “STUDENTS.MDB” using user srikanth, enter:

Ws.opendatabase(“C\SRIKANTH\STUDENTS.MDB”,false,false,null)

To start a transaction and end it successfully, enter:

ws.BeginTrans
‘ make changes here
ws.CommitTrans

Database Object
This object is used to access a particular database. This object has the collections, methods and properties using which we can access database and its structure and modify it. The following are the collections, methods and properties:

Type Name Value
Collections Tabledefs Contains the details of all tables of the database.
Querydefs Contains the list of queries defined on the database.
Relations Contains the list of relations set between tables.
Recordsets Contains the list of open recordsets.
Method CreateQuerydef Creates a new query definition object.
CreateTabledef Creates a new table definition in the table.
CreateRelation Creates the relationship between two columns of two tables.
OpenRecordset Opens a new recordset and populates the recordset either with the result of the query or with the contents of the specified table.
Execute Allows you to execute any command on Oracle.
CreateProperty Allows you to create a new userdefined property.
Close Closes the database connection.
Properties Collatingorder Specifies the sequence in which characters are to be taken.
Connect Contains the information regarding what type of database and which database is currently open.
QueryTimeout Sets or returns a value that specifies the number of seconds to wait before a timeout error occurs when a query is executed on an ODBC data source.
RecordsEffected Returns the number of records effected by the most recently executed Execute method.
Updatable Returns true, if database is updatable.
Transaction Returns true, if database supports transactions.
Table 17.3 : Members of database object.

The following are a few examples to show how to use database object.

To increase RPU of products table by 10% and display number of records updated, enter:

DB object refers to an open database.

Db.execute “update products set rpu = rpu * 1.1”
If db.recordseffected <> 0 then
Msgbox “ Updated : “ & db.recordseffected
End if

QueryDef object
This object is used to store an SQL query in the database. SQL query that is stored using this object can be used as the source for recordset object later. Queries stored using QueryDef object are stored in processed format and as a result they are executed faster than ordinary query.

To create a Querydef object, enter:

Dim db As Database
Dim qd As QueryDef
Dim rs As Recordset

On Error Resume Next

Set db = OpenDatabase("d:\vbbook\products.mdb")
' delete TOTSALES if it is already existing
db.QueryDefs.Delete ("totsales")
‘ reset error handler
on error goto 0
Set qd = db.CreateQueryDef("totsales", "select prodno,sum (qtysold * rpu) from sales group by prodno")
Set rs = qd.OpenRecordset

Note: Once a Querydef object is created, it is automatically added to QueryDefs collection. This object behavior is different from other objects and collections where the object is to be explicitly added to collection using Append method of the collection.

Once Querydef is created it could be used to create a recordset as shown above. Generally Querydef is used to store complex queries.

The following are the collections, methods and properties of QueryDef object.

Type Name Meaning
Collections Parameters Contains the details of all parameters to be passed to the query.
Fields Contains information regarding the fields in the query.
Method Openrecordset Creates a record set based on the query in the object.
Execute Executes the given action query, such as Insert, Delete and Update commands.
Cancel Cancels execution of an asynchronous query in ODBCdirect workspace.
Properties Returnsrecords Indicates whether the query returns records or not.
Recordseffected Returns the number of records effected by the most recently invoked Execute method.
SQL Contains the SQL command that is to be executed by the object.
MaxRecords Specifies the maximum number of records to be retrieved. Default value is 0 – no limit.
Prepare Indicates whether server should create a temporary stored procedure before executing the command or should execute the command directly.
Stillexecuting Returns true if an asynchronous query is still in execution.
Table 17.4:Members of Querydef object.

Dim db As Database
Dim qd As QueryDef
Dim rs As Recordset

Set db = OpenDatabase("d:\vbbook\products.mdb")
' create a new querydef object
Set qd = db.CreateQueryDef("totsales", "PARAMETERS pn Integer;select * from sales where prodno = pn")
‘ set value for parameter
With qd
.Parameters.Refresh
.Parameters("pn").Value = 2 ‘ get details of sales for product with number 2
End With
‘ create a recordset using query defintion
Set rs = qd.OpenRecordset
‘ display all records from recordset
Do Until rs.EOF
' display data from recordset. RS(0) refers to first column in the result set, RS(1) for second column and so on
Print rs(0), rs(2), rs(3), rs(4)
rs.MoveNext
Loop

TableDef Object
Contains details of a table in the database. The table may be a linked table also. TableDefs collection contains the list of TableDef objects. Each TableDef object represent one table. The following are the collections, methods and properties of the TableDef object.

Type Name Meaning
Collections Fields Contains the list of fields of the table
Indexes Contains the list of indexes of the table.
Methods CreateIndex Creates an index on the table
CreateField Creates a field.
RefreshLinks Updates the connection information for a linked table.
Properties Attributes Contains one or more characteristics of the table, such as whether the table is a normal table or system table.
RecordCount Returns the number of records in the table.
SourceTablename Contains the name of the source table for link table or the name of the table for base table.
ValidationRule Contains the condition to be satisfied when table is manipulated.
ValidationText Contains the text that is to be displayed when validationrule fails.
Table 17.5 : Members of TableDef object.

Field object
A Field object represents a single field of a table, recordset, index or querydef. Fields collection contains a list of field objects.

The following are the methods and properties of field object.

Type Name Meaning
Method AppendChunk Adds a block of data to the field.
GetChunck Returns a chunk of memo or binary field object.
Properties Type Specifies the type of value that can be stored in the field
Size Specifies the number of bytes the field is occupying.
Default value Specifies the default value that is stored when no other value is specified.
Required If set to true, the field must not contain null value.
ValidationRule Contains the condition that is to be satisfied for the field.
Validationtext Contains the message that is to be displayed when validationrule fails.
Value Contains the value of the field.
Table 17.6: Members of field object.

Index Object
Represents an index of a table. Indexes collection contains a list of index objects.The following are the collections, methods and properties of the index object.

Type Name Meaning
Collections Fields Contains information regarding the fields in the index.
Method CreateField Creates a field, which can be added to index.
Properties Unique Indicates whether values in the index are unique.
IgnoreNulls If it is set to null, fields with null values do not have entries in index.
Primary Specifies whether the index is the primary index of the table. A primary index is unique also.
Required If set to True, the indexed column must contain a not null value.
Foreign Determines whether index object represents a foreign key in the table.
Distinctcount Returns the number of unique values the index currently has.
Table 17.7: Members of index object.


Relation Object
Relation object represents relationship between two fields in two tables. This is used to enforce referential integrity. The following are the collections, methods and properties of relation object.

Type Name Meaning
Collections Fields Contains the list of fields that are involved in relationship.
Methods CreateField Creates a field.
Properties Table Indicates the name of relation object’s primary table. The field in this table contains valid list of values.
ForeignTable Contains the name of the foreign table. The field in this table is to be validated.
Attributes Specifies the characteristics of the relation. For details please see on-line documentation.
Table 17.8: Members of relation object

The following example creates a relationship between PRODNO of SALES table and PRODNO of PRODUCTS table so that referential integrity is enforced.

Dim db As Database
Dim relnew As Relation

Set db = OpenDatabase("d:\vbbook\products.mdb")
Set relnew = db.CreateRelation("SalesProducts", _
"products", "sales", dbRelationUpdateCascade)
relnew.Fields.Append relnew.CreateField("prodno")
relnew.Fields!prodno.ForeignName = "prodno"
db.Relations.Append relnew

CreateRelation method has the following parameters.

• Name of the relation (SALESPRODUCTS).
• The table that is to be used as the look up table (PRODUCTS)
• The table that is to be verified
• The attribute - dbRelationUpdateCascade – specifies whenever the product number in Products table is updated then automatically product number in Sales is to be updated.

Note: Before a relation is created, both the fields participating in the relationship should be indexed.

Connection Object
A connection object represents a connection to ODBC data source. This is used only in ODBCDirect workspace.

ODBCDirect workspace
ODBCDirect workspace was introduced in Visual Basic 5.0. When you establish a connection through ODBCDirect workspace, you can access the database bypassing Jet Engine. This improves performance as well as flexibility.

At the time of creating workspace you have to explicitly specify the workspace as ODBCDirect workspace using dbUseODBC as the type of the workspace. The following are the collections, methods and properties of the collection object.

Type Name Meaning
Collections Querydefs Contains the list of Querydefs defined using the connection.
Recordsets Contains the list of Recordsets defined using the connection.
Method CreateQuerydef Creates a QueryDef object.
OpenRecordset Opens a recordset.
Execute Executes the given command by passing it to data source.
Cancel Cancels a command that is run asynchronously.
Close Closes the connection.
Properties Connect Contains ODBC connect string.
Recordseffected Returns the number of records effected by the most recently invoked Execute method.
Stillexecuting Returns true if an asynchronous query is still in execution.
Querytimeout Sets or returns a value that specifies the number of seconds to wait before a timeout error occurs when a query is executed on an ODBC data source.
Updatable Returns true, if database is updatable.
Transaction Returns true, if database supports transactions.
Database Returns the database object that is connected to the connection.
Table 17.9: Members of collection object.

Error Object
Contains the details of data access errors. Errors collection contains Error objects. The following are the properties of Error object.

Property Meaning
Description Returns the error message.
Number Returns the error number.
Source Returns the name of the object or application that caused the error.

The following example displays the list of errors related to most recently occurred error.

Dim db As Database
Dim err As Error

On Error Resume Next

Set db = OpenDatabase("d:\vbbook\products.mdb")
' Simulate an error
db.execute “ update prodcuts “
‘ display the list of errors
For Each err In DBEngine.Errors
Print err.Number, err.Description, err.Source
Next