Create A Report In VB 6 With MS Access Database

Table of Contents

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.

Figure 1 - Product table with data — Figure 1 – Product table with 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.

Figure 2 - Choose a Data Project — Figure 2 – Choose a Data Project

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

Figure 3 - Double Click DataEnvironment1 Under Project Explorer — Figure 3 – Double Click DataEnvironment1 Under Project Explorer

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.

Figure 4 - Go to Connection1 Properties — Figure 4 – Go to Connection1 Properties

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

Figure 5 - Select Data Link Provider — Figure 5 – Select Data Link Provider

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.

Figure 6 - Select the Database Product_list.mdb and Test the Connection — Figure 6 – Select the Database Product_list.mdb and Test the Connection

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.

Figure 7 - Right Click Connection1 and Add Command — Figure 7 – Right Click Connection1 and Add Command

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.

Figure 9 - Select Table for Database Object and Products under Object Name — Figure 9 – Select Table for Database Object and Products under Object Name

The table fields will appear under Command1 as follows.

Figure 10 - Fields of Table Products Under Command1 — Figure 10 – Fields of Table Products Under Command1

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

Figure 10 - DataReport1 Window Opens up when you click Datareport1 under Project explorer — Figure 10 – DataReport1 Window Opens up when you click Datareport1 under Project explorer

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.

There are two columns for a field that you drag to Datareport1 from Command1. First one is heading and goes to Page Header section in DataReport1.

Figure 11 - Drag the fields to section 1 and then out of two columns, drag the first column to section 2 and adjust second column. — Figure 11 – Drag the fields to section 1 and then out of two columns, drag the first column to section 2 and adjust second column.

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.

Figure 12 - Go to Data Report1 Properties — Figure 12 – Go to Data Report1 Properties

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

Figure 13 - Set the Data Source and Data Member for the DataReport1 — Figure 13 – Set the Data Source and Data Member for the DataReport1

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.

Figure 14 - Main Data Form — Figure 14 – Main Data Form

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

Figure 15 - Report from the Visual Basic Data Project — Figure 15 – Report from the Visual Basic Data Project

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.