storing images in Access (2003)

Topic

New Reply

I have a report that produces 4,500 records. It uses two tables as its data source. The primary table (LocationItems), with 4,500 records refers to the other table (items) with just 500 records. The items table has a field with paths to image files.

So the same pictures are repeated for each item at each location.

When the report is run it runs out of memory.

Would storing the images as OLE objects in the Jet database improve performance? for example would each image be loaded into memory just once instead of for each individual record?

Reply | Quote

Replies

With Access 2003 the best option is not to store the images in the database at all. Instead keep them outside the database, and just store the path to the image in the data base.

You will some examples in post 296,892 .

Reply | Quote

Yep, that’s what I am doing but the repeated display of the same pictures in this report (average of 12 times each) runs out of memory so I was just investigating whether,

if I stored them in the database, would Access be smart enough just to use a pointer to the image in memory rather than loading multiple copies of the same image as it does when the images are stored externally.

Reply | Quote

Sorry I see that I did not read your question carefully enough.

I don’t know the answer to your question, but I would be surprised if it improved performance. Storing images in the db has a reputation for creating bloat, as Access converts them to bitmaps.
2007 is supposed to be much better, but I have not tried this with 2007.

Have you done everything you can to reduce the file size of the images?

Can you produce the report in stages so it is not all in memory at once?

Reply | Quote

I have two databases with large amounts of pictures one in 2003 and one in 2007. The one in 2007 works perfectly fine. The 2003 one I had the same problem as you, so I set the printout of the report to print in sequence each page, opening and closing the report between each page and that solved the problem.

Reply | Quote

I’ve got the file sizes down to 2 or 3 KB JPGs.
So, Zave, How do you open and close the report between pages?

Reply | Quote

It is pretty primitive but t works.

On Error GoTo Err_Command2_Click

Dim stDocName As String
Dim x As Integer
x = 1
stDocName = “repStudentInfo”
DoCmd.OpenReport stDocName, acPreview
Do Until x = 20
DoCmd.PrintOut acPages, x, x
x = x + 1
Loop
Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

Reply | Quote

Thanks Zave, I’ll give it a go.

Reply | Quote

I am having this problem, too. I have an application that has a large main report with 15 subreports on it, seven of which can contain images. I use the image control in all cases, having the image file network path and filename stored as a hyperlink in various tables within the system.
For the most part the system works well, but when the number of images to be printed in a particular report becomes large, say 40 or 50, in some cases the system runs out of memory and just generates blank pages.
The system is set up to produce a PDF of the entire report, which in some cases exceeds 100 pages many of which are images. I’m planning to try a variation of Zave’s idea of printing the report a page at a time, but I need to produce a single PDF file and I don’t want the users to have to assemble manually a composite file out of many smaller PDFs.
The problem doesn’t seem to have any predictable pattern. The image filesizes range from 20KB up to 600-700KB in size but the filesize doesn’t always seem to affect the result. Most of the files are JPEGs, and I’ve tried converting to GIFs and/or PNGs but the problem persists. The problem usually occurs when there are more than 20 or 30 images to print successively without intervening text – the typical report has topical images interspersed throughout amidst pages of text.
Does anyone else have experience with this or suggestions on how to overcome?

Reply | Quote

Depending on the structure of the report, it might be possible to use mail merge in Word with an Access table or query as data source instead. I use this method successfully to produce “reports” with thousands of pictures whose filename is a field in the data source.

Reply | Quote

Thanks for the suggestion. Do you simply merge in entire pages of report into Word? I have done many Access reports in Word using automation, but haven’t done mail merge very much.

Reply | Quote

I create the “report” entirely in Word, with an Access table or query as data source, i.e. I bypass the report feature in Access entirely.

Reply | Quote

I see. I had thought about this before and wondered whether it might work better, but didn’t want to invest the time unless I had some confidence that the problem could be overcome this way.
Thanks again, I’m afraid I have a bit of work in front of me.

Reply | Quote

Sorry to belabor this, but why, or how, does this handle many images/pictures successfully while Access does not? (Because of the complexity of the report in question I foresee a great amount of work to replicate it in MS Word.)

Reply | Quote

I don’t know how it works, but I suppose it has to do with the way Access builds a “print image” in memory.

I did mention that the feasability depended on the structure of the report. An alternative is to print the report to PDF a page at a time, as mentioned in this thread. Adobe Acrobat should be able to create a PDF file from a series of files.

Reply | Quote

Thanks again, Hans.
I think I’ll pursue the many PDF pages idea first as that would be much easier from where I have to start.

Reply | Quote

(Edited by HansV to make URL clickable – see Help 19)

Jon, you can use a PDF maker like http://www.fineprint.com/products/pdffactory/index.html%5B/url%5D to make one PDF from multiple reports or docs. You start a PDF then print as many docs as you want to until you save the PDF to complete it.

Reply | Quote

Kent,
Thanks, I’ve been testing PDFFactory for that very reason.

Reply | Quote

This may or may not be a factor, but are you printing a “page x of y” on your report? If you are, just show the page #, without the “of y”.

Reply | Quote