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.
Three Report automation examples, are demonstrated. The first reporting example shows how our Excel consultants extract the raw data on a worksheet and populate the information into a report. The second reporting example shows how to incorporate parameters in the report automation process and place the calculated values into the report. Then a report is generated for each of the selected records. The third one is a report consolidation example.
Excel Reporting Automation Example - Parameter Driven Report (1)
Excel Reporting Automation Example - Parameter Drivn Report (2)
Excel Reporting Automation Example - Report Consolidation
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.
Data Entry Form 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
Copyright © Excel Business Solutions. All Rights Reserved.