• Access97 Image bloat

    Author
    Topic
    #1769790

    I’m using Access97 which stores 620Mb of images. I updated and replaced many of these images and the file bloated to its max size of 1.024Gb. I compacted and the file only went down to 960Mb instead of 620Mb. I’ve tried importing to a new file but still get 960Mb.

    So how do I recover my lost 300Mb? I haven’t added any new images, just replaced the ones already stored.

    Thanks in advance!

    Viewing 1 reply thread
    Author
    Replies
    • #1784542

      It’s a very BAD idea to store images in a database, and deleting and replacing them doesn’t reuse the storage allocated to the deleted image, it just grabs new storage. Even compacting may not recover the space since the new images may not be the same size as those you deleted.

      You’re getting uncomfortably close to the upper limits of Access 97, anyhow, and I’d suggest you look into linking the images to the database instead. That keeps the size out of the database but gives you access to the images.

      If this database is on a network, you’ll see especially bad performance with this much image storage and you may get irate notes from your system administrator when the server is filled up as a result.

      • #1784544

        Charlotte, thanks, I know this but unfortunately I am not in a position to change the database design – I just need to reclaim the 300Mb!

        Even importing to a new database doesn’t resolve the problem – what can I do here?

        Thanks again.

        • #1784547

          Probably nothing. Are you sure the replacement images were the same size as those deleted?

          • #1784581

            Yes exactly the same. As for it being a BAD idea, isn’t this precisely what Access is supposed to be able to do? Sheesh, it’s not my fault I believed their hype! If there is indeed nothing that can be done then I call it a huge B-U-G.

            • #1784584

              Access is a database, not a document management system. Graphics files are generally much larger than any data you might cram into a record, so they bloat the database enormously. You can’t really blame that on Access.

              Did you compact the database TWICE? That is sometimes necessary, at least in Access 97 and earlier, to get rid of the extra space. Are you deleting the images from the record or are you running a delete query? Are you deleting the entire record or just the image from the field and then replacing it right then?

              If a second compact doesn’t do it, you could try a decompile before you compact again. That usually squeezes out some extra space.

            • #1784645

              Well – I *do* blame Access for that actually. When I replace a file on my hard disk, Windows frees the space of the original file. I expect Access to act the same, being a standard Windows product – and even made by the same people.

              I guess it’s just another of those many Microsoft ‘features’ that are ‘offered’ but should never ever be used – similar to Word’s Master Documents.

              In any event, I will go to a recent backup and use that. I will also take your advice and rework the application to simply contain links to the files.

              Thanks for your help. Pity there wasn’t a solution.

            • #1784648

              There is a solution, and you’ve found it. I’m sorry that it isn’t what you wanted, but it *is* the solution.

              You can’t compare Access operations to Windows operations at all. Windows deals in files, so it can tell where a file begins and ends. You either overwrite a file or append to a file or you don’t, and every segment of a file may be dislocated from the rest of the file without anyone suffering for it (except in the case of lost clusters, but that’s a different problem) as long as the pointers that link the segments are intact.

              Access, on the other hand, is contained *within* a file. It knows when you expand, but it doesn’t have any way to tell whether it is safe to elminate that expansion until you give it the compact and repair instruction. Some of the expansion is created by the internal queries that Access has to create to follow you instructions for things like delete queries and make-table queries. Data stored in a database is too important to risk damaging accidentally, so Access doesn’t take that risk. And this behavior isn’t peculiar to Access. Database servers like SQL Server and Oracle don’t contract either unless you tell them to. For that matter, dBase didn’t either.

              Windows simply writes a bit into the file header to say, in effect, this file isn’t here anymore so you can reuse the space. Databases have a much more complicated problem to handle, regardless of who publishes them.

              In Access 2000, there is an option to automatically compact a database on exit; but even that isn’t going to be able to handle large graphics well because Access is a database and graphics are *not* really data, although the address of the graphic *is* data.

            • #1784656

              Charlotte, thanks for that – I wasn’t aware of how Access stores its data, that is interesting. Presumably there should be a way of retrieving the data that Access has held on to, but that is a moot point at this stage.
              I’ve been working on redesign to eliminate the OLE fields. That will work fine, but it means, unfortunately, that I have to have the objects and DB together. The original goal was to have one DB which had everything in it to save against lost files, etc.

    • #1784647

      You said you were storing 620MB of images. Was that the sum of the sizes of the original images, or was that the size of the Access database after the images were loaded?

      • #1784649

        That is the size after all images are stored.
        Thanks

        • #1784651

          I just discovered an interesting fact. Compacting a database that contains images ADDS(!) 4KB for each new image that has been stored. Don’t know why, and can’t find any documentation in MSKB about this. All we know for sure is that Access is lousy at storing images. I’d really look hard at a redesign.

          • #1784652

            I tried in 2 DB’s on my PC both ways, embedding and linking, and found the difference not so big (both A97, backend, local). The DB’s had about 30 resp. 70 MB with embedded images, with linking some 10 MB less and both ways growing fast and working very slow, even locally. So I threw away the OLE fields from the tables and use instead a hyperlink field to hold the path to the image file. So the image still can be opened for editing with a click.

            On the form I have an image control, whose picture property gets updated in Form_Current and txtPath_AfterUpdate and a Search Image command button calling the GetOpenFileName API. Now, the DB’s have about 8 resp. 3 MB and performance is much better. After this, using image fields on a network sounds like nightmare for me.

            However, I would like to know, what difference image format and associated software make? I tried these DB’s with BMP 256 colors, JPG and WMF and didn’t notice much difference. The associated program is Paintshop Pro and noticed that having it open speeded up the DB’s considerably. What’s happening actually, when an OLE image field gets filled/displayed? I also noticed, the DB’s got bloated very much, sort of from 7 to 20 MB after linking the very first image. Was Access somehow incorporating something like the “image engine” of Paintshop??

            • #1784653

              Unfortunately, I can’t offer you much more help, as my experience with manipulating graphic files is somewhat limited.

          • #1784655

            Mark, thanks for that – I had noticed that my DB would increase in size when I compacted it and couldn’t figure out why – just one of those things.
            I’ve since been working on redesigning it – no more OLE fields for me! Thanks for your input.

            • #1786338

              Blast!. I have just discovered the same problem. I found out that linking actually adds to the size of the database. From what I can work out about it, the image is still embedded when you use linking and a link is added to the database so that the image can be automatically updated. Here I was thinking that linking would mean that the images were not stored in the database but they are.
              From what I can gather, Access stores images as bitmaps no matter what format you have them in to begin with. I had 300 .jpg photos, average about 10k each but they were about 340k as bitmaps. Hey presto 100MB Access file. Someone showed me a fantastic thumbnailer called Smaller Animals. It will thumbnail all the photos in a folder to your specifications. For reasonable size photos 8×10 150dpi it flies through them at about 1 per second. I then put the thumbnails into Access and a hyperlink to the full size photo underneath.

              My question is: How can I show a preview of the hyperlinked photo and not have the OLE in the table at all. Is this possible?

            • #1786345

              Edited by charlotte on 25-Jul-01 07:23.

              Edited to activate link

              I nearly lost one very attractive contract because of this file storage problem. Finally I use the Word!with Access wizard which replaces the lousy ACCESS OLE procedure completely. In exchange to 200$ I have no more bloating effect and supplementary functions are zooming and preview as well as code how to display these JPGs with ASP from webservers.
              There is a link on the http://www.unsoftwareag.com website (see Powerup ACCESS page).

    Viewing 1 reply thread
    Reply To: Access97 Image bloat

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: