Data Access objects (DAO) communicate with Microsoft Access and other ODBC complaint data sources through the JET engine. They provide properties and methods that allow to perform all the operation necessary to manage such a system, including the ability to do the following.

1. Create databases
2. Define tables, fields, and indexes
3. Establish relations between tables.
4. Navigate and query the database, and so on.

Figure: A typical remote data access using DAO and the JET engine.

The data Access objects (DAO) model is a collection of object classes that provide properties and methods for database programming. This model furnishes facilities for creating databases, defining tables, fields and indexes, establishing relations between tables navigation and querying the database, and so on.

The Microsoft jet database engine translates operations on data access objects into physical operations on he database files, handling all those mechanics of interfacing with the different supported databases. This approach simplifies access to the database and insulates from the underlying mechanics of retrieving and updating data. It affords great flexibility because the same objects, properties and methods can be used with a wide variety of supported database formats.

Through DAO and the JEF engine, Visual Basic recognizes three categories of database, and these are summarized below:

Visual Basic Database                                 Also called native databases, these databases

Files use the same format as Microsoft Access. These databases are created and manipulated directly by the jet engine and provide maximum flexibility and speed.

External Databases                                      These are indexed sequential access method (ISAM)

database in several formats, including Btrieve, dBase III, dBase IV, Microsoft Fox Pro version 2 dand 2.5 and paradox version s 3.x and 4

ODBC Databases                                        These include client/server databases that conform to

the ODBC standard, such as Microsoft SQL server and Oracle.

The DAO programming model provides the following features:

1. Advanced result set management.
2. A universal programming model that can access any ODBC database regardless
3. Key, static, and forward only scrolling snapshot cursor implementation.
4. The ability to create updateable cursors against complex result sets created as a join product.
5. Universal error management

When to use DAO

1. DAO is the only data access technology that supports 16 bit operations. If an application must run within a 16 bit environment, then DAO is the only choice.
2. If the application must access both native Microsoft Jet and ODBC resources, DAO provides a consistent programming model.

Accessing a database using DAO

A DAO based application generally follows the logic given below while accessing a data source:

• Create the work space Define the user session, including user identification, password, and database type (such as Microsoft jet or ODBC)
• Open the database Specifies a connection string for a particular workspace object, with information such as data source name and database table
• Open the recordset Runs and SQL query (with or without parameters) and populates the recordset.
• Use the recordset The query result set is now available to the application. Depending on the cursor type, the row data can be browsed and changed.
• Close the recordset Drops the query results and closes the recordset.
• Close the database Closes the database and releases the connection.

The following code shows how to open the database students and to close it.

Dim ws as workspace

Dim db as database

‘Setting ws to default workspace

Set we= DBEngine. Workspace

‘Setting db to students database using workspace’s open database’ method

Set db = ws Open database (“Students. Mdb”)

‘Some work with students database

‘Closing students database

db close

‘closing workspace

Ws.Close

About Remote Data Control (RDC)

The Remote Data Control (RDC) binds controls to an ODBC remote database. The remote Data Control is similar to the data control, except that it creates and manipulates RDO objects. RDO and RDC helps to access ODBC data sources through data aware or bound controls without going through the JET engine, resulting significantly higher performance and greater flexibility when accessing remote data sources.

Figure: The Remote Data control, RDO and bound controls.

The remote data control performs all operation on the current row. The remote Data Control automatically handles a number of contingencies, including empty result sets. Adding new rows, editing and updating existing rows; converting and displaying complex data types, and handling some types of errors.

The Remote Data control provides an interface between Remote Data Object (RDO) and data aware bound controls. With the remote Data it is possible to

• Establish a connection to a data source based on its properties
• Pas the current row’s data to corresponding bound controls
• Permit the user to position the current row pointer.
• Pass any changes made to the bound controls back to the data source
• Remote Data Object (RDO) and collections provide a frame work for using code to create flagship interface to relational ODBC data sources.

Figure: A typical remote data access using RDO.

RDO is specifically designed to deal with remote intelligent data sources such as SQL server and Oracle. RDO can execute ordinary table based queries, but it is especially good at building and executing queries against stored procedures.

The following advantages are associated with the RDO programming model:

• It is an universal programming model that can access any 32 bit level ODBC data source
• All remote databases such as SQL server and Oracle can virtually be accessed.
• Key set, static, dynamic and forward only cursors are implemented on the sever side.
• It gives the ability to create standalone rdo query and rdo connection objects and to associate queries with connections at design time
• It provides advanced result set management, including the ability to limit the number of retuned rows, and to handle the input, output and return value arguments of stored procedures.

Active X Data Objects (ADO)

 It is emerging as another data access alternative, which may replace the need for other interfaces. ADO enables to write a Client application to access and manipulate data in a database server through a provider. ADO’s primary benefits are ease of use, high speed, low memory overhead, and small disk footprint.

Figure: An ADO connection to a remote data source.

In ADO, the object hierarchy is de emphasized… unlike data access objects (DAO) and remote data objects (RDO), ADO allows creating objects independently; no longer having to navigate through a hierarchy to create objects. The ADO model also results in fewer objects, which allows for a smaller working set.

ADO is designed to eventually negate the need for all other interfaces. ADO is to specifically designed for ISAM or relational database access, but as an object interface to any data source. ADO is built around a set of core functions that all data sources are expected to implement.

The ADO programming model provides the following features:

Advanced record set cache management.

Different cursor types, including the potential for support of back end specific cursors.

Independently created objects.

Support for stored procedures with in/out parameter and return values.

Support for limits on the number of returned rows and other query goals.

