Click here to Skip to main content
15,861,125 members
Articles / Desktop Programming / MFC
Article

The CAccessReports class

Rate me:
Please Sign up or sign in to vote.
5.00/5 (4 votes)
14 Mar 2000 161.6K   4.5K   60   25
A class for Printing and Viewing MS Access Reports

Sample Image - access_reports_class.gif

Author's Note

This article was written based on Access '97 (I believe) and I haven't updated it since. It will probably not work with other versions of Access but I welcome others to update the code to work with newer versions of Access.

Introduction

This class (CAccessReports) was created for those of us who have the misfortune of having to 1) use the MS Access database engine and 2) use the MS Access database engine from a Visual C++ application. The CAccessReports class uses the Access Automation objects to open a specified database, run a report (within Access), print the report and save the Access database in HTML format so that it can be viewed in a Visual C++ application. You can accomplish each of these tasks with a single line of code. Here are some examples of how to use the CAccessReports class.

Examples of how to use the CAccessReports class

When you download the source code for the CAccessReports, you will also find a full-fledged test application. However, it's always nice to be able to see what you're getting before you invest the time in downloading, unzipping and running someone else's code. Therefore, here are some code snippets that show how easy the CAccessReports class is to use.

Instantiating a CAccessReports object

How you choose to instantiate CAccessReports object depends on how you plan to use it in your application. The first argument of the CAccessReports is the fully qualified name of the database file. The second argument specifies whether you want the CAccessReports class to preload all of the report names. An example of when you would want the class to retrieve all of the report names is if your application needs to display a list of the reports to the end-user. Otherwise, if you are instantiating a CAccessReports object in order to run, print or display specific reports whose names are known at compile-time, you can pass a value of FALSE for this argument.

// Have the CAccessReports object load the report names
CAccessReports accessReports(YOUR_ACCESS_DATABASE, TRUE);

// Simply attach to the specified Access database
CAccessReports accessReports(YOUR_ACCESS_DATABASE, FALSE);

Getting a List of Reports from an Access Database

If you specified a value of TRUE for the second argument of the CAcessReport constructor, you can then retrieve the list of reports for a given Access database. Here's an example of a simple loop to retrieve and display all of the report names.

CAccessReports accessReports(YOUR_ACCESS_DATABASE, TRUE);

for (int i = 0; i < accessReports.m_reports.GetSize(); i++)
{
 AfxMessageBox(accessReports.m_reports[i]);
}

Running an Access Report

While the CAccessReports class does allow you to display an Access report from a Visual C++ application (shown below), there are still valid reasons to run the report from within Access. Therefore, the RunReport member function does exactly that.

CAccessReports accessReports(YOUR_ACCESS_DATABASE, FALSE); 
accessReports.RunReport(YOUR_REPORT_NAME);

Printing an Access Report

The PrintReport function takes as its only argument the name of a report to print. This function calls RunReport and then uses Automation to print the report.

CAccessReports accessReports(YOUR_ACCESS_DATABASE, FALSE); 
accessReports.PrintReport(YOUR_REPORT_NAME);

Displaying an Access Report (Visual C++ 6.0)

Access Automation doesn't allow for the ability to redirect the output of a report to a given window. However, it does allow for a report to be run and then saved in HTML format. That combined with the new Visual C++ 6.0 CHtmlView gives you the ability to run an Access report and display it in a Visual C++ application. In the example below, CAccessReportView is a CHtmlView derived class.

void CAccessReportView::OnInitialUpdate()
{
 CHtmlView::OnInitialUpdate();

 CAccessReports accessReports(YOUR_ACCESS_DATABASE, FALSE); 
 accessReports.SaveAsHtml(YOUR_REPORT_NAME, DESTINATION_FOLDER);
 Navigate2(FULLY_QUALIFIED_FILE_NAME, NULL, NULL);
}

Displaying an Access Report (Visual C++ 5.0)

Unfortunately, Visual C++ 5.0 does not have the CHtmlView class. However, you can still display Access reports using the WebBrowser control (in the Visual C++ Component Gallery). In order to display the Access report in a Visual C++ 5 application, simply instantiate a CAccessReports object, call its SaveAsHtml member function and then call the WebBrowser control's Navigate function specifying the name of the HTML file.

Notes

Access Automation can not be used to get the entire list of reports for a an Access database. Therefore, if you specify TRUE to the second argument of the CAccessReports constructor, the class attempts to use the MFC DAO classes to access the msysobjects table of the specified database. In order to accomplish this, you must give the Admin user "read" priveleges to the database's msysobjects table (see below). If your application is printing or viewing a specific report from a specific database and you don't need the list of reports, then this limitation does not apply. If anyone knows of a better way of doing this, I'm always open to learning new things :)

