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 (figure 17.1). 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.

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