CAccessReports - Class for Printing and Viewing MS Access Reports


This article was contributed by Tom Archer.

Environment: Source will work on VC++ 5 and higher. However, the demo application will only work on VC++ 6 and higher as it uses the CHtmlView class.

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

    • 1. 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. 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 :)

    Downloads

    Download source - 38 KB
    Download demo project - 60 KB Date Last Updated: October 22, 2000 !--#include virtual="footer.shtml" -->


    About the Author

    Tom Archer - MSFT

    I am a Program Manager and Content Strategist for the Microsoft MSDN Online team managing the Windows Vista and Visual C++ developer centers. Before being employed at Microsoft, I was awarded MVP status for the Visual C++ product. A 20+ year veteran of programming with various languages - C++, C, Assembler, RPG III/400, PL/I, etc. - I've also written many technical books (Inside C#, Extending MFC Applications with the .NET Framework, Visual C++.NET Bible, etc.) and 100+ online articles.

    Comments

    • 'Unrecognized DB format

      Posted by lcajones on 01/26/2005 05:30pm

      I am receiving an error 'Unrecognized DB format' when I attempt to open a DB mdb file. This is an Access 2003 DB. Is this supported?

      • Access 2000

        Posted by Tom Archer on 01/26/2005 05:41pm

        This article was written back in 1999 with Access 2000 databases. I believe that there are some tips for using it on newer versions of Access on the CodeProject site where this article is also hosted.

        Reply
      Reply
    • Need Help~~~

      Posted by martiana on 11/30/2004 12:11pm

      I got this error when run the demo. "Unrecognized database format "D:\DB.mdb"". Then i trid to change the code to m_pAccessReports = new CAccessReports(lpszPathName, FALSE); m_pAccessReports->RunReport("TB_pImage"); Then got this error: "The report name 'TB_pImage' you entered in either the property sheet or macro is missspelled or refers to a report that doesn't exist". i used MicroAccess 2002, and the report does exist. Anyone can help me??? Thanx

      Reply
    • Help is needed

      Posted by niceruba on 05/07/2004 04:45pm

      Hello
      I downloaded these files and added them to my project then I instantiated a CAccessReports object and used its RunReport method in a Button handler function:
      
      
      void CEmpForm::OnPrint() 
      {
      // TODO: Add your control notification handler code here
      CAccessReports accessReports(_T("D:\\Pay.mdb"), false); 
           accessReports.RunReport("Emp");
      
      }
      
       The Access application opens then disappears quickly when I click on the button. How can I keep the Access application window opened? 
      I tried with 97, 2000, 2002 formats of the Database but it didn't work  <
      
      My email is q_ruba@yahoo.com
      
      Thanks
      RUBA

      • Need Help~~~

        Posted by martiana on 11/30/2004 12:08pm

        I got this error when run the demo. "Unrecognized database format "D:\DB.mdb"". Then i trid to change the code to m_pAccessReports = new CAccessReports(lpszPathName, FALSE); m_pAccessReports->RunReport("TB_pImage"); Then got this error: "The report name 'TB_pImage' you entered in either the property sheet or macro is missspelled or refers to a report that doesn't exist". i used MicroAccess 2002, and the report does exist. Anyone can help me??? Thanx

        Reply
      • Need Help~~~

        Posted by martiana on 11/30/2004 08:36am

        I got this error when run the demo. "Unrecognized database format "D:\DB.mdb"". Then i trid to change the code to m_pAccessReports = new CAccessReports(lpszPathName, FALSE); m_pAccessReports->RunReport("TB_pImage"); Then got this error: "The report name 'TB_pImage' you entered in either the property sheet or macro is missspelled or refers to a report that doesn't exist". i used MicroAccess 2002, and the report does exist. Anyone can help me??? Thanx

        Reply
      • Call the SetVisible function

        Posted by Tom Archer on 05/07/2004 04:56pm

        In the CAccessReports constructor, insert the following lines of code after the call to OpenCurrentDatabase m_pAccess->SetVisible(TRUE);

        Reply
      Reply
    Leave a Comment
    • Your email address will not be published. All fields are required.

    Top White Papers and Webcasts

    • With JRebel, developers get to see their code changes immediately, fine-tune their code with incremental changes, debug, explore and deploy their code with ease (both locally and remotely), and ultimately spend more time coding instead of waiting for the dreaded application redeploy to finish. Every time a developer tests a code change it takes minutes to build and deploy the application. JRebel keeps the app server running at all times, so testing is instantaneous and interactive.

    • The exponential growth of data, along with virtualization, is bringing a disruptive level of complexity to your IT infrastructure. Having multiple point solutions for data protection is not the answer, as it adds to the chaos and impedes on your ability to deliver consistent SLAs. Read this white paper to learn how a more holistic view of the infrastructure can help you to unify the data protection schemas by properly evaluating your business needs in order to gain a thorough understanding of the applications …

    Most Popular Programming Stories

    More for Developers

    Latest Developer Headlines

    RSS Feeds