Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    MEA
    Guest

    Access97 Image bloat

    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!

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access97 Image bloat

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

  3. #3
    MEA
    Guest

    Re: Access97 Image bloat

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access97 Image bloat

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

  5. #5
    MEA
    Guest

    Re: Access97 Image bloat

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access97 Image bloat

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

  7. #7
    MEA
    Guest

    Re: Access97 Image bloat

    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.

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

    Re: Access97 Image bloat

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

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access97 Image bloat

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

  10. #10
    MEA
    Guest

    Re: Access97 Image bloat

    That is the size after all images are stored.
    Thanks

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

    Re: Access97 Image bloat

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

  12. #12
    Lounger
    Join Date
    Apr 2001
    Location
    Stuttgart, Germany
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access97 Image bloat

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

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

    Re: Access97 Image bloat

    Unfortunately, I can't offer you much more help, as my experience with manipulating graphic files is somewhat limited.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  14. #14
    MEA
    Guest

    Re: Access97 Image bloat

    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.

  15. #15
    MEA
    Guest

    Re: Access97 Image bloat

    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.

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
  •