Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Large Table Problem (Access 2000)

    We are having a problem with a table that holds ole objects (pictures of supplies). The pictures where compressed and exported from PhotoShop into Access. The table has gotten so big that it is crashing/ hanging some of our computers. Does anyone have any suggestions as to what we can do with this table.
    Thanks.
    Robin

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Large Table Problem (Access 2000)

    You should Link to these objects rather than Embed them.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Large Table Problem (Access 2000)

    Hi Again.
    I would link them to the actual .jpg file?
    Robin

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Large Table Problem (Access 2000)

    Hi yourself,
    Is that a statement or a question?
    As far as I know (others know more) when you link an object it records only the address of where the object is on the network. If you embed you actually save the entire .jpg file in the table and so the database bloats very quickly.
    Pat

  5. #5
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Large Table Problem (Access 2000)

    I guess it was a question - that I really answered myself when I wrote it - <img src=/S/confused.gif border=0 alt=confused width=15 height=20>. Thanks so much for all your help and time on both questions.
    Robin

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Large Table Problem (Access 2000)

    Hi Robin,
    I should expand on what Pat suggested. You really don't want to either embed or link to OLE objects, as either causes significant bloat in your database for every record. It seems to cause a database to grow about 1MB for each record with an OLE object (even though the object is only a few KB) - I suspect it's because you actually store the image/object conversion routine in the table. In any event, with a thousand record table, you are getting to the point where you exceed the maximum size of a database. The preferred method is to actually store a text string that is the path to the file containing the image/object. It's a pain to change designs at this point, but you will find it works much better.
    Wendell

  7. #7
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Large Table Problem (Access 2000)

    Hi Wendell - got late and didn't read your response until this morning. Thank you very much. I know it will be a lot of work to change but we can't have the db crashing. The pictures are part of our catelogue.
    Thanks again. This is such a great feature (the Lounge) to have available.

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Large Table Problem (Access 2000)

    Not to worry - I just posted it about 20 minutes ago. If you need help in figuring out how to get the path to the file, post again, and I'm sure someone can help if I don't.
    Wendell

  9. #9
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Large Table Problem (Access 2000)

    Thanks for the offer Wendell - I was going to see if I could find something in my Access book re storing a path. Do I just create a field and put a hyperlink (path) to the file????

  10. #10
    Star Lounger
    Join Date
    Aug 2001
    Location
    Seattle, Washington, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Large Table Problem (Access 2000)

    Robin,

    Charlotte posted a sample db that appears to do what you want. It's available through this thread. See the sixth message.

    HTH,

    Tom

  11. #11
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Large Table Problem (Access 2000)

    Thanks Tom. This is great info.

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Large Table Problem (Access 2000)

    Sorry for the delay - I had to do some real work at a client! Work seems to keep getting in the way here!
    In the meantime I see you've been pointed to a post of Charlotte's so you probably have things well in hand, but just to summarize:
    <UL><LI>The field doesn't need to be treated as a hyperlink unless you want people to click on it and actually open the photo in whatever application is linked to jpg files - we normally make it a text field 255 char in length and leave it at that.
    <LI>The bigger trick is figuring out how to make it display on a form - for that we use an image control, and each time a new record is displayed in the form we set the path to the jpg file using the Current event of the form - of course you need to check for nulls or empty strings to avoid errors.
    <LI>Finally we have a browse button on the form that is visible only to some users so the path to the correct photo can be entered - we use the common dialog box for files to let the user search for the correct file.[/list]Hope this works well for you - it should be far better than the OLE object approach.
    Wendell

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Large Table Problem (Access 2000)

    Thanks for the clarification Wendell, I thought that linking didn't take much space.
    Memory (mine I mean) is a wonderful attribute, if it works. I have just looked back in one of my apps where I needed to display .bmp files and found that I was recording the filename of the .bmp file in my table. From memory I do recall that I also had to change from "linking" to recording a filename. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Pat <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

Posting Permissions

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