Report formatting problem (1 Viewer)

danielpilon22

New member
Local time
Today, 06:57
Joined
Jul 5, 2017
Messages
2
Hi everyone !

I built a database for all my board games, and with 178 games and over 400 images total, I just can't create a report of all the records without Access 2013 not responding and then crashing. We're talking about an image folder weighing about 95 Mb overall (about 215 Kb per file, on average), so maybe that's a lot to swallow for poor Access.

The end game for me would be to be able to print a whole inventory of my games within one click, with all the visual stuff I put in (photo of the box, photo of a game being played, level of difficulty, level of competitivity, appreciation from 1 to 5 stars, etc.). I'd rather not trim it down, because it's much more appealing and colorful with all the clipart added in. Maybe I could turn some images off while formatting the report ? Make it "print only" ?

The database itself weighs less than 10 Mb. In other words, those images are not embedded directly into the Access file itself, as it tends to bloat the database: each file path ("F:\folder\subfolder\access files", for instance) is written down in a field, in my main table. With a bit of VBA coding, I can link the path to the corresponding image frame, so that every time, the database comes looking for the right image path to display the right image (i.e., if you open the "Risk" record, then the "Risk" game box picture would appear). For a smaller database, it's fine, but now, it just crashes Access while formatting the report. The form preview works pretty fine, but I guess that loading just a single page at a time helps.

This may not be the smartest way around for purists, since there is a lot of redundant code hanging about (Access' archenemy of sorts), since most of the files do carry the same path over and over again (in my previous example, "F:\folder\subfolder\access files" repeats itself for every single image I put in). I tried to edit out the repetitive part out with some intricate query or VBA coding in order to help Access filling in the repetitive bits without hardcoding them into a table, but I only ended up in more misery.

Note: this is only for my personal use on my sole PC, so there is no fancy server architecture behind all this. I tried compressing files via an image editor, changing the file extensions to BMP, compressing the images via Access (which did a ludicrous job, to be polite). Taking every picture off the report is sadly the only way to get the report working without any crashes... but where is the fun in that ? ;)

Would there be an easier, more efficient way (a query, a better table structure, some VBA miracle perhaps ?) to load images into a report so it can work out again, even though there is a massive amount of images to display ? Or have I gone too far with my ambitions ?

(As of now, the only workaround I found was to create an individual report for a single game via VBA coding: according to the opened record (in the Form), the macro translates the details into a single-page report for this particular game. It does the trick for every new game I add, but not for the whole inventory.)

Thanks in advance,
Daniel Pilon.
 

JHB

Have been here a while
Local time
Today, 14:57
Joined
Jun 17, 2012
Messages
7,732
What you describe here shouldn't crash MS-Access, so I suspect something else is going on.
Have you tried to show the report without the Image control? Make a copy of the report and remove the Image control.
If it still crash, the try a "Compact & Repair" or create a new database and import all into it.
If it not crash, then try to load the same image into all records.
If you don't want to print out the report, you could use a form instead.
 

Minty

AWF VIP
Local time
Today, 13:57
Joined
Jul 26, 2013
Messages
10,355
I'm guessing that the full inventory is at least a 178 page report. With images that may well take a very long time to format. Have you tried just directly exporting the report to PDF, rather than making it appear on screen?

It could be that you simply don't have enough machine memory available to format it.
 

isladogs

MVP / VIP
Local time
Today, 13:57
Joined
Jan 14, 2017
Messages
18,186
Hi

Attached is a small database used to display all images in a selected folder.

The form is used to view thumbnails & info for individual images
The report shows thumbnails of each image





The folder is selected by clicking the 'Populate Image List' button

As you will see from the screenshots, it can handle large numbers of images without problems.
This folder contains over 900 images
Another has over 3000 photos which also works fine but takes slightly longer to load the report

If its any use, feel free to use or adapt to suit your purposes.


HTH
 

Attachments

  • ImageDisplayForm.jpg
    ImageDisplayForm.jpg
    89.9 KB · Views: 380
  • ImageDisplayReport.jpg
    ImageDisplayReport.jpg
    102.5 KB · Views: 457
  • FolderImages EMPTY.accdb
    648 KB · Views: 117

danielpilon22

New member
Local time
Today, 06:57
Joined
Jul 5, 2017
Messages
2
Wow !

First of all, thank you all for your kind cooperation. It's all very appreciated... and most of all, you certainly gave me hope in Access again !

Now, to answer everybody that replied...

JHB

Have you tried to show the report without the Image control? Make a copy of the report and remove the Image control.
If it still crash, the try a "Compact & Repair" or create a new database and import all into it.
If it not crash, then try to load the same image into all records.

Good thinking, but unfortunately, even with the pictures taken off, it oddly crashes the very same way as before. The text appears, the blue cursor spins around for about 10 seconds, and then, Access stops responding. I'm baffled.

MINTY

I'm guessing that the full inventory is at least a 178 page report. With images that may well take a very long time to format. Have you tried just directly exporting the report to PDF, rather than making it appear on screen?

It could be that you simply don't have enough machine memory available to format it.

That's correct, it is a 178 page report. And opening the report in Print Preview, in order to print into a PDF file, is certainly a smart way to go, indeed. Unfortunately, it also uncovers an unexpected and downright awful fault in my design... it generates a whopping 1000+ page report (I cancelled the process after the fourth digit appeared), mostly made of copies of copies of my records, from what I can see. I just can't figure out it repeats itself that much, probably a bad query. Still, good guess from you too.

And yes, it could have been my PC that's slowing it down, but I have a strong PC with 16 Gb of RAM. It could still have been my mechanical HDD that's not up to par.

RIDDERS

Hi

Attached is a small database used to display all images in a selected folder.

The form is used to view thumbnails & info for individual images
The report shows thumbnails of each image

The folder is selected by clicking the 'Populate Image List' button

As you will see from the screenshots, it can handle large numbers of images without problems.
This folder contains over 900 images
Another has over 3000 photos which also works fine but takes slightly longer to load the report

Impressive stuff you got there, ridders ! And here I was, thinking that I was asking way too much of Access. ;) I will definitely try to adapt your method to my database... although Minty made me realize that there is a much nastier fault creeping around my report. A wrong query could be the real issue.

FINAL COMMENTS
Thanks to you all, the mystery has been solved ! You each brought a clue and they all lead me to a faulty query that sabotaged report formatting with redundant data. For some reason, redoing that query from scratch, with the very same tables and fields, seems to have put Access back into shape, though I can't figure out why the original query went bad in the first place... In a matter of 5 or 6 seconds, every thing appears. And I'm SO pleased with that ! Good job, people.
 

isladogs

MVP / VIP
Local time
Today, 13:57
Joined
Jan 14, 2017
Messages
18,186
Good to hear you've got if solved & that we all helped in different ways.

If you do want to test mine, all you need to do is click the Populate Image List button & select a folder containing images. Job done!
 

Users who are viewing this thread

Top Bottom