Database Programming | Reporting Automation | Data Entry

Talk to Your Databases!

A very powerful feature of Excel VBA is the ability to interact with different databases. This example demonstrates the data extraction process from a MS-Access database and presentation in a spreadsheet report.

This database programming example demostrates how our Excel consultants using Excel programming to extract data from a MS-Access database and present them in an Excel spreadsheet. The source data which stored in the tables (see Figure 1) of a Microsoft Access relational database is retrieved and populated into a report in an Excel worksheet (see Figure 2) through OLEDB (Object Linking and Embedding Database) and ADO (ActiveX Data Objects).

Fig 1. Screen shot of a table relationships chart

Fig 2. Screen shot of the report generated from the database

Automate Your Reporting Needs!

Beside the basic Excel data management and reporting tools (which are useful, but sometimes limited), Excel offers a more flexible reporting function through Excel VBA programming.

This project extracts data from an Excel worksheet (see Figure 3) and populates the information based on the criteria filter (see Figure 4) into a report (see Figure 5). The reporting process is slightly more complex due to the multiple demension of the data (region, country, service, quarter, and year). In additional, the report does not use a pre-styled template since the number of rows or column on the report is unknown before the users make their selection using the filter. Array technique is used in this project to accommandate for the multi-demension of the date and the report output variation.

Fig 3. Screen shot of raw date in a worksheet	Fig 4. Screen shot of the record filter user interface
Fig 5. Screen shot of a final report generated from the raw data

This project uses different Excel files as data sources. One of the major features of this program is that it allows users to select the company file (see Figure 6), define the computational parameters (see Figure 7), select the names of the records (see Figure 8), and build one separate report for each of the records selected (see Figure 9).

Fig 6. Screen shot of the Select Company interface	Fig 7. Screen shot of the Update Parameters interface
Fig 8. Screen shot of Print Option interface	Fig 9. Screen shot of the diferent reports generated

This example demonstrates how individual business reports can be summarized into a consolidated master report despite unique formats and styles. Through Excel report automation, all business reports can come together a smaller chance of human error.

Fig 10. Report consolidation screen shot.

Input Data Into Your Database With Ease!

MS-Access is a relational database management system. It is a wonderful tool for building data entry forms for databases with many-to-many relationship tables. This section provides such an example.

This MS-Access application allows users to input data into multiple database tables that have many-to-many relationship (see Figure 1). By using multiple subforms, users can update data in multiple tables on one master data entry form (see Figure 2).

Fig 11. Screen shot of many-to-many relationship tables in the database.

Figure 12. Screen shot of the master data entry form