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
'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(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
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"
Me.Controls("Image" & x).Picture = s
Set Img = Nothing
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
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.