Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    storing images in Access (2003)

    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?

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: storing images in Access (2003)

    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>post 296,892</post#> .
    Regards
    John



  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: storing images in Access (2003)

    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.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: storing images in Access (2003)

    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?
    Regards
    John



  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: storing images in Access (2003)

    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.

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: storing images in Access (2003)

    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?

  7. #7
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: storing images in Access (2003)

    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

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: storing images in Access (2003)

    Thanks Zave, I'll give it a go.

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: storing images in Access (2003)

    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".
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Chicago, Illinois, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: storing images in Access (2003)

    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?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: storing images in Access (2003)

    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.

  12. #12
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Chicago, Illinois, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: storing images in Access (2003)

    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.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: storing images in Access (2003)

    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.

  14. #14
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Chicago, Illinois, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: storing images in Access (2003)

    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.

  15. #15
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Chicago, Illinois, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: storing images in Access (2003)

    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.)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •