<

Printing many large images in MS Access reports

Published on
13,705 Points
4,305 Views
14 Endorsements
Last Modified:
Approved
With the ubiquity of camera phones, you can document many, many things with images. When it comes time to commit those images to paper in the form of an MS Access report, you will quickly discover that Access doesn't really care to render and print an unlimited number of arbitrarily large images.

Depending on the size and number of the images, the version of Access, and the oomph of the machine doing the heavy lifting, the number of images you can get printed before Access doesn't print them, gets dog-slow, hangs or crashes can vary. But there is general unhappiness at some point. Fortunately, we can get good results if we know a few facts, follow a few best practices, and work with some VBA code.

First, the facts. MS Access prints images at the web-standard 96 dots per inch (DPI) for the most part. How do I know that? Experimentation. Put a big, unbound image control on a report (say 8" x 6") with a size mode of 'clip'. Now bind it to a small image to it (say 200 x 150 pixels). Print it and get out a ruler. 96 dots to the inch. That's the minimum.

Next, how does Access resize to that resolution? Back in the day, Access only dealt with .bmp files. I suspect that Access internally converts the image to a bmp and then resizes it. If you've ever converted a .jpg to a .bmp and watched memory usage in Task Manager, you know this isn't an easy operation and consumes vast quantities of RAM. Watch Access's RAM consumption when you are trying to spool up a report with lots of big pictures, and you can watch RAM consumption go through the roof, too.

Second, best practices. Yes, I know that Access can deal with BLOBS (binary large objects) and you can create OLE object fields in tables. I can steer my car strictly with my thighs, leaving my hands free to hold a novel for me to read while driving down the highway, too. Neither one of these things is a good idea, even if they are possible.

You should store the images used by MS Access in a folder, and in the database you should store the path and filenames of those individual files. You won't run into many problems that I won't go into great detail about here if you store images as files and not in tables -- and it makes embedding and printing those images much simpler.

Third, VBA code. You need a nice little form to fire report opening events from (which is best practices, too!), and to let you customize the DPI you'd like to attempt to print the images at. Then you need the report, with code to resize the images on the fly and clean up afterward. Here it is:
 
Option Compare Database
Option Explicit
'reference to Microsoft Windows Image Acquisition Library 2.0 is required
'***use the code as desired
'***please maintain a reference to Nick67 of Experts Exchange as the author

Private Sub ShrinkEm(thePictureID As Long)
Dim s As String
Dim BuiltPath As String
Dim TempPath As String
Dim x As Integer
Dim Img As WIA.ImageFile
Dim myfolder As Object
Dim myfile As Object
Dim DesiredDPI As Integer

Dim IP As ImageProcess
Set IP = CreateObject("WIA.ImageProcess")

Dim fs As Object 'our friend the filesystemobject
Set fs = CreateObject("Scripting.FileSystemObject")

IP.Filters.Add IP.FilterInfos("Scale").FilterID
'IP.Filters(1).Properties("MaximumWidth") = 1600 'you can hand code an exact size
'IP.Filters(1).Properties("MaximumHeight") = 1200 'you can hand code an exact size
      
'variables for a recordset in tblPictures which stores the path to the images
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM [tblPictures] WHERE PictureID = " & thePictureID, dbOpenDynaset, dbSeeChanges)

BuiltPath = rs!Path 'where the original is
TempPath = CurrentProject.Path & "\Resized\" ' a place to store dynamically generated images
'clean out resized folder
Set myfolder = fs.getfolder(TempPath)
For Each myfile In myfolder.files
  fs.DeleteFile myfile.Path, True 'whack the file, force it to die
Next myfile

DesiredDPI = Nz(Forms!form1!txtDesiredDPI, 96) ' from the form
For x = 1 To 3 'resize and point the three controls at the resized image
  Set Img = CreateObject("WIA.ImageFile")
  Img.LoadFile (BuiltPath) 'load the stored jpg in WIA
  IP.Filters(1).Properties("MaximumWidth") = Me.Controls("Image" & x).Width * DesiredDPI / 1440 '96 dpi * control width / twips per inch
  IP.Filters(1).Properties("MaximumHeight") = Me.Controls("Image" & x).Height * DesiredDPI / 1440 '96 dpi * control height /t wips per inch
  Set Img = IP.Apply(Img)
  s = TempPath & x & ".jpg"
  Img.SaveFile (s)
  Me.Controls("Image" & x).Picture = s
  Set Img = Nothing
Next x
End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
ShrinkEm (Me.PictureID)
End Sub

Open in new window


Pushing the DPI above 600 is likely pressing your luck for getting Access to print the image. In production, I use 200 DPI as the standard.

Attached is a sample. Unzip it so that the path of the database is c:\PrintIt to work with it. Give it a whirl, and let me know what you think. The following sample was created for use in Access 2003 on Windows 7. The problem still manifests itself in Access 2013 and the code will work on all versions from Access 2003 onward and may work on previous versions as well. Both Vista and Windows 7 have the WIA 2.0 library used installed by default. It could be installed on Windows XP but has become very difficult to source from MS. The library is wiaaut.dll and it must be registered on XP systems to work.

Nick67
PrintIt.zip
14
Comment
Author:Nick67
3 Comments
 

Administrative Comment

by:Eric AKA Netminder
Nick67,

Congratulations; your article has been published. Thank you for your collaboration in getting this done.

ericpete
Page Editor
0

Expert Comment

by:kico222yilin
a  usefull article
0

Expert Comment

by:Jan Huijs
Nick, great article. Your article helped me solving an issue that has troubling my mind for quite some years! Thanks sooooo much! With many greetings here from Renkum, the Netherlands! Jan Huijs
0

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month