Using DataGrid and Data Controls to Access an Access Database

• Download source files and sample database - 7.82 KB

Introduction

Hi all.. I write this article as a reply to Anonymous in the CodeProject discussion board.

Anonymous wrote:

Friends, I am very new to programming and have started with a new project which consist of a form which has 2 calendar controls. I wanted to know how can I fetch data that was inputted into a database between two dates and show it into a grid. The date selection is dynamic. Any help will be appreciated.

The program is not something fancy... but I think it can be useful for beginners in VB6 like our Anonymous friend.

The Project

I created a database using Access XP and saved it in Access 97 format. It contains one table named table1.

Table1 has 3 columns:

1. ID: AutoNumber
2. Name: Text .. max. Length=50
3. BirthDate: DataTime

The main Form and Controls

frmMain: The main form... nothing special to say...

Data: A data control with properties:

• Visible=False
• DatabaseName=”Path of database”
• Connect=Access (the default)

Cal(): Array of calendar control that contains 2 elements.
(You can create a control array by dropping a control over the form and copy it, then paste it on the form.)

DBGrid: A data Grid control:

• dataSource=Data

Now... let’s come to the easy code needed to update data each time the user selects a new date from a calendar control.

This is the code for Cal_AfterUpdate:

Private Sub Cal_AfterUpdate(Index As Integer)
Dim strDate0 As String
Dim strDate1 As String
Dim strSQL As String

strDate0 = Cal(0).Day & "/" & Cal(0).Month & "/" & Cal(0).Year
strDate1 = Cal(1).Day & "/" & Cal(1).Month & "/" & Cal(1).Year

strSQL = "SELECT * FROM table1 WHERE BirthDate Between #" _
        & strDate0 & "# AND #" & strDate1 & "#"

Debug.Print strSQL

Data.RecordSource = strSQL
Data.Refresh

End Sub

That is all the required coding... thanks to the data control and data biding.

How It Works

1. I declared two string variables to hold the dates... I composed them using the calendar control properties.
2. strSQL is the query used to let the jet engine (which is the component that accesses data) know what data we need.
3. Debug.Print strSQL is called for debugging purposes, so I can check if the query is formed well.
4. Assign strSQL value to the RecordSource property of the data control and refresh data... Then we can see the extracted result set in the grid control.

Any comments are welcome.

History

• 8^th January, 2003: Initial post