Russell Sinclair and Peter Vogel
Russell Sinclair has some answers for working with printers and UNC names. In addition to solving some interesting problems, you'll also get a look at the Windows API. Peter shows how to use ActiveX controls with Access.
I use the Access runtime to distribute many of my databases. Whenever I create reports that use any paper size other than the default paper size for the printer, users who receive a copy of the database have to preview the report and manually change the page setup before they print it. Is there any way to have my reports retain the paper size on other machines?
Russell: This has been a huge issue for me with many of the databases I've created in the past. About half of the reports I use are designed for legal size paper with a landscape orientation. Although Access does retain the orientation of the report from machine to machine, it doesn't carry over the paper size and will use the default paper size for the printer when it prints the report. This is very frustrating when most of the other Microsoft programs retain document settings for paper size between different machines.
I've received many calls from users saying that their reports are getting cut off at the sides, and I then have to walk them through the process of previewing the report and changing the page setup. In an MDB database, once you've previewed the report and changed the setup, it's retained for every time you use the report after that. Unfortunately, MDE databases don't allow you to save the setup of a report, since you can't modify the design of reports in MDE databases.
My first attempt to solve this problem involved using the Windows API to change the default paper size for a printer. My plan was to change the default before previewing or printing the report, then change it back to the original settings after the user was done with the report. Unfortunately, this is considerably harder than I could have imagined it to be. There are a number of different functions in Windows 95 and NT that can be used to retrieve or change settings for printers. In Windows 95, however, these functions are all intended to be used in languages like C++, where control of printing documents is done on a page-by-page basis. Nevertheless, I thought I could get it to work with Access.
Working with any printing process in Windows requires that you use a structure called the DevMode structure. This structure defines the different settings for a printer, including orientation, paper size, number of copies, and other settings you can see when you access the Printer Setup dialog box. You can see the way Windows defines this structure in the user-defined type DEVMODE in basAPIPrintSetup in the sample database (PrintSetup.MDB, available in the accompanying Download file). In Windows 95, there's only one API function that will let you change the printer setup through code. The ResetDC API call allows you to change a device context (an abstraction in Windows to allow the system to work with various items in the operating environment such as printers, windows, files, and so forth). The ResetDC API is declared as follows:
Declare Function ResetDC Lib "gdi32" Alias _
"ResetDCA" (ByVal hdc As Long, _
dmInitData As DEVMODE) As Long
The argument hdc is a handle to a Windows device context, and dmInitData is the DevMode structure containing the settings you want to change the printer to. In order to use this function, I needed to obtain some information from the system regarding the printer in question.
To obtain a handle to the device context of the printer, I first had to open it for administration. This requires a call to the OpenPrinter API function. This function uses the PRINTER_DEFAULTS structure that includes the DevMode as one of its members. In order to open a printer for administration, I had to retrieve the settings for the printer using the DocumentProperties API function. As you can probably see, this leads you into a nightmarish pattern of backward calls to API functions that can be very confusing. Once you've changed the settings, you then have to close the printer (ClosePrinter API function) and release the device context so that Windows can use it for other functions (ReleaseDC API function).
Although I could go into great detail about how to use each of these functions and what they do, it wouldn't be very useful -- none of it worked. It was only after I'd been through this whole process that I realized that the ResetDC function is intended to be used by programs such as C++ that can manage their own printing through the API. It's intended to change the paper size of a print job between the pages of the print job. You can see the code in basAPIPrintSetup in the sample database.
An attempt to use the ResetPrinter function was similarly unsuccessful -- it turns out that it's only supported in Windows NT (though changing the printer setup is precisely what it's documented as doing).
This left me with a very good understanding of how Windows handles printing but not much hope for what I could do in Access. That is, until I found out that forms and reports in Access have their own DevMode in the PrtDevMode property. The solution looked simple: Build a DevMode structure, and then set the PrtDevMode property. However, in order to change the PrtDevMode property for an object, it's necessary to open the object in design view. This means that this solution won't work for MDE databases, though it's still very useful for non-MDE databases.
The PrtDevMode property in Access is a string. The length of this string varies according to the printer being used and the capabilities of that printer. However, only the first 94 characters of the string are necessary when changing the print settings for an object. In order to access the information in this property, you first save the existing value to a variable-length string (this ensures that you retain all of the information in the PrtDevMode property). The first 94 characters of this string must be moved to a user-defined type DevModeString that has only one 94-byte element, fstrRGB. Finally, you can convert DevModeString to a more structured format (see Listing 1) that's only slightly different from the Windows DevMode structure, using Access's LSet function. The LSet function enables you to save one user-defined type to another user-defined type, even if they have different structures. The code to do this looks like this:
strDevMode = rpt.PrtDevMode
dmsReturn.fstrRGB = strDevMode
LSet dmaModify = dmsReturn
Listing 1. The DevMode structure for an Access form or report.
fstrDeviceName As String * 16
intSpecVersion As Integer
intDriverVersion As Integer
intSize As Integer
intDriverExtra As Integer
lngFields As Long
intOrientation As Integer
intPaperSize As Integer
intPaperLength As Integer
intPaperWidth As Integer
intScale As Integer
intCopies As Integer
intDefaultSource As Integer
intPrintQuality As Integer
intColor As Integer
intDuplex As Integer
intYResolution As Integer
intTTOption As Integer
intCollate As Integer
fstrFormName As String * 16
lngUnusedPadding As Long
lngBitsPerPixel As Long
lngPelsWidth As Long
lngPelsHeight As Long
lngDisplayFlags As Long
lngDisplayFrequecy As Long
At this point, you can modify the individual elements in the printer setup of the report. The function ChangePaperSetup in the sample database demonstrates changing the page size and orientation by modifying the intPaperSize and intOrientation members of the report's DevMode structure. You must ensure that you use the values of the DMPAPER constants and the DMORIENT constants to set the proper settings for the report. Once the settings have been changed, it's necessary to send the DevModeAccess structure back to the DevModeString type using the LSet function. Finally, you set the first 94 characters in the string that stored the full PrtDevMode property to the same value as fstrRGB using Access's Mid function (the Mid function can also be used to save a string to a specified portion of another string). The report can then be closed and saved, and the new settings will take effect.
As mentioned earlier, this method won't do you much good if you're using an MDE database. For exactly this reason, the ChangePaperSetup function checks to see if the database is an MDE database before performing the changes. This is accomplished by checking the MDE property of the database. If this property exists (non-MDE databases don't have this property) and is set to "T", you're executing in an MDE database. The function simply checks the property, and if error 3270 is returned (property not found), then it's not an MDE and can be modified.
What can you do about MDE databases? A number of months ago, I created a report that required that it be routed to a color printer. When I distributed this database, I noticed that an error message was produced on a user's machine that the report was looking for the printer under the same name that I had on my computer (the user had named it something else). Access retains the name of the printer the report should go to if the default printer wasn't specified. This gave me an idea for how to work around the problem with MDE databases. I set my legal paper sized reports to print to a specific printer called "Legal Printer" and created a printer on the user's machine with the same name, then set the default paper size on the new printer to legal. Believe it of not, this actually works. Sometimes the simplest solutions are the best.
How do you implement the non-MDE solution? One of the lines I always use in my distributed databases is a line in the Autoexec macro that checks for the existence of a module called basUpdate. This can be accomplished with the ObjectExists function included in the sample databases. If this module exists, the macro runs a function in that module to update the table design of attached databases and make other such changes that can't be managed through the interface. After this function is run, the module is deleted. My latest version of this module includes code to change the page setup of all reports that should print on legal size paper. Once changed, they won't have to be updated again until I send out a new version (in which case the Update function will simply run again).
In the November 1997 issue of Smart Access (see "Repair and Compact Attached Databases"), there was a function that enabled a program to check the free space on drive before repairing and compacting a database. But the program returns an error that there's not enough free space on drive "\\" if the drive you're repairing and compacting the database on is set to a UNC drive. Is there a way to check the free space on a UNC drive?
Russell: You've come to the right place -- that was my article and my function. The truth about this problem is that it didn't even occur to me to test it on one of my databases that resides on the network (let this be a lesson to me). The problem is that the GetDiskFree API call requires a mapped path to check for free space on a drive. Essentially, I needed to map the drive temporarily so that the function would work. Once again, this required that I turn to the Windows API for help.
In order to map the UNC path to a network drive, I first had to find a drive path that wasn't being used. The Windows API has a function that returns the type of drive that a drive mapping refers to (CD-ROM, fixed, removable, and so forth). The function, GetDriveType, is declared as follows:
Declare Function apiGetDriveType Lib _
"kernel32" Alias "GetDriveTypeA" _
(ByVal strDrive As String) As Long
The function returns a long value corresponding to one of the Windows DRIVE constants, or 1 if the drive isn't mapped (which is what I was interested in). I looped backward through the possible network drive letters (Z: to F:) and checked each one to see if it was being used.
Once I'd found a drive that wasn't mapped, I could map the drive, use my original API call to check the free space, and then delete the temporary mapping. To map the drive and then delete my mapping, I returned to the API and used two function calls -- WNetAddConnection and WNetCancelConnection. WNetAddConnection has three arguments: the network path to connect to, a password for the connection (for which you can use the constant vbNullString, since the function assumes you're already connected to the path), and the new mapping in the form of the drive letter and a colon. When I added the connection, I also set the variable boolAddedNet to true so that I'd know later if the drive had been temporarily mapped.
The part of the function that checks the free space is unchanged. Once the free space is determined, the function checks to see if a temporary mapping was used and deletes the mapping if this is the case.
I've made only one assumption that you should be aware of: The function won't work if all of your drive mappings are being used. It will still return a message saying that there's not enough free space to compact the selected database. However, I felt that this was a reasonably safe assumption, since few people use more than a few drive mappings at a time. The sample database in the accompanying Download file includes the updated function with the original CompactAttachedDatabases function that originally appeared in the November 1997 issue.
I want to use the Microsoft Common Dialog Control in my Access database but can't get to its methods and properties. My IntelliSense drop-down list just doesn't list the object's properties and methods.
Peter: I'm assuming from your question that you're a Visual Basic programmer moving over to Access and want to leverage the neat tools that you've learned in VB. You'll be happy to know that most of what you learned in VB works in Access, but there are a couple of wrinkles to working with controls that you'll need to pick up. The Form object in Access is a different Form than what you're used to from VB. This makes it a lot easier to create database applications, but ActiveX controls (as you've found) are treated differently.
One way to think of what happens when you add an ActiveX control to an Access form is that you're not adding the control you selected. Instead, you're actually adding an ActiveXControl object to your form. That ActiveXControl object contains within it the ActiveX control that you want to use. In your code, when you see the IntelliSense drop-down list boxes, what you're seeing are the methods and properties for the ActiveXControl object -- not the object that you want to use.
To get at the object you want to use, you must use the Object property of the ActiveXControl object. So, to use the ShowOpen method of the Common Dialog control you've added to your form, you'd write this:
Even when you use the Object property, VBA won't display the IntelliSense drop-down lists. VBA gets the information for the list boxes from the type library for the object (a type library is created and shipped with most ActiveX controls). Because the Object property is declared with a generic type, VBA doesn't know what object is stored inside the control and doesn't know what type library to use.
In order to get your IntelliSense boxes back, you'll need to give VBA a clue as to what object you're working with. The simplest way to do this is to create an object variable of the correct type (in this case, the Common Dialog box) and use it to hold a reference to the object inside the ActiveXControl. Once you've done that, you'll find that -- if you work with the object variable -- VBA will provide you with the IntelliSense that you're missing (your program will even run a few milliseconds faster, too). The code to do this will look like this:
Dim cdl As CommonDialog
Set cdl = ActiveXCtl0.Object
Figure 1 shows this code with the appropriate IntelliSense support.
Read about the Download SA9805QA.ZIP on this page