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.
- ADODC ( ActiveX Object Data Control )
- 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.
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.
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
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.
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.
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.
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
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.
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.
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.