Printing Microsoft Access 2010 Reports from Visual Studio

Introduction

The world of programming is a strange realm of uncertainties. Sometimes a need arises to do a task because "sloppy programmers" didn't consider all the facts, or "sloppy planning". I put these terms into quotation marks because these reasons remain open for discussion. Yes, there are times that these reasons are the main reason for problems in your code and programs, but not always. Sometimes it depends on the situation at hand and the resources at hand. As a programmer, you should be willing to take these challenges head-on and face them.

The reason I mention these facts is because today's program is one of those topics that usually gets frowned upon - which is a tad unfair. I will demonstrate with my article today, how to print a Microsoft Access 2010 report from within your C# or VB.NET program. This topic is usually the start of a discussion on decent reporting tools such as Crystal Reports etc. Face it, not every company will use Crystal Reports or another product, simply because of funding or hardware concerns. Enough rambling, Let's get started with our project...

Design

Our design is simple, as illustrated in Figure 1

Our design
Figure 1 - Our design

References

Add a project Reference to Microsoft Access 14 Object Library. By clicking on Project, Add reference, select the COM tab, and scroll down to Microsoft Access Object library. Just a note, you could ( if you do not have Microsoft Office 2010 yet ) make use of the Microsoft Access 12 Object library for Office 2007. There should be no differences. With this project, I tested with both Microsoft Access 2007 and Microsoft Access 2010

Code

Apart from adding the Project Reference to Microsoft Access, we need to be able to reference the Access code library through code as well, add the following namespace:

VB.NET

Imports Microsoft.Office.Interop.Access 'Import MS Access funtionalities, ADD PROJECT REFERENCE AS WELL

C#

using objAccApp = Microsoft.Office.Interop.Access; //Import MS Access funtionalities, ADD PROJECT REFERENCE AS WELL

Already the succinct differences between VB.NET and C# is clearly visible. VB.NET follows a three step process here:

  1. VB.NET imports the Access library
  2. It creates an object from that library (which we will do next)
  3. Lastly, it instantiates that object so that we could use it

C# on the other doesn't work like that. With C# we directly make an object of the Access library, and then instantiate other objects from it.

Browse Button

Add the following code for your browse button:

VB.NET

    Private Sub btnARBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnARBrowse.Click

        Dim objAccApp As New Microsoft.Office.Interop.Access.Application() 'Instantiate Access Object

        objAccApp.Visible = False 'Do Not Display Access Window

        Dim strFileName As String = txtARSource.Text.Trim() 'File Name To Open

        Dim ofdAccReport As New OpenFileDialog() 'Create New Open File Dialog

        ofdAccReport.Filter = "Microsoft Access (*.accdb)|*.accdb)" 'Set File Filter For OFD

        If strFileName.Length > 0 Then 'If File Selected

            ofdAccReport.FileName = strFileName

        End If

        If ofdAccReport.ShowDialog() = DialogResult.Cancel Then 'If Cancelled

            Return

        End If

        txtARSource.Text = ofdAccReport.FileName 'Display File Name 

        lstARReports.Items.Clear() 'Erase Previous ListBox Items

        objAccApp.OpenCurrentDatabase(ofdAccReport.FileName, False, "") 'Open Selected Access Database

        If Not objAccApp.Visible = False Then 'Do Not Display access Window(s)

            objAccApp.Visible = False

        End If

        Dim strReportSQL As String = "SELECT [Name] FROM MSysObjects WHERE Type = -32764" 'Search All Access Reports

        Dim daoDB As dao.Database = objAccApp.CurrentDb() 'Open The Access Database

        Dim rsReports As dao.Recordset = daoDB.OpenRecordset(strReportSQL, Type.Missing, Type.Missing, Type.Missing) 'Find All Reports

        While Not rsReports.EOF

            lstARReports.Items.Add(rsReports.Fields(0).Value) 'Add Each Report Name To The ListBox

            rsReports.MoveNext() 'Continue

        End While

        rsReports.Close() 'Close All Objects

        daoDB.Close()

        objAccApp.CloseCurrentDatabase()

        rsReports = Nothing 'Release All Resources
        daoDB = Nothing
        objAccApp = Nothing

    End Sub

