Working with Data Access Objects (DAO)


*  DAO object model
*  DBEngine object
*  WorkSpace object
*  TableDef object
*  QueryDef object
*  Index object
*  Field object
*  Connection object
*  Error object



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.
  
Figure 17.1: 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

Working with Data Access Objects (DAO)


*  DAO object model
*  DBEngine object
*  WorkSpace object
*  TableDef object
*  QueryDef object
*  Index object
*  Field object
*  Connection object
*  Error object



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.
  
Figure 17.1: 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