Data Access

Over the years, Visual Basic has become an application development tool used for developing Client/Server applications. This is an area of Visual Basic that seems to be getting better and better with every new version. For instance, Visual Basic 6.0 has introduced a new way of accessing data through ADO & OLEDB. And Microsoft says you stick to ADO because that is the future. Old methods like DAO and RDO are already obsolete.  If you are wondering about all the stuff I mentioned, I would say, "do not worry". All that is part of data access and we will thoroughly discuss about DAO, RDO and ADO in the next couple of chapters.

What is Data Access?

Data access is a feature of Visual Basic that allows you to access and manipulate any database from Visual Basic. The database may be either MS-Access database or FoxPro database or it may also be any of the relational databases such as Oracle. You can develop applications in Visual Basic that can access data in a database. The database may be on the same machine as the application or it may be on database server that is far away from Visual Basic application. Whatever may be the case, you can access and manipulate the data using applications written in Visual Basic.

The following are the various ways of access database.

Data Access Objects (DAO)

This is an object model that has a collection of objects using which you can access a database. This model gives complete control on the database.  This model uses Jet Engine, which is the native database engine used by Visual Basic and MS-Access. This was the first model to be used in Visual Basic. Though it is possible to access any database using this, it is particularly suitable for MS-Access database and not suitable for ODBC data sources such as Oracle and MS-SQL Server.  So, Microsoft later introduced RDO.

Remote Data Objects (RDO)

These objects are only used to access ODBC data sources such as Oracle. These objects access databases that are on remote machine (database server). This object model has less number of objects compared with DAO and more suitable for accessing remote databases.  We will discuss more about RDOs in chapter 18.

ActiveX Data Objects (ADO)

Microsoft has introduced a new object model called ActiveX Data Objects (ADO), which is based on ActiveX technology, for the first time in Visual Basic 6.0. This object model has very few objects and it is based on OLE DB interface. OLE DB interface is a new interface (replacing ODBC and others), through which you can access data of all formats in the same manner. ADO uses OLE DB providers to access the data. That means each database is accessed through OLE DB provider. And ADO provides the programming framework to access OLE DB. ADO is also much easier to deal with.

That is all about the introduction to data access methods. If you could not understand every part of that, don’t worry. As we unfold things, you will start understanding all about data access. But one thing is for sure. Visual Basic has got so many ways of accessing data (various object models and controls). It is certainly going to confuse a beginner. Also remember, that there is no single method that is efficient in all circumstances. Given the task on hand, choose the best-suited method.  My personal suggestion is – use ADO as much as you can. Because that is what Microsoft heralds as the future.

Accessing MS-Access database

First, let us understand how to access data using a very simple and old method – using data control.  Data control is one of the standard controls used to provide access to data. Data control internally uses DAO to access data. It means when we use data control, we are using DAO to access data. As we will understand later, though we use data control initially, without using DAO objects and their methods you cannot go much further with simple data control.

Accessing BIBLIO.MDB using data control

Let us understand how to access MS-Access database, BIBLIO.MDB, using data control.  A database is a collection of tables, where each table contains data related to an entity. For example, Authors table in BIBLIO.MDB contains details of authors; Publishers table contains details of publishers and so on.

Note: BIBLIO.MDB and NWIND.MDB are two sample databases supplied along with Visual Basic.

To access the data of AUTHORS table of BIBLIO.MDB:

1.      Place Data Control on the form.

2.      Invoke properties window and select DatabaseName property and click on three dots displayed on the right of the property.

3.      Visual Basic invokes DatabaseName Dialog Box. Select BIBLIO.MDB file using the dialog box. The file is normally placed in D:\Program Files\Microsoft Visual Studio\VB98\BIBLIO.MDB. However, the exact path of this file may be different on your system.

4.      The complete path of the file is placed in Properties windows.

5.      Select RecordSource property and click on down arrow to the right of the property.

6.      Visual Basic displays the list of tables from the selected database – BIBLIO.MDB.

7.      Select AUTHORS table from the list of tables.

8.      Change Caption property to “Author Details”

Note: You may also use SELECT command in RecordSource property of the data control.

That’s all that you have to do so far data control is concerned at this stage.

Displaying data using Data bound controls

Data bound controls are controls that are bound to columns of a table via data control. Data bound controls are used to display and modify the data of the database. For instance, a textbox could be used to display and change the data of the corresponding column in the table.

Data bound controls have DataSource and DataField properties that allow programmer to bind them to database.

Let us now take three text boxes to display author id, name and year born. So, place three text boxes and required labels (as shown in figure 15.1) on the form. Remember, the data control is already on the form.

Two properties that you have to change to bind a control to data control are – DataSource and DataField.

DataSource property specifies from where the data bound control takes data. The name of the data control is given there.

