Create A Report In VB 6 With MS Access Database

You can create reports from a database using visual basic 6. To create this project, you need visual basic 6.0, Microsoft Access 2003 and above, Windows XP and above as operating system. In this example project, we do the following

• Create a database with Microsoft Access
• Create a data project in visual basic 6
• Connect the vb 6 project to the database
• Import all the fields and data to generate a report
• Test our project and make sure it works correctly.

Create A Database with Microsoft Access

The first step in this project is to create a simple database with a single table in Microsoft access. For the purpose of this exercise, we are using Access 2013.

Create a blank database called product_list and save it as product_list.mdb. The database must be in access 2002-2003 format (.mdb).

In the database, create a table called Products and insert following data.

You can choose data types for each field as per your wish. It does not affect the project because this is the only table we are going to use.

Create A Data Project in Visual Basic 6

Once the database is ready, our next step should be to create a data project in visual basic 6 software. As soon as you launch the VB 6, you get an option to select the type of projects you want to start.

Choose “Data Project”. See the image below.

The first thing you must do is to double click the “DataEnvironment 1” under Project Explorer windows.

This will bring the DataEnvironment1 windows.

Connect The DataEnvironment1 to Product_list Database

From the DataEnvironment1 windows, right-click the Connection1, and go to properties.

The data link properties will open up. You must select ” Microsoft Jet 4.0 OLE DB Provider” and click Next.

The next tab is Connection, under ‘Select or enter a database name’, click the (…) to browse to the database product_list.mdb and select it.

The connection must be successful if previous steps are done correctly. If connection fails, check the error and fix it. Click OK to close the data link window.

You are back to DataEnvironment1 window.

Import All Fields to DataReport1

Before we import fields from our Product table in product_list database. We must create a command1 in DataEnvironment1 window.

Right click the Connection1 and select Add Command option shown below.

Right click Command1 and click Properties. The Command1 properties will open, you must select ‘Database Object’ as Table under Source of data.

Select the table “Products” under Object Name.

The table fields will appear under Command1 as follows.

The next step is to click the DataReport1 under Project Explorer on the right side.

Now, you must drag all the fields from the Command1 to Details (Section1) in DataReport1. Note that there are two columns created representing each field. The first field is headings, therefore, drag it to Page Header(section2) in DataReport1.

Note that you can choose any field to add or delete from the report. You have absolute control over how you want the report to appear.

In the next step, go to properties of DataReport1 in the Project Explorer.

The DataReport1 has two properties you need to set.

DataMember : Command1
DataSource: DataEnvironment

First you must select the data source and then data member.

Test The Project and Viewing Data Report

In this final section, we will test the report generation. The data project has a form where you need to create two buttons. See the image below.

Properties Of Form

Name : frmDataEnv
Caption: Product List
BackColor: &H00FFFFFF&
Width : 9000
Height: 5000

Properties of Label

Name : lblBanner
Caption: Product List Report
BackColor:  &H00FFFFFF& 
Font: Bold 18 PX MS Sans Serif

Properties of Buttons

Name : cmdExit
Caption: &Exit
BackColor:  &H0000FFFF&
Style: 1- Graphical
Font: 14 Bold Ms Sans Serif

Add following code for exit button.

Private Sub cmdExit_Click()
Unload Me
End Sub

Properties of report button.

Name : cmdReport
Caption: &Report
BackColor: &H0000FFFF&
Style: 1-Graphical
Font: 14 Bold Ms Sans Serif

Add the following code to show reports when the report button is clicked.

Private Sub cmdReport_Click()
DataReport1.Show
End Sub

When we run the program and click on the Report button, you should see a report generated from the Access database, like the one above. The Exit button will close the VB program.