Visual Basic also includes ADO Data control (ADODC), which works in the similar manner to the remote Data control, except that is uses OLE DB provider instead of ODBC driver.

Advantage of Active X Data Objects

Flat object models: ADO does not impose hierarchical object creation. That is to create a recordset object in DAO, a workspace object and database objects are to be created first. Then alone recordset object can be created. In ADO either this kind of hierarchical creation can be followed or its objects can be crated independently.

Less number of objects: ADO does not have large amount of objects like DABO and RDO, which lead difficulty in coding. ADO objects set is less than DAO and RDO, but more powerful.

Direct assignment Data bound controls: To fill data bound controls its data sources property can be set to a recordset object instead of setting to a data control (explained later in this chapter)

Enhanced performance by recordset object:  Indexed fields greatly enhance the performance of the Recordset object using find methods, and sort and filter properties.

Recordset persistence: Recordset data can be saved in a file. Later this persisted file can be used to recreate the recordset object.

The ADO Object model

The ADO object model’s hierarchy is given below.

Figure An ADO object model.

Each of the connection, command, recordset and field objects also has a properties collection.

ADO Data Control

The ADO data control user Microsoft ActiveX Data Object (ADO) to quickly create connections between data bound controls and data providers (any controls that feature a Data Source property). Data providers can be any source written to the OLE DB specifications.

Creating a sample data base application with ADO data control

This section describes steps involved in creating a sample data base application with ADO data control. The behavior of this application will be similar to those applications, which are created using data control and RDC in the previous chapters. But the different lies in the design process. The following steps explain to design a simple application with the ADO data control.

To create a simple database application that uses ADO data control

Draw an ADO Data Control on a form (The Icon’s tooltip is “ADODC”)

If the control is not available in the toolbox, press CTRL + T to display the components dialog box. In the components dialog, click Microsoft ADO data control.

On the toolbox, click the ADO data control to select it. Then press F4 to display the properties window.

In the properties window, click connection string to display the connection string dialog box.

 

Creating an ADO database with a Front End

Using the ADO data control in your applications is no more difficult than using the standard DAO Data control. In the following example, you will create a sample front end for an ADO database. Although this application is very simplistic, it does illustrate how easy it is to create an application using ADO. To create the front end for the Database, perform the following steps.

1. confirm that you have a valid. MDL files it you will be using an OLE DB Data source.
2. start a new project. On the form, place an ADO control. If the ADO control. Is not available in your toolbox, you can add it form the components dialog box and select the Microsoft ADO data control option shown in figure.

Figure: The ADO Data control is added to the toolbox after the Components

Dialog box is closed

1. In the properties window of the ADO control, click the ellipsis button on the connection string property to display the connection string dialog box. This is shown in figure 14.2

Figure: Property pages allow you to enter the ODBC source and connecting string

1. Select the General tab. If you have created a Data link file (MDL), select the use data link file check box and click the browse button to locate the MDL file. Alternatively, you could select either the use ODBC Data source name or use connection sting check boxes to create a connection to your database.
2. In the properties window of the ADO control, click the ellipsis button to display the record source dialog box. In the command Text (SQL) text box, enter the following SQL statement.

SELECT *FROM Authors WHERE Au_ID=72

This set the record source property to a SQL statement. This is shown in

Figure- SQL statements can also be enter via the Record Source tab on the Property

Pages dialog box

1. place two labels and two text box on the form. The captions of the text boxes should be Author and Author ID

1. Set the Data source property for Text1 and Text2 to the name of the ADO control on the form.

1. In the property window of Text1, set the data field property to author.

1. Set the data field property of Text2 to Au_ID

10. Press F5 to run the application. The completed form should resemble the one in figure

The ADO data control can be major resource hog. The ADO control will automatically use at least two connections for the first ADO control on a form. Additional ADO controls on the same form require one connection each.

An alternative to using the ADO data control is to set the properties programmatically. The sample code segment is listed below.

Example

Private Sub Form_Load ()

With ADODC1

Connecting string = “driver = {SQL server};” &_

“Sever=orion; uid=sa;pwd;database=pubs”

Record source – “Select *From Authors where Au_ID = 72”

End with

Set Text1.Datasource = ADODC1

Text1.Dtafield = “Author”

End sub

Connecting to a database

ADO can be used to connect with a OLE DB data source and non data base types also.

A sample code is given to connect with SQL sever database

Dim con as ADODB. CONNECTION

Dim cmd as string

Cmd = “provider = SQLOLEDB.1;”

BOF and EOF properties

• BOF (Beginning Of File):  This property returns a Boolean value that indicates whether the current record position is before the first record in a Recordset object.

 

• EOF (End Of File): This property returns a Boolean value that indicates whether the current record position is after the last record in a Recordset object.

 

• The BOF property returns true if the current record position is before the first record and False if the current record position is on or before the last record.

• The EOF property returns true if the current record position is after the last record and False if the current record position is on or before the last record.

The BOF and EOF properties can be used to determine whether a recordset object contains records or whether the cursor has gone beyond the limits of a recordset object when one of a move method is used to move the record pointer from record to record.

If both the BOF and EOF property is True, it means there is no current record. If an attempt is made to move beyond the limits of a recordset object then an error will occur. So these properties can be used in conjunction with the move methods. For example, to prevent he move operation beyond the last record the following code may be used.

rs.MoveNext

If rs. EOF then

MsgBox “End of the file!, ponter being moved to

last_record.”

Rs.MoveLast

/End if

At the end of the record set rs, its EOF property is set to True. So, as the if condition satisfies the move next method will be roll backed. Similarly the BOF property can be used with the move previous method.