DataField property specifies which field of the data control is bound to the control (i.e., which field is accessed and manipulated). In our example, we have three fields in our data control – AU_ID,  NAME, and YEAR BORN. The fields of the data control come form the RecordSource of the data control.

Place a command button at the button (as shown in figure 15.1) to quit the application.

Change properties of labels and command button.

Change the properties of three textboxes as follows:

Control	Property	Value
Text1	Datasource	Data1
	Datafield	Au_id
	Name	txtauid
Text2	Datasource	Data1
	Datafield	Author
	Name	txtauthor
Text3	Datasource	Data1
	Datafield	Year born
	Name	txtyb

Test Run

Run the project to test how it works.

1.      Run the project using F5.

2.      You should see first record of AUTHORS table (as shown in figure 15.1).

 

Figure 15.1: Form displaying first record of Authors table.

3.      Click on  right triangle to move to next record.

4.      Change either name or year born and click on right triangle.

5.      Data control saves the change to the database.  To confirm that data control has really saved the changes you have made, click on left triangle to move to previous record. You will see the changes made by you.

6.      Right most button takes you to last record and left most button takes you to first record. Please see figure 15.2.

Adding a new record

We have seen how to display the data and change the existing data using data control and data bound control. Now let us see how to add a new record to AUTHORS using data control.

Before we understand how to add a new record to AUTHORS table, we must understand RecordSet object. At runtime, data control creates an object called RecordSet (an object in DAO), which contains the data that is taken from Recordsource of the database. In our example, recordset created by data control contains the details of authors. When you modify data in data bound controls, data control modifies data in recordset. And changes that are made to recordset are made to the corresponding table in the database.

So, now we have to add a new record to recordset. As a result an empty record is created at the end of the recordset and automatically data control empties all bound controls. If you enter data into bound controls and move to next record the data entered into bound control is added to authors table as a new record.

Let us add a command button to add a new record.  Place a command button and change the following properties of that command button.

Caption = “Add Author”

Name    = cmdAdd

Write the following code for click event of command button.

Private Sub cmdadd_Click()

  'add new record to recordset

  Data1.Recordset.AddNew

  'set focus to author name

  txtauthor.SetFocus

End Sub

Listing 15.1: Code for add button.

 

Figure 15.2: Data Control

As we have already seen that data control creates a recordset. Data control accesses the recordset using RecordSet property. Recordset object has a collection of methods and properties. We have used AddNew method to add a new record to recordset.

We set the focus to author name and not to author id because author id is an AutoIncrement field. That means, it’s value is automatically incremented whenever a new record is added.  So you need not enter any value for author id.

Here are the steps to add a new record using Add Author command button on the form:

1.      Run the project and click on Add Author button.

2.      You see a new author id in txtauid textbox .

3.      Focus is set to txtauthor. Enter data into author's name and year born fields.

4.      Click on next record button of data control (right trigangle) to save the new record and move to next record of the record from where new record is added.

5.      Then click on move last button to move to last record. There it is. The record you have entred you entered should appear as the last record.

Note: In relational databases, position of the record is not important. Because you never access records using their position. So there is no way to add a record in the middle of the table. A new record always added at the end of the table.

 

Deleting  a record

Let us further enhance our project to include the ability to delete the current record. We will add a button, which deletes the current record.  Again we have to manipulate recordset. If you delete a record from your recordset that record is automatically deleted from the corresponding table  in the database.

The method used to delete current record is Delete. Delete method deletes the record in the recordset but doesn’t clear the data from bound countrols. So to clear data from bound control we have to move out of deleted record. So we will move to next record using MoveNext method.  But what if we are deleting the last record of the record set? Moving out of last record will land you in EOF position. When you move to EOF (End of file) position, as there is no data, the bound control will be empty. That means after moving to next record we have to check whether we reached EOF. If so, we have to move to the last available record.

Well, it is upto you to decide what you want to do in this situation. You might want to go to the first record as well.

Let us find out the steps to be taken to delete current record.

1.      Delete current record

2.      Move to next record

3.      If next record is End of file then move to last available record

Here is the code for delete button.

Private Sub cmdDelete_Click()

 With Data1.Recordset

   ' delete current record

   .Delete

   ' move to next record

   .MoveNext

   ' if you have reached EOF then go to last record

   If .EOF Then

     .MoveLast

   End If

 End With

End Sub

Listing 15.2: Code for Delete button.

EOF property of the recordset will be true if control reaches end of the recordset. MoveNext method moves to next record and MoveLast moves to last record of the recordset.

Execute the following steps to delete a record:

1.      Run the project and move to the record that you want to delete.

2.      Click on Delete button. The record is removed and the next record appears.

