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...
Hi Tom, I was looking information about why my network printers keep changing configuration and i get into your blog.
ReplyDeleteI was reading your article and i found that i have similar problems. I created a module for printing using .NET Printersetting class to send reports directly to printer. I connect directly to printer and send the report but i don't know why the user get the report in different size from time to time.
Maybe forcing printing configuration is the answer, what do you think?
That's really hard to say without knowing a lot more about your situation. Everyone's had problems with printers - in my experience, it's sometimes been Windows' interaction with (bad) printer drivers, poor application software, lack of a decent API for communicating properly with the printer, ...
ReplyDeleteI recommend that you visit the MSDN forums for the .Net Framework at http://social.msdn.microsoft.com/Forums/en-US/clr/threads
Post up your problem, including a description of what you're trying to do, what's not working - and please include a code snippet of how you're currently using the PrinterSettings class.
Good luck!
Once I had issues with access files and a friend advised-Repair Access,to my surprise software helped very quickly.And executed it for free,moreover it demonstrated how retrieved from the source file, the software restores the structure of user’s tables (indexes, field types and names).
ReplyDeleteHi Todd,
ReplyDeleteI came across your post when I was looking into the WinAPI Devmode for messing with my Access printers. I gotta say that in some respects you are right, but in a lot of ways, you have made this harder on yourself than it probably needed to be.
To start, when Access opens it picks up the computer's default printer. But it becomes a separate object that can be manipulated at will throughout the lifetime of the Access application. Changes you make to it are NOT sent back to Windows or other applications and are NOT saved when you close Access. You can access it in VBA as
Application.Printer
You can change it like so (The following Line changes ACCESS's default printer for the duration of the time Access remains open to the printer named 'HP 2025 Cait'):
Set Application.Printer = Application.Printers("HP 2025 Cait")
A printer object has a mess of properties that can be read out, changed and set. These include:
DeviceName
DriverName
Port
ColorMode
Copies
Duplex
Orientation
PaperBin
PaperSize
PrintQuality
Now, the first three spit out data about a given printer, while the next seven allow you to muck with some very straight-forward printing settings. To make a report that is saved to come out on the default printer, in color, in simplex, in portrait, on letter paper come out on a printer named 'My Big Color Laser', in black and white, duplex, landscape on 11x17 from is fairly simple:
Set Application.Printer = Application.Printers("My Big Color Laser")
With application.Printer
.ColorMode = acPRCMMonochrome
.Orientation = acPRORLandscape
.Duplex = acPRDPHorizontal
.PaperSize = acPRPS11x17
end with
DoCmd.OpenReport "MyReportName", acNormal
The rub starts to come in when you want to know the capabilities of a given printer. Access will let you SET those--but it won't tell you if those settings are applicable to the printer you are messing with. That's why I am here, looking into Devmode. I need the windows API to report them back to me. Fortunately, that too is fairly straight-forward. For me, I need to know if the present printer can handle duplex, because some of my reports HAVE to be duplexed.
So, in a module I have:
'------------------------------------------------
Public Const DC_DUPLEX = 7
Declare Function DeviceCapabilities Lib "winspool.drv" Alias "DeviceCapabilitiesA" (ByVal lpDeviceName As String, ByVal lpPort As String, ByVal iIndex As Long, ByVal lpOutput As String, lpDevMode As Any) As Long
'-------------------------------------------------
And in my report parameters form module, I have:
'------------------------------
with Application.printer
if DeviceCapabilities(.DeviceName, .Port, DC_DUPLEX, ByVal Var, 0) = 0 then
msgbox .DeviceName & " isn't duplex, and cannot print this report!"
end if
End with
'--------------------------------
It would be child's play to populate a listbox with all the .DeviceName where
DeviceCapabilities(.DeviceName, .Port, DC_DUPLEX, ByVal Var, 0) = 1
and change Application.Printer to match the user input and spit out the report.
I also have some API code originally written by Dev Ashish that can snag logged on usernames, and computernames and mess with things based on user and computer.
***Note that none of this makes any changes whatsoever to the Windows printers, just to what Access is presently working with***
Nick67
Continued...
ReplyDeleteWhat you need is a little more complex -- but not much.
You need to retrieve and select the correct tray. And you've said that you need to muck with the paper TYPE. That last requirement may be a bridge too far for VBA.
The bins are simple enough http://support.microsoft.com/kb/194789 shows that the DeviceCapabilitesA parameters for bins and binnames would be:
'------------------------------
Public Const DC_BINS = 6
Public Const DC_BINNAMES = 12
'-------------------------------
A little VBA/API can get those out:
'-------------------------------------------------------
Private Sub BinLoop()
On Error Resume Next
Dim prn As Printer
Dim dwbins As Long
Dim ct As Long
Dim nameslist As String
Dim nextString As String
Dim numBin() As Integer
Dim mymessage As String
For Each prn In Printers
mymessage = ""
dwbins = DeviceCapabilities(prn.DeviceName, prn.Port, DC_BINS, ByVal vbNullString, 0)
ReDim numBin(1 To dwbins)
nameslist = String(24 * dwbins, 0)
dwbins = DeviceCapabilities(prn.DeviceName, prn.Port, DC_BINS, numBin(1), 0)
dwbins = DeviceCapabilities(prn.DeviceName, prn.Port, DC_BINNAMES, ByVal nameslist, 0)
If mymessage <> "" Then
mymessage = mymessage & vbCrLf & vbCrLf
End If
mymessage = mymessage & prn.DeviceName
For ct = 1 To dwbins
nextString = Mid(nameslist, 24 * (ct - 1) + 1, 24)
nextString = Left(nextString, InStr(1, nextString, Chr(0)) - 1)
nextString = String(6 - Len(CStr(numBin(ct))), " ") & ct & " " & nextString
mymessage = mymessage & vbCrLf & nextString
Next ct
MsgBox mymessage
Next prn
End Sub
'------------------------------------
Where it gets complex again is knowing what tray will correspond to which acPRBN.... constant. And you're right that is not at all well documented. And sometimes you need to get more granular thatn just the default Application.Printer
'--------------------------------------
Set Application.Printer = Application.Printers("My Big Color Laser")
DoCmd.OpenReport "rptFieldReportnew", acViewPreview, , , acHidden
With Application.Reports("rptFieldReportnew").Printer
.ColorMode = acPRCMMonochrome
.Orientation = acPRORLandscape
.Duplex = acPRDPHorizontal
.PaperSize = acPRPS11x17
.PaperBin = acPRBNLower
End With
DoCmd.OpenReport "rptFieldReportnew", acNormal
'---------------------------------------
This code will knock out the report to Big Color Laser in monochrome, landscape 11x17 duplex pulling paper from Tray 2. Note that you open the report in Preview, and hidden, and then screw with the printer settings, and then knock it out with those settings.
But telling Access you want to print on a transparency...yeah, that might very well be a bugger
Thanks for the comments, Nick! If I ever have to deal with that PoS Access application again, I will definitely use this. Here's hoping I never have to...
ReplyDeleteContinued....
ReplyDeleteSo what I'd probably do for that is set up a print driver, and printing defaults that had the media stuff pre-selected and just have Access knock out the report to that print driver. I get it to do something very similar for shipping labels. Install a copy of a printer, share it, configure its defaults to be what it will need for that purpose and that purpose alone, and then send any label printing to that printer instance. That works REALLY nice in an SBS environment, if you configure and share the printer from the server. Heck, in an enterprise environment, to write up a script that makes a connection to a shared printer in the user's context and configures it, and then another script that deletes the same after Access was done with it would work too!
Thinking about it overnight--depending on just how many reports and just how many configurations you needed, I would have just copy-paste-saveas the report with new names -- 'rptReportConfig1','rptReportConfig2'... and set each one up the way it needed to print, and have the form that launches the report (I assume you have a form on the go that users click a command button on, and that the report draws some parameters from) handle which of the configurations to actually print.
It's clear that you view Access the way I view Excel -- as a nice toy that has some uses. IF you come to understand it, you will see that it is indeed a kick-ass tool -- when used correctly -- even in an enterprise environment, perhaps especially then because most of the licensing costs are already taken care of. Put your backend on SQL Server Express Edition on a dedicated box--then you don't have to worry about your Access users taking down your big SQL iron. Do your simple queries in Access. Do your complex queries as views or sprocs in SQL Server Management Studio (Express). Write good, documented VBA code. Use a proper naming convention. Set a reasonable query timeout. Put the front end on each workstation. Build some scripts to push out updates to a staging point, and to pull them down to the local workstation. Give 'er.
I run 25 concurrent users in 2 sites, with 2 remote users connecting through aircards/terminal services. Backend is on a PowerEdge 1900. SSEE isn't even in the top 5 proceses for CPU usage. The backend is approaching 300 MB. There are 13 GB (43000 files) of pictures linked into the app. Its all good.
But you have to know how to do it and use the tool appropriately. And cleaning up after an amateur, or a hack that didn't develop from a plan can be very painful indeed.
Nick67
For those that think it is difficult to change printer settings:
ReplyDeleteChanging printer settings for a report in Access
http://office.microsoft.com/en-us/access/HA010345531033.aspx