Appending Table with a Picture (1 Viewer)

NancyR

Registered User.
Local time
Today, 09:17
Joined
Oct 6, 2019
Messages
10
I am trying to combine two tables that are very similar, I used Append.

It gave me the "An Append query cannot contain a multi-valued field" error message.

Through examination, I figured out that the graphic field is what Access considers multi-valued. It won't append or update that picture!

Since these are databases of designs, the picture is vital.

Is there a work-around? Am I doing something simple, incorrectly? :(
 

isladogs

MVP / VIP
Local time
Today, 16:17
Joined
Jan 14, 2017
Messages
18,186
Look at the table design for that field. What is the datatype?
If its an attachment field or a text field allowing multiple vales (multivalued field), most developers would advise you to modify your approach.
 

Micron

AWF VIP
Local time
Today, 12:17
Joined
Oct 20, 2018
Messages
3,476
You shouldn't store attachments in Access tables - too many issues and the file size can quickly reach the limit. Better to have a text field that stores the file path and use something like Application.FollowHyperlink method - without making the field a hyperlink field.


Since I don't do this, I can't be sure but the message indicates something else to me. Are you sure that it pertains to the attachment field? Did you remove only this field and then you could append to the table? I would have expected the message to be more like 'cannot append to a field of complex data type' or something, which would include attachment fields as well as multi value fields. Those are typically something else, and are another thing you should avoid.
 

NancyR

Registered User.
Local time
Today, 09:17
Joined
Oct 6, 2019
Messages
10
Did you remove only this field and then you could append to the table?

Yes, the rest of the fields worked as expected.

Better to have a text field that stores the file path and use something like Application.FollowHyperlink method - without making the field a hyperlink field.

I actually tried this at first (years ago). My issue was a relative reference to the linked location vice an absolute one. If I want to copy my DB to a thumb drive and move it to my laptop, I couldn't figure out how to make the hyperlink be to a subdirectory that moved with it (relative ref), it was always trying to look over there ---> (absolute ref), where I copied it from.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:17
Joined
May 7, 2009
Messages
19,169
what is the fieldtype, OLE or Attachment?
 

June7

AWF VIP
Local time
Today, 08:17
Joined
Mar 9, 2014
Messages
5,423
If images folder is located in same folder as database, relative pathing in Image control ControlSource property could be like:

=CurrentProject.Path & "\ImagesFolder\" & [ImageNameField]

If images are PDF, use that pathing in FollowHyperlink

Or construct 3-part hyperlink string in a textbox set as hyperlink:

="#" & CurrentProject.Path & "\ImagesFolder\" & [ImageNameField] & "#"
 
Last edited:

Mark_

Longboard on the internet
Local time
Today, 09:17
Joined
Sep 12, 2017
Messages
2,111
NancyR,

One of the other reasons most of us recommend keeping attachments as a path to the file is because of how difficult it is to update/keep the current attachment straight when you try to edit it.

If you save the path, when you update your image/document/what ever, it is automatically reflected in your database. If you save the attachment you have to write it out to disk, update it, then re-import it followed by deleting the saved copy. Miss or mess up one of those and you have different versions of your attachment in your program and located elsewhere. Honestly for me its too much bother to do all that when a simple path avoids so much work for me. :D:D:D
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:17
Joined
Oct 29, 2018
Messages
21,358
NancyR,

One of the other reasons most of us recommend keeping attachments as a path to the file is because of how difficult it is to update/keep the current attachment straight when you try to edit it.

If you save the path, when you update your image/document/what ever, it is automatically reflected in your database. If you save the attachment you have to write it out to disk, update it, then re-import it followed by deleting the saved copy. Miss or mess up one of those and you have different versions of your attachment in your program and located elsewhere. Honestly for me its too much bother to do all that when a simple path avoids so much work for me. :D:D:D
Hi Mark. I'm not saying I disagree but just wanted to mention that all that "work" (saving to disk, update, and reimport) is automatically done for you by Access when using an Attachment field. For instance, if you embed a Word doc as an Attachment field, double-clicking the attachment (or selecting Open) will make Access pull the attachment from the table, place it in a Temp folder, open it for editing, and when the user is finished with the changes, closing the Word doc will automatically reimport the new file to replace the old attachment (after user confirmation). Just my 2 cents...
 

Mark_

Longboard on the internet
Local time
Today, 09:17
Joined
Sep 12, 2017
Messages
2,111
theDBGuy,

Didn't know that. Had run into an issue with it a while ago and had kinda sworn them off...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:17
Joined
Oct 29, 2018
Messages
21,358
theDBGuy,

Didn't know that. Had run into an issue with it a while ago and had kinda sworn them off...
Can't say I blame you. Cheers!
 

NancyR

Registered User.
Local time
Today, 09:17
Joined
Oct 6, 2019
Messages
10
Gentlemen, much as I appreciate the info/suggestions, no one has actually answered the question. Can I move/append/copy/update records containing an attachment from one table to another?
 

isladogs

MVP / VIP
Local time
Today, 16:17
Joined
Jan 14, 2017
Messages
18,186
I believe the answer is No (or at least not directly) for both attachment and other MVF fields.
These depend on data stored in hidden system tables which cannot be directly edited.
There are many reasons not to use attachment / MVF fields.
See this article on my website: http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763
Strongly advise you to get rid of them
 
Last edited:

Micron

AWF VIP
Local time
Today, 12:17
Joined
Oct 20, 2018
Messages
3,476
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 09:17
Joined
Oct 29, 2018
Messages
21,358
Gentlemen, much as I appreciate the info/suggestions, no one has actually answered the question. Can I move/append/copy/update records containing an attachment from one table to another?
What? Did you not see my post earlier? Just curious...


It was a demo showing you exactly how it's done.
 

NancyR

Registered User.
Local time
Today, 09:17
Joined
Oct 6, 2019
Messages
10
What? Did you not see my post earlier? Just curious... It was a demo showing you exactly how it's done.


No, it was a demo showing it CAN be done. It wants me to buy your $54 book to see HOW to do it. Thanks for the info.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:17
Joined
Oct 29, 2018
Messages
21,358
What? Did you not see my post earlier? Just curious... It was a demo showing you exactly how it's done.


No, it was a demo showing it CAN be done. It wants me to buy your $54 book to see HOW to do it. Thanks for the info.
I'm sorry. Where did it say you have to buy the book. I'll have to fix that. You just download the demo and that's it. Nothing to buy at all. What I was trying to say on that page is the demo was based on, or came from, the book. That's all. Sorry for the confusion. Just to repeat, the demo is free to everyone, and it contains everything you would need to make it work (without the book).


Here's a small quoted description from that page:
This demo illustrates how to use VBA code to copy or transfer an MVF or Attachment field from one table into another. This approach is necessary because using a regular Append or Make-Table query does not work with MVF and Attachment fields.
 

NancyR

Registered User.
Local time
Today, 09:17
Joined
Oct 6, 2019
Messages
10
NancyR,

One of the other reasons most of us recommend keeping attachments as a path to the file is because of how difficult it is to update/keep the current attachment straight when you try to edit it.

I appreciate that, but isn't applicable. My DB is a catalogue of designs. The attachment is a picture of that design and doesn't change. This isn't a power database, it's simple and only for me, but needs to be portable to my laptop.

I created a blank DB and pulled both main tables and their look-up tables into it. Then started trying to combine them into one main table. So both mains (with attachments) and the combined table (with half the pics repeated) are in there. With all that in the in-progress rebuild, it's just over half a meg. When/if I can pull the other 600 pics into the new main, and the two old tables deleted, I still won't be in any size danger. :eek:

I see in the 10 Commandments of Access that I should now abhor look-up tables. In my computer science classes (admittedly years ago), look-up tables were THE method to refrain from mis-typing, mis-spelling, etc., data in a field that's repeated over and over (like the source of one of my designs). I have over 1200 designs but only 51 sources and 12 formats and 27 locations (vital for finding that design when I want it!). Look-up tables!

Back on topic, why does access give us a data type (attachments) that is so different in routine use? :mad: That is rhetorical, I don't expect you to answer. :p Thanks for sharing!
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:17
Joined
Sep 21, 2011
Messages
14,044
I'm sorry. Where did it say you have to buy the book. I'll have to fix that.

It does not theDBguy, it merely mentions it and the download is clearly shown on the left.
The code for the demo is also available in the forms.:confused:
 

Users who are viewing this thread

Top Bottom