3.      Now test deletion of last record by going to last record using last record button of data control and click on Delete button.

4.      Last record will be deleted and its previous record will be displayed. Because once last record is deleted its previous record will become the last record.

That’s all for deletion.

Searching based on a condition

The project needs one more enhancement. Let us provide a new facility to user by which user can enter any condition and move to first record that satisfies the condition.

Add another command button to form and change the following properties.

Name = CmdSearch

Caption = “&Search…”

Write the following code for click event of  search button.

Private Sub cmdsearch_Click()

Dim con As String

 ' take condition from user

 con = InputBox("Enter a condition", "Condition")

 With Data1.Recordset

   .FindFirst con

   If .NoMatch Then

     MsgBox "No record found"

   End If

 End With

End Sub

Listing  15.3: Code for Search button.

Test run

Now run the project to test searching.

1.      Run the project usig F5.

2.      Click on search button and enter  au_id = 15  as the condition in inputbox and click on Ok.

You will see details of author with author id 15.

3.      Again click on Search button and enter  au_id = 10 as the condition  and click on Ok.

4.      This will display the error message as no record with author id 10 is existing.

Let us look the last case a bit closely. We have searched for record with author id 10. As author id 10 is not found NoMatch property is true and error message is displayed. But in the process data control has moved to first record of the recordset. The ideal behavior here would be going back to the record from where search was inititated. That means if search succeeds, user should see the first record that satisfies the condition. If condition is not satisfied then user should get error message and the position of the record should not be changed.

Here is the revised code of search button.

Private Sub cmdsearch_Click()

Dim con As String

Dim currec As Variant

 ' take condition from user

 con = InputBox("Enter a condition", "Condition")

 With Data1.Recordset

   currec = .Bookmark

   .FindFirst con

   If .NoMatch Then

     .Bookmark = currec

     MsgBox "No record found",, "Search"

   End If

 End With

 End Sub

Listing  15.4: Revised code for Search button.

BookMark property of the recordset could be used to get unique identifier that identifies the current record. Bookmark is also used to move to the record whose bookmark is assigned to it.

We have copied bookmark of the current record before starting search into currec variable. If search is not successful, then before displaying error message we go back to the record where search began using bookmark that was saved.

1.      Now run the program and move to any record other than first record.

2.      Click on Search button and enter  au_id = 10 in inputbox and click on Ok.

As search fails error message is displayed and control remains at the record where search began. See figure 15.5.

 

Figure  15.5: Error message when search failed.

Here is complete code of the application.

Private Sub cmdadd_Click()

  'add new record to recordset

  Data1.Recordset.AddNew

  'set focus to author name

  txtauthor.SetFocus

End Sub

Private Sub cmdDelete_Click()

 With Data1.Recordset

   ' delete current record

   .Delete

   ' move to next record

   .MoveNext

   ' if you have reached eof then go to last record

   If .EOF Then

     .MoveLast

   End If

 End With

End Sub

Private Sub cmdquit_Click()

  Unload Me

End Sub

Private Sub cmdsearch_Click()

Dim con As String

Dim currec As Variant

 ' take condition from user

 con = InputBox("Enter a condition", "Condition")

 With Data1.Recordset

   currec = .Bookmark

   .FindFirst con

   If .NoMatch Then

     .Bookmark = currec

     MsgBox "No record found", , "Search Error"

   End If

 End With

End Sub

Listing 15.5: Code for entire application.

This project demostrates how to retrieve records, add new records, delete current record, and search for the record based on the given criteria. All that with mininum code.  You can perform various other operations using recordset. We will discuss more about recordsets later but for the time being let us understand various methods and properties and events of the data control.

Properties, Methods and Events of Data Control

Now, let us understand properties, events and methods of data control. We have already used DatabaseName and Recordsource properties of the data control. Here we will understand other properties, methods and events of data control.

Properties of Data control

The following are important properties of data control.

Property	Meaning
Databasename	Contains the name of the database that is to be accessed.
Recordsource	Contains the name of the table that is to be accessed. It may also contains SELECT command.
Readonly	If set to true, doesn’t allow the data to be updated.
Exclusive	If set to true, doesn’t allow any other application to open the database until you close the database.
BOFAction	Specifies the action data control takes when BOF property of the underlying recordset is true.
EOFAction	Specifies the action data control takes when EOF property of the underlying recordset is true.
Connect	Specifies the type of database accessed.
DefaultCursorType	Specifies the type of cursor driver used by datacontrol. Applicable only when ODBC Direct is used.
DefaultType	Specifies whether Jet Engine is to be used or ODBC Direct is to be used.
RecordsetType	Specifies the type of record set to be used. See types of record sets in chapter 17.

Table  15.1:  Properties of data control.

Methods of Data Control

The following are the methods that are specific to data control.

