Show Database Record in MSHFlexGrid through ADODC in VB6.0

In this post, we will define process to learn about database connection and use MS_Access with MSHFlexGrid to show record through ADODC. First, drag ADODC component from Toolbox. If you want to show MS-Access record in MSHFlexGrid, create MS-Access database.

Create Database and Table:

Suppose my database name is "Record_Details.mdb". Now, we create table in this mdb file with table name "Record_Tbl" and add some Fields as shown bellow.

Go to ‘Design View’ of "Record_Tbl"and add following field.

Field Name	Data Type
Srno	Number
E_Name	Text
E_Age	Text
E_Mono	Text

After that save design view and open "Record_Tbl" table in ‘Datasheet View’ and add some records in this table.

Record_Tbl
Srno	E_Name	E_Age	E_Mono
1	ABCD	23	000000000
2	EFGH	32	111222222
3	HIJK	26	222233333
4	PQRS	18	444555555

Create Column Header with Name:

Now, create Column header name with ‘Srno’, ‘E_Name’, ‘E_Age’, ‘E_Mono’. And add column header through Form’s Load event code.

Here, change MSHFlexGrid1 Name as "DataMSFGrid".

Private Sub Form_Load()

   With DataMSFGrid

      .Clear
      .Rows = 2
      .TextMatrix(0, 0) = " Srno "
      .TextMatrix(0, 1) = " E_Name "
      .TextMatrix(0, 2) = " E_Age "
      .TextMatrix(0, 3) = " E_Mono "

   End With

End Sub

Controls Description

Drag control and establish DB Connection:

After that add command button and set this Caption as ‘Show DB Data’ and Name as ‘Show_Data’. First we establish connection. You have drag ADODC control to Form1. After that write code on ‘Show_Data’ command button’s click events.

MSHFlexGrid items Edit or delete code

Connection CODE:

Private Sub Show_Data_Click() 

Dim cnn As New ADODB.Connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Record_Details.mdb;Persist Security Info=False"

End Sub

Retrieve DB Records:

After write code for connection, you have to write code for retrieve data from database to this control. You can write code just below to connection code. But, you have to define data set for it. Mainly 2 ADODB objects you have to define.

1 ADODB.Connection
2 ADODB.Recordset

Now come to the code of showing data.

Code:

Private Sub Show_Data_Click() 

   'cnn use for connection
   Dim cnn As New ADODB.Connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Record_Details.mdb;Persist Security Info=False"

   'rcset use for table's data
   Dim rcset As New ADODB.Recordset

   Dim i As Double
   i = 1

   'open recordset through Query
   rcset.Open "Select * from Record_Tbl;", cnn, adOpenKeyset, adLockOptimistic

   'Loop recordset process till Last data
   While Not rcset.EOF

       'Increament row for every data
       DataMSFGrid.Rows = i + 1

       'Apply condition if particular Field's data is Null or Blank.
       If Not rcset!Srno = "" Then

            DataMSFGrid.TextMatrix(i, 0) = rcset!Srno

            Else

            DataMSFGrid.TextMatrix(i, 0) = ""

       End If

       If Not rcset!E_Name = "" Then

           DataMSFGrid.TextMatrix(i, 1) = rcset!E_Name

           Else

           DataMSFGrid.TextMatrix(i, 1) = ""
 
        End If

        If Not rcset!E_Age = "" Then

           DataMSFGrid.TextMatrix(i, 2) = rcset!E_Age

           Else

           DataMSFGrid.TextMatrix(i, 2) = ""

        End If

        If Not rcset!E_Mono = "" Then

            DataMSFGrid.TextMatrix(i, 3) = rcset!E_Mono

            Else

            DataMSFGrid.TextMatrix(i, 3) = ""

        End If

       'Increament i for increament row of MSHFGrid

        i = i + 1

        'MoveNext is use for go to Next data of Table.

        rcset.MoveNext

       'DoEvents use during loop for hanging situation

        DoEvents

        'wend is use for closing Loop

   Wend

   'Close recordset

   rcset.Close

   'cnn.Close is use for close connection

    cnn.Close

End Sub

Note: DoEvents is very important to use inside loop code, because it allows your running application not hang during loop process.

Design Column width, Color, Font and Grid style.

There is image shows the MS-Access data.

Database Table Record show in MSHFlexGrid

Table's data

Show DB Record at Run time:

Now come to the output of run time. The same record of MS-Access table will present in running application's control. An important point is that it is bit slower in processing than Listview.