Hey, Scripting Guy! How can I print a Microsoft Access report?

— RW

Hey, RW. You know the cool thing about being a Scripting Guy – aside from the opportunity to pal around with Paris and Nicole – is the fact that people think we know what we’re doing. Someone asks a question like, “How can I print a Microsoft Access report?” and the Scripting Guys respond with a script that does just that. Wow, you find yourself thinking. Is there anything those guys don’t know?

Just between us, RW, there are tons of things the Scripting Guys don’t know, things like, well, how you can print a Microsoft Access report. To be honest, we have no idea how to print a report from Microsoft Access; that means a question like this one results in a flurry of activity, with a gaggle of Scripting Guys checking SDKs, writing sample code, and running and debugging scripts. But no one ever sees that part of the process; instead, everyone sees the finished product and assumes that we knew the answer right off the top of our heads. That’s rarely the case, but we’re careful not to let anyone know that: after all, we’ve acquired a reputation as guys who know what they’re doing, and you’d have to be a really dumb Scripting Guy to spill the beans and let people know the sordid truth.

So how do you print a Microsoft Access report? That’s an easy one, RW, at least for highly-competent, world-class experts like the Scripting Guys. Here’s how:

Set objAccess = CreateObject(“Access.Application”)
objAccess.OpenCurrentDatabase “C:\Scripts\Test.mdb”

Set objCommand = objAccess.DoCmd
objCommand.OpenReport “EmployeeList”
objAccess.CloseCurrentDatabase

As you can see, we didn’t exactly have to knock ourselves out in order to print a report from Microsoft Access. We start out by creating an instance of the Access.Application object, then use the OpenCurrentDatabase method to open the database C:\Scripts\Test.mdb.

objAccess.Visible = True

That brings us to this portion of the script:

Set objCommand = objAccess.DoCmd

The DoCmd object is an unusual – albeit useful – little object. More often than not scripts that interact with Microsoft Access don’t use any Access-specific commands; instead, the scripts simply use Access as a data source and rely on generic database commands (like those found in ActiveX Data Objects) to perform their chores. On occasion, however (and this is one of those occasions), your script will need to do something Access-specific, like print a report or open a form. At times like that you need to use the DoCmd object to invoke those Access-only commands.

Which, needless to say, explains why we created that instance of the DoCmd object.

Once we have our very own DoCmd object we can then print the report (to the default printer) by calling the OpenReport method and passing the name of the report we want to print (in this case, that’s a report named EmployeeList):

objCommand.OpenReport “EmployeeList”

And from there all we have to do is close the database and we’re done:

objAccess.CloseCurrentDatabase

And yes, with this script you need to explicitly close the database. If you don’t, your invisible instance of Access will continue to run in the background forever and ever; we’re guessing you don’t really need to have invisible copies of Access running in the background using up system resources. But that’s up to you.

Incidentally, you can use a related script to print the data in an Access table; that provides a way to print information without having to create an Access report. For example, this script prints all the data in the Employees table:

Set objAccess = CreateObject(“Access.Application”)
objAccess.OpenCurrentDatabase “C:\Scripts\Test.mdb”

Set objCommand = objAccess.DoCmd
objCommand.OpenTable “Employees”
objCommand.PrintOut
objAccess.CloseCurrentDatabase

You can see the similarities between this script and our report-printing script. In both scripts we start out by creating an instance of Access, opening the database C:\Scripts\Test.mdb, then creating an instance of the DoCmd object. In the table-printing script we then use these three lines of code to open the table, print the data (using the Printout method), and then close the database:

objCommand.OpenTable “Employees”
objCommand.PrintOut

objAccess.CloseCurrentDatabase

All in all, pretty darn easy.

We should also note that there are additional parameters you can employ when calling the PrintOut method; for example, you can specify a range of pages to print, you can specify a print quality (draft, low, medium, high), and even indicate the number of copies to print and whether or not those copies should be collated. For more information on these additional parameters take a look at the Microsoft Access VBA Language Reference.

Not that the Scripting Guys ever have to refer to outside resources like the VBA Language Reference; after all, we wouldn’t be Scripting Guys if we didn’t already know all that stuff. We just thought that other people might find it helpful.

Top of page