C#

        private void btnARBrowse_Click(object sender, EventArgs e)
        {
            string strFileName = txtARSource.Text.Trim(); //File Name To Open

            OpenFileDialog ofdAccReport = new OpenFileDialog(); //Create New Open File Dialog

            ofdAccReport.Filter = "Microsoft Access (*.accdb)|*.accdb)"; //Set File Filter For OFD

            if (strFileName.Length > 0) //If File Selected
            {
                
                ofdAccReport.FileName = strFileName;

            }

            if (ofdAccReport.ShowDialog() == DialogResult.Cancel) //If Cancelled
            {
                
                return;

            }

            txtARSource.Text = ofdAccReport.FileName; //Display File Name

            lstARReports.Items.Clear(); //Erase Previous ListBox Items

            objAccApp.Application app = new objAccApp.Application(); //Instantiate Access Object

            app.Visible = false; //Do Not Display Access Window

            app.OpenCurrentDatabase(ofdAccReport.FileName, false, ""); //Open Selected Access Database

            string strReportSQL = "SELECT [Name] FROM MSysObjects WHERE Type = -32764"; //Search All Access Reports

            dao.Database daoDB = app.CurrentDb(); //Open The Access Database

            dao.Recordset rsReports = daoDB.OpenRecordset(strReportSQL, Type.Missing, Type.Missing, Type.Missing); //Find All Reports

            while (!rsReports.EOF)
            {

                lstARReports.Items.Add(rsReports.Fields[0].Value); //Add Each Report Name To The ListBox

                rsReports.MoveNext(); //Continue

            }

            rsReports.Close(); //Close All Objects
            daoDB.Close();
            app.CloseCurrentDatabase();

            rsReports = null; //Release All Resources
            daoDB = null;
            app = null;

        }

In this procedure we create a new OpenFileDialog and allow for file selection. Once a file has been selected, we place the name into our Source textbox. Now it gets interesting. To open the physical Microsoft Access file we do not make use of ADO.NET here, as we are making use of the Microsoft Access object Library's functionalities. We make use of the OpenCurrentDatabase method (inside the Access object library) to open the selected database.

To identify the report objects present in the Access database, we use an SQL statement searching through MSysObjects for type -32764 (which is the report objects). Inside a loop that loops through all the objects present in the database, we place each report name into a listbox.

Lastly, we just close all the Access objects and release all the memory resources.

If you were to run your project now, you'd be able to get a list of all reports inside your selected database.

Print Button

Add the following code behind your Print button:

VB.NET

    Private Sub btnARPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnARPrint.Click

        Dim objAccApp As New Microsoft.Office.Interop.Access.Application() 'Instantiate Access Application Object

        Dim strAccReport As String = lstARReports.SelectedItem.ToString() 'Get Selected ListBox Item

        objAccApp.OpenCurrentDatabase(txtARSource.Text.Trim(), False, "") 'Open Database

        If Not objAccApp.Visible = False Then 'Do Not Show Access Window(s)

            objAccApp.Visible = False

        End If

        objAccApp.Visible = False

        objAccApp.DoCmd.OpenReport(strAccReport, Microsoft.Office.Interop.Access.AcView.acViewPreview, Type.Missing, Type.Missing, AcWindowMode.acWindowNormal, Type.Missing) 'Open Selected Report

        objAccApp.DoCmd.PrintOut(AcPrintRange.acPrintAll, Type.Missing, Type.Missing, AcPrintQuality.acHigh, Type.Missing, Type.Missing) 'Print Report

        objAccApp.CloseCurrentDatabase() 'Close Database

        objAccApp = Nothing 'Release Resources

    End Sub

C#

        private void btnARPrint_Click(object sender, EventArgs e)
        {

            string strAccReport = lstARReports.SelectedItem.ToString(); //Get Selected ListBox Item

            objAccApp.Application app = new objAccApp.Application(); //Instantiate Access Application Object

            app.OpenCurrentDatabase(txtARSource.Text.Trim(), false, ""); //Open Database

            app.Visible = false; //Do Not Show Access Window(s)

            app.DoCmd.OpenReport(strAccReport, Microsoft.Office.Interop.Access.AcView.acViewPreview, Type.Missing, Type.Missing, objAccApp.AcWindowMode.acWindowNormal, Type.Missing); //Open Selected Report

            app.DoCmd.PrintOut(objAccApp.AcPrintRange.acPrintAll, Type.Missing, Type.Missing, objAccApp.AcPrintQuality.acHigh, Type.Missing, Type.Missing); //Print Report

            app.CloseCurrentDatabase(); //Close Database

            app = null; //Release Resources

         }

