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
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:
- VB.NET imports the Access library
- It creates an object from that library (which we will do next)
- 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.
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.
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!
