VB 6.0 Persistent Recordset

When you establish connection with a database such as Microsoft Access or SQL through Visual basic 6 form. You must extract a set of records from one or more tables. These records are stored in a VB 6 object called a recordset. There are two ways to get recordset.

1. ADODC ( ActiveX Object Data Control )
2. ADODB ( ActiveX Object Data Base)

The next section I will discuss more about it.

ADODC vs ADODB

The ADODC provides a control that that creates a “connection string” and interface with the database. It skips the programming and helps to connect controls like textbox to connect to database directly.

Whereas ADODB is a class library with lot of methods and objects to connect to external databases. In other words, using methods and objects you can connect to any database and retrieve recordset.

To use the ADODC and ADODB, you must install following components under Project > Components > Controls Tab.

Select two items – Microsoft ADO Data Control 6.0 and Microsoft Data Grid Control 6.0.

Click Apply and then click OK.

You will find that the VB Tool box has new sets of controls in the left hand side of VB 6.0 IDE. See image below.

Recordset Connection Problem

Once you establish connection with the database and retrieve recordset , you can insert, delete, update or display data anyway you like. However, this will not work if your connection is broken or you want to work offline. As long as the database connection state is open, you can continue without problem.

Suppose you established connection with MS Access database and display your data in a data-grid control as shown in the following example.

Form Design

This form has two buttons and a data-grid control to display data. The show data button will retrieve data from the database table ‘student‘ and display in the data-grid control.The second button, disconnect will close the recordset and the connection to the database.

Create MS Access 2003 Database with table ‘student’

The first step is to create a MS access database and create a simple table – students. Fill some random data for testing.

Code for Controls

Now you need to write appropriate codes for both the controls.

Show Data

Private Sub cmdShowData_Click()
Call myconnect
rs.CursorLocation = adUseClient
'open student table
rs.Open "student", con, adOpenKeyset, adLockBatchOptimistic, adCmdTable
Set DataGrid1.DataSource = rs
End Sub

Disconnected

Private Sub cmdDisconnect_Click()
rs.Close
con.Close
End Sub

Code under ‘General’ to establish database connection

Option Explicit
Public con As New ADODB.Connection
Public rs As New ADODB.Recordset
Public constr As String
Public Sub myconnect()
constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\VBProjects\Temp\temp.mdb;Persist Security Info=False"
con.Open constr
MsgBox "Connected"
End Sub

Testing Our Program

When you run the program, you will find that clicking show data will populate the data-grid control successfully.

However, as discussed earlier the recordset is available as long as the connection to the database is open. If you click the Disconnect button the data will disappear.

Therefore, the access to data set depends on the connection. We need data available even after the connection is closed or recordset is closed.

Persistent Recordset

The persistent recordset follow a very simple principle of saving your recordset data in a file, so that even if you break the connection with database it does not matter.

Now we rewrite the same program with persistent recordset.

Form Design

This form has two buttons and a data-grid control. The first button is Show Data and the second button is Save Data.

The save data button will run a query to the database and get the recordset. It will open a file and save the data immediately. The connection is closed.

The show data will not query the data but open the file and get the recordset. In this way, it does not matter whether the connection to the database is available or not, we can continue to work offline.

Database

There is not change in the database. You may use the same database and table which was used for the program earlier.

Code for controls

Save Data

Private Sub cmdSave_Click()
Call loadcon
rs.CursorLocation = adUseClient
rs.Open "student", con, adOpenKeyset, adLockBatchOptimistic, adCmdTable
If Dir$("C:/VBProjects/Temp/mydata.xml") <> "" Then
Kill "C:/VBProjects/Temp/mydata.xml"
End If
rs.Save "C:/VBProjects/Temp/mydata.xml", adPersistXML
rs.Close
con.Close
End Sub

Let us discuss the code in detail.

Call loadcon
rs.CursorLocation = adUseClient
rs.Open "student", con, adOpenKeyset, adLockBatchOptimistic, adCmdTable

The above code will establish the connection to database which is MS Access 2003. It will store the entire table ‘student’ from the database to a recordset called rs.

adUseClient– it is the cursor location that change, navigate or update data. This cursor works on the client side.

con – name of the connection object.

adOpenKeyset -This is the cursor type when you access the recordset. This specify which records are visible or accessible, how efficient it is.

adLockBatchOptimistic– It is lock type required to edit the record. Optimistic batch update lock is not supported when cursor is adUseClient.

adCmdTable – It is a commandType that will internally generate a query that will fetch a table with all columns.

If Dir$("C:/VBProjects/Temp/mydata.xml") <> "" Then
Kill "C:/VBProjects/Temp/mydata.xml"
End If

The persistent recordset uses rs.save command to create a file and save the results. Each time it attempts to create a new file. It there is an exiting file, you get an error. The above command will check for existing file and delete it.

rs.Save "C:/VBProjects/Temp/mydata.xml", adPersistXML

The above code will create a new file and save the recordset data. Now, the file is an XML file because we specified adPersistXML parameter which save the query into an XML file.

There is a default method to save the recordset in a DAT file using parameter – adPersistADTG

rs.Save "C:/VBProjects/Temp/mydata.dat", adPersistADTG

This file can be retrieved in the same manner as an XML file.

rs.close
con.close

The last part of code is very important as it closes all the connection. However, it does not concern us because the recordset is stored in the file.

Show Data

The show data button will open the recordset from previously saved file and display it to the data-grid.

Private Sub cmdShow_Click()
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "C:/VBProjects/Temp/mydata.xml", , adOpenKeyset, adLockBatchOptimistic, adCmdFile
Set DataGrid1.DataSource = rs
End Sub

The most important code above is

rs.Open "C:/VBProjects/Temp/mydata.xml", , adOpenKeyset, adLockBatchOptimistic, adCmdFile

The recordset is opened from a file and the command type specified is adCmdFile.

The above result is persistent because the database connection is already closed. Note that the code to establish a database connection will remain same as the previous program.