Link vs. imbed image: result is counterintuitive

[VirgilMachine]

I have a small access db (access 2010, Win7Pro). The main table currently has 31 rows. I want an image displayed for each row (about 20 currently have them)--different image for each row.



The front end is in one db, and the tables in another.

This all works fine, but I'm puzzled by the results of link vs. imbed. I right-click on the field in the form where I want the image and navigate to the image source (.bmp file) and select "insert image". I'm assuming that when I click "link" in the dialog box, it links to the image, and when I don't click link it imbeds it.

The problem: when I do NOT click link (i.e., imbed the image) the size of the table db explodes to 70MB from less than 10MB. Fine...that must be because the image is imbedded. The way to fix that must be to click "link." I went back to the form, and re-inserted the image clicking "link." The db expands to 130MB!

This makes no sense to me. What am I missing?

[alansidman]

Try running a compact and repair. Access does not give back space when changes are made without a c&r. If you are making changes/updates to design you should run a C&R daily until you have completed the design.

[VirgilMachine]

I did compact, but to illustrate the problem, I did some more work:

1. db size with imbedded images: 77016MB
2. DB size after deleting bitmap field contents: 496K
3. db size after linking: 154928MB
4. db with linked fields after compressing: 154844MB

this is consistent...the size with linked fields is double that with imbedded fields (which is still too big).

[alansidman]

Is this the result of a compact and repair or some other compression?

db with linked fields after compressing

alan

[boblarson]

Oh, and it is embed, not imbed. (just thought you might like to know for future use)

As for the size differences, when you did your tests did you do the embedded version first and then delete them and then do the linking? If so, I would THINK that you would want to try the embedding and then delete them and then import everything into a brand new database shell for the test so that all things are equal so that any remaining baggage might not be there.

[VirgilMachine]

Is this the result of a compact and repair or some other compression?

alan

C&R...thanks again

[VirgilMachine]

Not that it's relevant to anything, but imbed is an alternate spelling. Just thought you'd like to know before you irritate someone else.

No, I didn't do what you said..I did what I said (please read the post for content rather than spelling and grammar). After deleting the field contents and do a compact/repair, the db went to <500KB. What baggage would cause that to go to 150MB when I link to images rather than the 70MB it was with imbedded images?

[boblarson]

Not that it's relevant to anything, but imbed is an alternate spelling. Just thought you'd like to know before you irritate someone else.

Umm, your alternate spelling cannot be found in any dictionary, so I stand by my original statement. And get over it. It was given to help you not look like an uneducated moron, but you seem to not care so I will no longer respond to your posts and will set you in my Ignore list. So don't bother to respond as I won't see it.

No, I didn't do what you said..I did what I said (please read the post for content rather than spelling and grammar). After deleting the field contents and do a compact/repair, the db went to <500KB. What baggage would cause that to go to 150MB when I link to images rather than the 70MB it was with imbedded images?

Because you didn't test it like I said to test, you are not getting a true reading since you have contaminated the test results due to baggage which is left over from EMBEDDING the images and then deleting them.
What part of that did you not understand? I stated that so that you would actually TRY THAT TEST which might actually give you the answer that you were asking for, and what I had stated about LEFTOVER BAGGAGE.

Bye, and have a good day.

[VirgilMachine]

Umm, your alternate spelling cannot be found in any dictionary,

http://www.merriam-webster.com/dictionary/imbed & Webster's Third New International Dictionary..that took me 30 seconds to find. Not bad for an uneducated moron.

Re your suggestion, the test you suggest won't answer my question, which was why? You say you THINK this might help. Do you KNOW what the solution is and why? If so, please enlighten me. What leftover baggage (or what other factor) would cause a table with ~30 rows to expand from <500KB to over 150MB after linking to images, particularly when the same table was only ~70MB with the images imbedded.

Can anyone tell me:

1. Would exporting the table to a fresh structure after deleting the imbedded images, then linking to the images from the fresh table reduce the size of the table well below the ~70MB it took with imbbeded images?
2. If not, is there an alternate solution?
3. If so, why is that?