Granting access to the MS Access MSysObjects table

  1. Open Microsoft Access
  2. From the Tools menu, select the Options menu option
  3. On the View tab, click the System Objects checkbox
  4. Click OK to save your changes
  5. From the Tools menu, select the Security -> User and Group Permissions menu option
  6. Click the Permissions tab
  7. Select the Table entry in the Object Type combo box
  8. Select the Admin userid in the User/Group Name listbox
  9. In the Object Name listbox, select the MSysObjects entry
  10. In the Permissions group box, check the Read Data check box

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Software Developer (Senior) Microsoft
United States United States
I manage the strategy for the Azure Management Experience documentation at Microsoft. Those technologies include Open-Source DevOps (Terraform, Ansible, Jenkins), Azure PowerShell, and Azure CLI.

Comments and Discussions

 
Generalunrecognized format Pin
deposit11-Feb-07 18:31
deposit11-Feb-07 18:31 
GeneralRe: unrecognized format Pin
Tom Archer11-Feb-07 18:51
Tom Archer11-Feb-07 18:51 
GeneralRe: unrecognized format Pin
Chris Maunder12-Feb-07 14:19
cofounderChris Maunder12-Feb-07 14:19 
GeneralRe: unrecognized format Pin
Tom Archer12-Feb-07 14:36
Tom Archer12-Feb-07 14:36 
QuestionError - pls help Pin
Lavinia Mary Gonsalvez24-Oct-05 23:05
Lavinia Mary Gonsalvez24-Oct-05 23:05 
GeneralAccessAutomation Projet Pin
Hervy15-Sep-05 23:31
Hervy15-Sep-05 23:31 
GeneralList of Reports without MSysObjects Pin
FriendOfAsherah20-Sep-04 22:51
FriendOfAsherah20-Sep-04 22:51 
GeneralADO Pin
cybz5531-Mar-04 9:28
cybz5531-Mar-04 9:28 
QuestionHow to do this using ADO instead of DAO ? Pin
Anonymous31-Mar-04 9:13
Anonymous31-Mar-04 9:13 
Generala few more errors Pin
Member 4781141-Oct-03 18:33
Member 4781141-Oct-03 18:33 
GeneralRe: a few more errors Pin
Tom Archer2-Oct-03 4:23
Tom Archer2-Oct-03 4:23 
Generalundeclared identifier error Pin
Member 4781141-Oct-03 7:04
Member 4781141-Oct-03 7:04 
GeneralRe: undeclared identifier error Pin
Tom Archer1-Oct-03 11:34
Tom Archer1-Oct-03 11:34 
Questionway to pass /user and /pwd via automation? Pin
Cyndi25-Mar-03 9:11
Cyndi25-Mar-03 9:11 
AnswerRe: way to pass /user and /pwd via automation? Pin
Tom Archer25-Mar-03 9:15
Tom Archer25-Mar-03 9:15 
GeneralRe: way to pass /user and /pwd via automation? Pin
Cyndi25-Mar-03 9:23
Cyndi25-Mar-03 9:23 
GeneralRe: way to pass /user and /pwd via automation? Pin
Tom Archer25-Mar-03 9:42
Tom Archer25-Mar-03 9:42 
Questionhow to send SQL to access report by MFC Pin
9-Jun-02 7:26
suss9-Jun-02 7:26 
GeneralQuery for primary keys in access 2000 Pin
28-Mar-02 7:16
suss28-Mar-02 7:16 
GeneralUsing reports with access queries Pin
Todd Hallquist10-May-01 6:30
Todd Hallquist10-May-01 6:30 
GeneralRe: Using reports with access queries Pin
Tom Archer25-Mar-03 9:19
Tom Archer25-Mar-03 9:19 
GeneralGetting it to work under access 2000 Pin
Ashraf Moosa2-May-01 3:44
Ashraf Moosa2-May-01 3:44 
GeneralRe: Getting it to work under access 2000 Pin
Sketch10-Aug-02 15:16
Sketch10-Aug-02 15:16 
GeneralRe: Getting it to work under access 2000 Pin
Kapoor Vikrant23-Nov-03 6:18
Kapoor Vikrant23-Nov-03 6:18 
GeneralODBC REPORTS Pin
3-Apr-01 3:47
suss3-Apr-01 3:47 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.