Method	What it does?
Refresh	Recreates the underlying recordset with current Databasename, Recordsource and other related properties.
UpdateControls	Updates the data bound controls by taking data from current record of the recordset.
UpdateRecord	Saves the current data of bound controls to data. This method doesn’t cause Validate event.

Table 15. 2: Methods of Data Control.

Events of Data Control

The following are the events that are specific to data control.

Event	When does it occur?
Reposition	Occurs each time data control moves to a different record in the underlying recordset. This event occurs after the new record (the record to which you are moving) becomes the current record.
Error	Occurs because of data access errors. Provides the error number and allows you to either display the error message or ignore the error and continue.
Validate	Occurs before a different record becomes the current record; before the Update method (except when data is saved with the UpdateRecord method); and before a Delete, Unload, or Close operation. Provides information regarding the operation that caused the event and whether bound data has changed.

Table 15. 3: Events of Data Control.

The following example changes the label of data control to current record number/ number of records in the data control.

Private Sub Form_Activate()

    ' Move to last to update RecordCount property of Recordset

    Data1.Recordset.MoveLast

    ‘ Get back to first record.

    ‘ Because in the beginning control must be at the first record.

    Data1.Recordset.MoveFirst

End Sub

Private Sub Data1_Reposition()

      Data1.Caption = Data1.Recordset.AbsolutePosition & "/" &  _

                              Data1.Recordset.RecordCount

End Sub

Listing 15.6: Code to display current record position.

Next we will discuss about the properties, methods of the recordset object.

Properties and Methods of RecordSet object

RecordSet object contains the set of records retrieved from database.

The following are commonly used properties of recordset object.

Property	Meaning
EOF	Returns true if record pointer reaches end of recordset. End of recordset mark is the position that is after the last record of the recordset (figure 15.4).
BOF	Returns true if record pointer reaches beginning of the recordset. The position before the first record is called as beginning of the recordset (figure 15.4).
AbsolutePosition	Returns or sets the relative record number of the recordset object’s current record.
BookMark	Sets or returns a bookmark that uniquely identifies the current record in a RecordSet object.
EditMode	Returns the editing state of the recordset. The valid options are none, editing in progress, and addition is in progress.
Filter	Contains the condition that is to be used to fileter records when recordset is subseqently reopened using OpenRecordSet method.
LastModified	Returns the bookmarks of the most recently editied or added record.
LockEdit	Specifies whether optimistic lock (where records are locked when you used Edit method) is to be used or pessimistic locking (where records are locked only when Update method is used). Setting it to true chooses pessimistic lock and setting it to false chooses optimistic lock.
NoMatch	Returns true if one of the most recently used  Find methods (Findfirst, Findnext, FindLast, FindPrevious) or Seek method failed.
RecordCount	Returns the number of records accessed in the recordset. If the recordset is of Table-type recordset then returns the number of records in the recordset. To get accurate count, prior to using this use MoveLast method.
Sort	Contains the order in which records are to be arranged if recordset is reopened.

Table 15.4: Properties of RecordSet object.

Methods of RecordSet Object

The following are commonly used methods of RecordSet object.

Method	What it does?
AddNew	Adds a new record at the end of the recordset.
CancelUpdate	Cancels any pending updates that were made after Edit or AddNew methods but before Update method.
Close	Closes recordset.
Delete	Deletes the current record in the recordset.
Edit	Makes the current record ready for editing.
FindFirst	Searches for the given criteria from the beginning of the recordset.
FindNext	Searches for the given criteria from the current record of the recordset.
FindLast	Searches for the given criteria from the end of  the recordset in backward direction.
FindPrevious	Searches for the given criteria from the current record of the recordset in backward direction.
Move	Moves the record pointer in the recordset by the given number of records. The movement is relative to the current record.
MoveFirst	Moves to first record.
MoveNext	Moves to next record.
MoveLast	Moves to last record.
MovePrevious	Moves to previous record.
OpenRecordSet	Opens a  recordset with the information given, such as source and type of recordset.
Requery	Updates the recordset by reexecuting the query on which the recordset is based.
Seek	Used to seach for a value in the current index. Applicable only for recordsets of type table-type and an index is defined for the underlying  table.
Update	Makes the changes made to underlying tables permanent

Table  15. 5: Methods of RecordSet object.

Some of the properties and methods of the recordset object have already been used in this chapter. Some more will be used in the remaining  chapters. One important thing to note about recordset object is, it is an object in the object model of DAO. As I already mentioned in the beginning of the chapter that DAO is getting out-dated. So the faster you move onto ADO the better it is. However, you can leverage your knowledge of DAO in ADO. For instance,  even ADO contains recordset object with similar functionality.  So if you understand DAO, understanding ADO will be easier later.