Introduction

I love Microsoft Access. It is the best database for non-enterprise usage because it is so easy to work with: it is portable and can easily be backed up. The best part of Access is that reporting is extremely easy. You can get nice reports without having to pay extra for a package like Crystal Reports.

Until I did some investigation for this article, I was not able to take advantage of the reports from a C# application. I have been using Access' storage structure as a backend database for years using .NET's OleDb. It is very simple and makes application programming extremely easy, but I always thought that not having the ability to generate the Access reports through my programs was a bit of a drawback. Creating a report generator is just too much work for departmental applications and getting involved with Crystal Reports was just too much of a mess, so normally I generated HMTL or text reports.

This article covers how to print Microsoft Access reports from a .NET application.

Background

All thanks for this article go to Tom Archer who originally implemented this functionality in C++. I did not repeat Tom's class structure, but just illustrated the functionality using .NET through an application. The code is quite simple. This article uses the .NET Interop functionality with Microsoft Access COM. Although these technologies are used, this article does not go into detail explaining these technologies, as there are many other articles that do that. This article focuses on just printing the Microsoft Access Reports form.

Using the Code

The MainForm class is the application and is a good place to start a top-down evaluation of the code. There are just three tasks that this application performs. When the Browse... button is pressed, it loads the reports into the list box.

// we have a valid file name so we now need to
// populate the list box with available reports
listBoxReports.Items.Clear();

// create an application object.
MsAccess.Application app = new MsAccess.Application();
// open the access database file.
app.OpenCurrentDatabase(dlg.FileName, false, "");
string sql = "SELECT [Name] FROM MSysObjects WHERE Type = -32764";
dao.Database db = app.CurrentDb();
// query the database for all the reports.  all this data is
// contained in the MSysObejcts table which is invisible through
// the table listing in access.
dao.Recordset rs = db.OpenRecordset(sql, Type.Missing, Type.Missing, Type.Missing);
// go through and add all the reports to the list box.
while (!rs.EOF) {
    listBoxReports.Items.Add(rs.Fields[0].Value);
    rs.MoveNext();
}

// clean up
rs.Close();
rs = null;
db.Close();
db = null;
app.CloseCurrentDatabase();
app = null;

When the Print... button is clicked, the selected report is opened and sent to the default printer.

string report = listBoxReports.SelectedItem.ToString();

// create an application object.
MsAccess.Application app = new MsAccess.Application();
// open the access database file.
app.OpenCurrentDatabase(textBoxAccess.Text.Trim(), false, "");
app.Visible = false;
// open the report
app.DoCmd.OpenReport(report, 
    Microsoft.Office.Interop.Access.AcView.acViewPreview, Type.Missing, 
    Type.Missing, MsAccess.AcWindowMode.acWindowNormal, Type.Missing);
// print the report to the default printer.
app.DoCmd.PrintOut(MsAccess.AcPrintRange.acPrintAll, Type.Missing, 
    Type.Missing, MsAccess.AcPrintQuality.acHigh, Type.Missing, Type.Missing);
// cleanup
app.CloseCurrentDatabase();
app = null;

Finally, when the Save button is clicked, the selected report is saved as HTML in the same directory as the Access database file.

// create an application object.
MsAccess.Application app = new MsAccess.Application();
// open the access database file.
app.OpenCurrentDatabase(textBoxAccess.Text.Trim(), false, "");
app.Visible = false;
// open the report
app.DoCmd.OpenReport(report, Microsoft.Office.Interop.Access.AcView.acViewPreview, 
    Type.Missing, Type.Missing, MsAccess.AcWindowMode.acWindowNormal, Type.Missing);
// export the report to an HTML file
app.DoCmd.OutputTo(MsAccess.AcOutputObjectType.acOutputReport, 
    report, "HTML (*.html)", fileName, Type.Missing, Type.Missing, Type.Missing);
// cleanup
app.CloseCurrentDatabase();
app = null;

Adding Microsoft Access COM Components

The Microsoft Access Reports are accessed using Microsoft Access COM components. Wrappers to these COM components need to be created. Microsoft Visual Studio 2005 provides an easy method for including non-managed COM components into your managed code. Microsoft Access must be loaded on your development system for you to follow these instructions. Microsoft Access 2003 and Visual Studio 2005 are used in this example.

From the main menu select Project > Add Reference.
Go to the COM tab. Scroll down and select Microsoft Access 11.0 Object Library.
Click Select and then click OK.

When you go to Solution Explorer, you should see Access, ADODB, DAO, Microsoft.Office.Core, System.XML and VBIDE under the References folder. The next step is to add the using statement to your code. For the using statement I use the assignment format because, for library-specific code, I prefer to use the namespace part of the class name so that I know where the class is located. By using the assignment format of the using statement, I can make the code more readable and still indicate the location of the class.

using MsAccess = Microsoft.Office.Interop.Access;

This is all that is required to add the Interop functionality to your project.

Discussion

As I said in the beginning of this article, I love Microsoft Access, but I hate programming in Access and I hate Access applications. I just do not like programming inside another application. I have been using Access as a storage location for .NET applications for years. In those years, I have been missing the rich reporting functions of Access. Now, thanks to Tom Archer, I can have rich reporting features in my applications without having to resort to Crystal Reports.

History

17 December, 2007 -- Original version posted

Print Microsoft Access Reports from C#