Large Table Problem (Access 2000)

Topic

New Reply

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

Reply | Quote

Replies

You should Link to these objects rather than Embed them.
Pat

Reply | Quote

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

Reply | Quote

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

Reply | Quote

I guess it was a question – that I really answered myself when I wrote it – . Thanks so much for all your help and time on both questions.
Robin

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Thanks Tom. This is great info.

Reply | Quote

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:

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

Reply | Quote

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

Reply | Quote