Thanks for your help.

[Amicron]

Now, now... settle down kids.

Linking AND embedding (imbedding) images in OLE objects are bad news in Access. I put together a whole 3 hour-long tutorial on the subject: http://599cd.com/XGKJ81

Basically, Access handles images horribly using OLE fields. They bloat your database incredibly - even after a compact and repair. The BEST thing to do is to store your images in a folder on your hard drive (or a network share like \\server\images) and then store the LOCATION of those images in a TEXT field in your database.

Now with older versions of Access, you have to use a little VBA code to get that image to display on your forms and reports. Drop a blank image control on your form and in the OnCurrent event, for example, you can say:

MyImageControl.picture = PathFilenameOfImage

That will display the image in the control. The only problem is that this doesn't work for continuous forms - only one per screen at a time. It will work fine with reports though.

In Access 2010, you can do this without ANY VBA code - which is really cool. Here's a tutorial I put together showing you how to do it: http://599cd.com/XBV1G4

If you work with images, that alone is worth the price of upgrade to Access 2010. The bottom line, however, is that Access is really bad with OLE objects.

[Amicron]

Oh, and to answer the question you posed, when you embed an image, Access just stores the image data itself. When you LINK to an image, not only does Access grab the image data, but also a lot of extra crap so it knows how to deal with displaying/processing the image itself. Linking is generally WORSE than embedding. Odd, huh?

I wrote an post about this in my Blog a few years ago: http://599cd.com/XT3860

[VirgilMachine]

Thank you!

You answered my question and offered a solution. Since I have Access 2010, I will try the control source process when I get to it, and post back here with results.

I watched the video, and it's helpful. Sounds like a winner.

[VirgilMachine]

Richard, I tried to follow your tutorial. Here's what I did:

• Deleted the column with the OLE field
• inserted a new text column with the path to an image
• created 2 rows in the table and put the path to different images in the new column

Now I tried different approaches, none of which worked.

First, I tried essentially what went on in the tutorial (I think):

• deleted the current image field in the form and replaced it by using the "insert image" control in desing view
• set control source to column containing thje path
• close and reopen form
• nothing appears (not even an empty form field)

Next,

• tried gthe same process using a text field instead of "insert image"
• it displays the contents of the field (C:\blahblah)

Next,

• I tried using the existing form field, which referred to the table column formerly defined as OLE...using "control source"
• displays nothing as in the 1st option

I must be doing something wrong. Do you see what that is?

Thanks for your help.

P.S. The existing form has the name of the OLE field in "control source." I tried using it as is with the new table and delete/add add--neither worked.

[VirgilMachine]

OK, so I made a dumb mistake (I thought so--my steps were correct, but there is still the nagging requirement to spell the pathname correctly).

For anyone watching, the steps are:

1. Delete the existing image field from the form
2. Delete the OLE column from the table
3. Create a new text column containing the path to the image specific to each row (d:\blahblah or \\server\blahblah)
4. create an image field on the form using "insert image"--pick any image
5. update "control source" in the form fileld properties to point to the column in the table that contains the path to the images

The first two steps are particular to my situation: I had a from displaying an OLE field in a table that contained an embedded image. This is the fix for me. The rest is the simple way to display images without a massively large database.

I will not mark this "solved" until I get to my full database on the network tomorrow, so I can report the changes in filesize.

Richard, thank you for taking the time to help with this, and for answering the question that I asked (as opposed to answering something else).

[VirgilMachine]

After deleting the OLE column (embedded images), then adding a new text column, and updating the new text column with the path to the image, the filesize of the table db went from 77MB to 736KB (10% of the size).

I then updated the form as described above (deleted the existing field that pointed to the OLE column, inserted an image in it's place, and updated the control source property to point to the new db column with the path info in it.

Everything works as before (including some slow response time due to network issues). I'm happy. This method is a little more labour intensive and not user friendly, but it works.

Regarding the title of this thread...the link vs. imbed thing is counter intuitive, as Richard reports above.