We obtain the selected (in the list) report name and store it inside a variable named strAccReport. We again make use of the OpenCurrentDatabase method to open the selected database. Now, we make use of the DoCmd method to Open the selected report, and to Print the the report out on paper.

Not too complicated, hey? No.

If you were to run your application now, you'd be able to print out any selected report from your Access database. I am including both sample projects below, just in case you put a foot wrong somewhere.

Conclusion

There, I hope you enjoyed this article and learned from it. This just shows how much is actually possible in the world of programming. Until next time, cheers!



Related Articles

Downloads

Comments

  • Student

    Posted by Nancy on 01/24/2014 02:27pm

    Both in the downloaded version for C# and in my code, there is an error on: dao.Database daoDB = app.CurrentDB(); and dao.Recordset rsReports = daoDB.OpenRecordset(startReportSQL, Type.Missing, Type.Missing, Type.Missing): Error: The type or namespace name 'dao' could not be found (are you missing a using directive or an assembly reference?) Any help would be appreciated, thanks!

    Reply
  • Brief post illustrates the unignorable details of chanel and ways it could actually threaten your site.

    Posted by emeseesip on 05/07/2013 03:24am

    The Biggest And Most Detailed nike E-book You Ever Witnessed Or Your Cash Back [url=http://www.guccija.biz/]グッチ 財布[/url] Ok, astounding service. Your company got to go look at nike now while it's still available ! [url=http://www.guccija.biz/]グッチ 長財布[/url] nike assists every one of us by including several unique features and options. This is a unvaluable item for any enthusiast of gucci. [url=http://www.guccija.biz/]グッチ ショルダーバッグ[/url] Unbiased write-up divulges Unique fresh, new things surrounding nike that noone is covering. [url=http://www.chanelja.biz/]シャネル 財布[/url] Reason why not a soul is referfing to adidas and for that cause the thing one should engage in right now. [url=http://www.chanelja.biz/]chanel バッグ[/url] New queries about adidas resolved and in addition the reasons why you has got to review each and every phrase of this specific write up. [url=http://www.chanelja.biz/]chanel 財布[/url] The fundamental principles behind adidas for you to make full use of starting today.[url=http://www.nikeja.biz/]nike[/url] A way to understand every part there is to know around nike in Some basic steps.

    Reply
  • Chemise Burberry

    Posted by Wrinalertiece on 04/30/2013 02:35am

    burberry femme pas cher Chemise Burberry Pas Cher sacoche burberry pas cher Chemise Burberry Femme [url=http://chemiseburberry6.webnode.fr/]chemiseburberry6.webnode.fr[/url] , sac burberry pas cher [url=http://burberryhomme2.webnode.fr/]burberryhomme2.webnode.fr[/url] , Burberry Pas Cher

    Reply
  • Odd summary gives the simple truth on mizuno which experts state not very many customers know.

    Posted by icoppyapedcap on 04/25/2013 02:23pm

    NuwFlfFymTtf [url=http://www.nikeyasuijp.com/]ナイキスニーカー[/url]SbkDpsYvnFtr [url=http://www.nikeyasuijp.com/nike-air-force1エアフォース1-c-14.html]ナイキ エアフォース[/url]XcxWyqYdcCbu [url=http://www.nikeyasuijp.com/nike-air-maxエアマックス-c-12.html]nike air max[/url]YxdNbmUvhRzx [url=http://www.nikeyasuijp.com/nike-air-jordanエア-ジョーダン-c-13.html]ナイキランニング[/url]LtoZstPwoMna

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

Top White Papers and Webcasts

  • The explosion in mobile devices and applications has generated a great deal of interest in APIs. Today's businesses are under increased pressure to make it easy to build apps, supply tools to help developers work more quickly, and deploy operational analytics so they can track users, developers, application performance, and more. Apigee Edge provides comprehensive API delivery tools and both operational and business-level analytics in an integrated platform. It is available as on-premise software or through …

  • Not long ago, security was viewed as one of the biggest obstacles to widespread adoption of cloud-based deployments for enterprise software solutions. However, the combination of advancing technology and an increasing variety of threats that companies must guard against is rapidly turning the tide. Cloud vendors typically offer a much higher level of data center and virtual system security than most organizations can or will build out on their own. Read this white paper to learn the five ways that cloud …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds