So in the interests of "setting the record straight", hoping I can spare someone else this pain, and providing a spot to base my upcoming conversation with Microsoft on, I will now lay out what worked for me and what didn't. I'll restate this caveat later as well: this works for me on Windows Server 2003 SP1 with Microsoft Access 2003. Your mileage may vary - just as mine did.
The Business Problem
An Access application needs to print some documents to a shared network printer (shared with other applications and users). This shared printer is not the logged on user's default printer, and the default tray of the printer does not contain the paper I need for this print job - A special tray on non-default printer contains the right paper.
The Technical Problem
#1 Access is Printer Ignorant
First, Access has very few ways to interact with the printer programatically. In order to do things like change the page orientation or tray, you have to resort to using the DEVMODE structure - a dusty and decrepit object from the Win32 API that (somehow) has still managed to hang on to life.
There are quite a few Microsoft Support KBs, blog entries, articles, and forum topics that suggest code for interacting with the DEVMODE, or other ways to accomplish the feats of changing paper orientation and tray. Unfortunately for me, only the DEVMODE route could change "media type" (AKA "paper type", such as "transparency" or "bond") - as the KB articles that had suggestions for orientation and tray used functions that didn't support media type. (It appears that media type didn't arrive on the scene until NT 4.0 - likely due to the advent of laser printers that supported different fuser intensities for different materials.)
#2 Access is Inconsistently Printer Ignorant
Despite not having any built-in functions to programatically check and set printer settings, Access supports saving printer settings to the Report object you're previewing through user interaction with the Page Setup dialog. You preview the report, change the printer and/or printer settings, and when you save the report, Access saves the printer and accompanying settings (including media type!) with the report. The next time the report is printed, it uses those settings.
Again, (IMO) Access is showing it's non-Enterprise suitability here. If you're building a single-user, single-purpose application, that functionality alone is fantastic. It's an easy way to configure your report, save the settings, and never have to worry about it again.
However, in an "Enterprise" setting, MS Access can't cut it. (And oh, how it hurts to hear "Enterprise" and "MS Access" in the same sentence.) Aside from the problems all Office apps have with traditional server environments, this business case presents another "flaw". (But I can't blame the Access devs for this - they don't (I hope) intend for Access to be used in large environments.) In my case, to support the business process, I need to set up the same report for different locations/users/printers. Through other wizardry (ahem - login) I know what printer I should use, and I happen to have a table in the database with the desired tray, paper type, and other printer settings I want to use. So I basically have to "override" the saved report settings with stored settings. However, there is no Access interface to manipulate those saved settings. Access saves the printer settings as a byte-array cast of the DEVMODE structure.
#3 Microsoft is Very Confused About DEVMODE
I've programmed the Win32 API before with VBA/VB6 - I wasn't afraid! Setting up DECLAREs for API functions and defining TYPEs to mirror API C-language struct definitions wasn't foreign to me. How hard could it be?
To be honest - a f***ing nightmare if the documentation is wrong. Get this - in several hours of searching (on and off as things looked like they'd work for me, then didn't) I came across SEVEN different definitions of DEVMODE from Microsoft. And no, I'm not saying they're different due to Windows version. This structure is (and many other APIs are) extended as OS versions get released - new items get tacked on the end. Here's a list, I'm sure you can find more:
- On MSDN, the Access 2003 VBA Language Reference for PrtDevMode
- On MSDN, the Win32 API for DEVMODE
- On MS Support, KB 282474: How To Print Using Custom Page Sizes on Windows NT and Windows 2000
- On MS Support, KB 190218: How To Retrieve Settings From a Printer Driver
- On MSDN, the VC++ 6 DEVMODE
- On MSDN, the Windows Mobile 6 DEVMODE
- On MSDN, the Windows CE 5.0 DEVMODE
First, confusion sets in when I read article #1 which says PrtDevMode "is a 94-byte structure that mirrors the DEVMODE structure defined in the Win32 Software Development Kit," yet if I try to add up the byte-counts of the structure elements (ints = 2 bytes, longs = 4) I easily arrive at 108 bytes.
I say "that can't be right, let's see what the Win32 API says" so off I go to read article #2. Despite the definitions for BCHAR, DWORD, POINTL, etc... being unlinked (I eventually track them down) I find that this structure totals 126 bytes. (Don't let the "union" fool you - it's a C language element that is an "either-or.") And the "FormName" element here says 32 bytes, but article #1 says 16 characters? Oh, it must be using Unicode...
Ahhh, right. So I say, "Let's go find a tie-breaker - maybe something with a concrete example to work with," and find article #3. Wait... "FormName" is now shown as 32 characters. And the element right after that is an Integer, not a Long.
I can feel the quicksand creeping up over my chin.
For most people (I have to assume) these details don't matter, as they're typically interested in the earlier elements of the DEVMODE structure like the Orientation - so having the structure misaligned or even completely hosed later on is of no consequence. In my case, however, I have to manage the Media Type - which isn't shown on most of those articles (it's the dmMediaType element on article #2 - it's a later addition to the Win32 API). This means I need to be EXTREMELY sure that everything is lined up right, or I'll crash Access intermittently if I'm unlucky, or regularly if I'm lucky. (I wasn't lucky. I thought I'd found the right struct, but it was wrong, and caused Access to GPF from time to time while printing - just not while it went through QA.)
#4 Access and VBA Suck at Structs
I'm getting pretty blunt now, aren't I? Sorry. Well, not really.
In conjunction with the problem of actually finding the correct structure for DEVMODE, I had to get that structure pasted into the variant/string/byte-array PrtDevMode property of the Access Report. "Converting" structures and byte-arrays into strings isn't a straightforward operation in VBA. Let's face it - if you have to do that kind of thing, you shouldn't be doing it in VBA. Ouch. Point taken. You should know I'm doing this under duress...
Microsoft knows this fact, that's why they stored the DEVMODE structure inside the Report object as a string. That, and they probably didn't want you to change it either.
The Technical Solution
Solving those technical problem(s) to arrive at a business solution requires two separate (yet related) parts. The first is to apply a known set of "adjustments" (specify a printer, a page orientation, tray, paper size, media type) to an existing report such that it prints to the desired printer using the right tray and paper type. The second part is to make the configuration of those printer settings relatively painless by allowing an "administator" to easily extract the printer settings from a test report that's been configured by using the standard Page Setup dialog.
Part 1: Applying a Printer Configuration
The code sample on my SkyDrive has a function called SetReportPrinterProperties that applies specific printer settings to a given report - open that up to illustrate the discussion that follows.
The basic idea of this function is that we're going to retrieve a "known good" version of the DEVMODE contents directly from the printer driver, then make changes to its configuration, and save those changes to the report. This process does not:
- Change the user's default printer.
- Change the settings for the user's default printer.
- Change the "default" settings for the desired printer.
- Sidestepping the partially implemented MS Access printer setting control with one that uses the "complete" Win32 API DEVMODE structure. This allows us to alter settings like Media Type.
- Providing an interface to apply ANY printer settings to ANY report at ANY time without altering the default settings for any printer, or changing the user's default printer.
- Finding a correct DEVMODE structure (at least for Windows Server 2003!) by trial and error. The winner? Link #5... plus "extra items" from link #2.
- To get a byte array (from a Win32 API call to DocumentProperties) stuffed into a VBA DEVMODE structure: a CopyMemory Win32 API call. To get a VBA DEVMODE structure stuffed into the Report's PrtDevMode variant/string/byte-array: An LSET to a "string struct" followed by an extraction of the string.
This is where the GetReportPrinterProperties function comes in from the sample. Give a regular user any Access report you like, and have them set it up using the standard Page Setup dialog until it's "just right". Once that's done, you can use this function to extract their settings from the stored DEVMODE structure in the Report object. This extraction function is a lot less complex, because we're only reading info, not reading, changing, casting, and writing.
Conclusion
Do I have to say it? OK. Don't use MS Access for anything except prototyping or simple, single-user, single-purpose apps. Right now, I'm crossing my fingers that this gets through an extended QA process - since my last flawed attempt snuck through the regular QA.
If you have to extend the DEVMODE usage to some other properties that have been added to later versions of Windows, I'm hoping all you have to do is add those elements to the end of the "udtDevMode" type I've got in the code sample.
If anyone has any other pointers about how to handle this better, or knows of anything that will come to bite me later - please say so! I have my fingers crossed that this is the last I'll be dealing with this, so I can get more quality time with my Guerilla BI project...
Posts
