Printing many large images in MS Access reports

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

Select all 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