Solved Thumbnail attachment in database appears in table but not in form

[CindyKredo]

I have a database which is linked out to a backend that has thumbnail attachments in it. Opening the linked table allows me to view the thumbnail attachments, no problem. The thumbnails were all imported from jpg files, and one of the jpg file names is "Kingfisher with Treat.jpg" A subform in the database contains only the thumbnail attachment with the primary key, and this form is linked by the primary key on a main form. When the user is on that main form, the thumbnail appears in the subform. This database has about 1,000 images in it (just in the attached table - I know that photo attachments aren't a good idea as a general rule) and all of them except for this ONE attachment work fine. At first I thought it was a problem with the jpg - but it turns out the actual name of the file seems to be the culprit! It is ONLY on the linked subform that the image doesn't appear - the image opens just fine directly from the table and/or from the subform. If I make a copy of that jpg and name it something else it appears just fine as long as I do not name it either Kingfisher with Treat or Kingfisher with Treats. Other variations using all three of those words work fine. Any thoughts?

 

[arnelgp]

maybe the primary (foreighn?) key does not match the main table.
what is the datatype of the primary key, you should use numeric not string (text).

 

[CindyKredo]

It matches. Not ideal that the primary key is a text string (photo name), but we have deleted the record and recreated it, rekeying the primary and foreign key entries, still with same issue. Later today I will see what happens if I add a numeric key and use that as the link, but even if that works, the fact that the actual text value is preventing the link seems really strange!

 

[GPGeorge]

I have a database which is linked out to a backend that has thumbnail attachments in it. Opening the linked table allows me to view the thumbnail attachments, no problem. The thumbnails were all imported from jpg files, and one of the jpg file names is "Kingfisher with Treat.jpg" A subform in the database contains only the thumbnail attachment with the primary key, and this form is linked by the primary key on a main form. When the user is on that main form, the thumbnail appears in the subform. This database has about 1,000 images in it (just in the attached table - I know that photo attachments aren't a good idea as a general rule) and all of them except for this ONE attachment work fine. At first I thought it was a problem with the jpg - but it turns out the actual name of the file seems to be the culprit! It is ONLY on the linked subform that the image doesn't appear - the image opens just fine directly from the table and/or from the subform. If I make a copy of that jpg and name it something else it appears just fine as long as I do not name it either Kingfisher with Treat or Kingfisher with Treats. Other variations using all three of those words work fine. Any thoughts?

Rename it and be done with it?

Seriously, if the problem were that multiple jpgs fail to display properly, it might be worth investing time in resolving the issue. One name that has something about it that doesn't suit Access? Nah, change it and move on.

Instead of "Kingfisher with Treat", maybe try "KingfisherwithTreat", for example.

 

[CindyKredo]

I modified the table with the photo title so that the primary key is an autonumber instead of the photo name. Added the foreign key to the thumbnail table, and changed the master/child fields on the form/subform to use the auto number primary key instead of the former string primary key. As suspected, this "resolves" the problem, but it still leaves a mystery why the subform is unable to find the link off a string field that is properly set up as a primary/foreign key! There is some sort of black magic in the word phrase Kingfisher with Treat!!! If anyone has any thoughts on this, I'd love to hear them. Thanks!

 

[theDBguy]

I modified the table with the photo title so that the primary key is an autonumber instead of the photo name. Added the foreign key to the thumbnail table, and changed the master/child fields on the form/subform to use the auto number primary key instead of the former string primary key. As suspected, this "resolves" the problem, but it still leaves a mystery why the subform is unable to find the link off a string field that is properly set up as a primary/foreign key! There is some sort of black magic in the word phrase Kingfisher with Treat!!! If anyone has any thoughts on this, I'd love to hear them. Thanks!

Are you able to post a sample db to demo the problem?

 

[Pat Hartman]

Parts of Access seem to operate on old filename rules. Sometimes, its a problem with file or path length. Sometimes its file extension.

 

[CindyKredo]

The photo is imported as an attachment, with a file name that is shorter than many, and all have a jpg extension, so it wouldn't be an issue with the filename rules. The database is huge, but maybe I can create a scaled down version with just a dummy main form and subform to recreate the problem and post it.... will do! And, for what it's worth, I've been delighted with this database after struggling with whether to use an attachment field in the first place. Putting the thumbnails in a separately linked back end has worked really well, with the database in use for a couple years now! (Saying this to be clear that it isn't a file link that is being used to bring up the photos!)

 

[CindyKredo]

Rename it and be done with it?

Seriously, if the problem were that multiple jpgs fail to display properly, it might be worth investing time in resolving the issue. One name that has something about it that doesn't suit Access? Nah, change it and move on.

Instead of "Kingfisher with Treat", maybe try "KingfisherwithTreat", for example.

If only it were so simple lol.... the name gets exported into other places and has to match the way it was done in the first place.... BUT, yes, I DO have a solution by changing the main form/subform link to use a different field - but at this point it's just a puzzling thing that has my curiosity piqued!

 

[CindyKredo]

When I put together the sample mini database to display the problem, I found the cause. Ridiculously silly misspelling that escaped us and that even managed to get rekeyed with the same typo, good grief. Sorry to clutter the boards with crazy user error, but a sigh of relief that things aren't as crazy as they seemed. Thanks for the responses.

 

[Gasman]

One good reason to use surrogate keys?
I have long descriptions of foods in my diabetes database, but just use the ID from tblFoods.

Then any misspelling does not matter.

 

[CindyKredo]

So true!

 

[GPGeorge]

If only it were so simple lol.... the name gets exported into other places and has to match the way it was done in the first place.... BUT, yes, I DO have a solution by changing the main form/subform link to use a different field - but at this point it's just a puzzling thing that has my curiosity piqued!

When I put together the sample mini database to display the problem, I found the cause. Ridiculously silly misspelling that escaped us and that even managed to get rekeyed with the same typo, good grief. Sorry to clutter the boards with crazy user error, but a sigh of relief that things aren't as crazy as they seemed. Thanks for the responses.

So, does that mean my suggestion to change the spelling wasn't totally off target?

 

[arnelgp]

If you already considered a Numeric PK/FK then your at a good direction.
Numeric fields are faster to index and consume less space.

Also when comparing, numeric field will only compare their values,
unlike on text it will compare character per character.

Text fields are larger than numeric fields, so their indexes take
more storage space and may slow down insert/update operations.

 

[CindyKredo]

Yes, I will be changing the structure to use numeric keys. Thanks all. I've been building databases for something like 30 years and yes, I should have known better .

 

[Mark_]

Please note, you may hit a similar issue in the future. Access does not always like non-PNG files. Database I'm currently keeping up only has about 500ish images that it displays. Found out early that if they were not PNGs, some would not show on reports.