Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-06-2019, 09:36 AM   #1
NancyR
Newly Registered User
 
Join Date: Oct 2019
Posts: 10
Thanks: 1
Thanked 0 Times in 0 Posts
NancyR is on a distinguished road
Appending Table with a Picture

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?

NancyR is offline   Reply With Quote
Old 10-06-2019, 09:45 AM   #2
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,018
Thanks: 114
Thanked 3,014 Times in 2,741 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Appending Table with a Picture

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.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns Ė the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
Old 10-06-2019, 09:47 AM   #3
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,085
Thanks: 10
Thanked 219 Times in 207 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Appending Table with a Picture

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.

__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 10-06-2019, 10:45 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,895
Thanks: 57
Thanked 1,289 Times in 1,270 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Appending Table with a Picture

Hi Nancy. If you're looking for a workaround, then maybe you could take a look at this demo. Hope it helps...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-06-2019, 10:48 AM   #5
NancyR
Newly Registered User
 
Join Date: Oct 2019
Posts: 10
Thanks: 1
Thanked 0 Times in 0 Posts
NancyR is on a distinguished road
Re: Appending Table with a Picture

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.
NancyR is offline   Reply With Quote
Old 10-06-2019, 11:01 AM   #6
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Appending Table with a Picture

what is the fieldtype, OLE or Attachment?
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 10-06-2019, 11:09 AM   #7
NancyR
Newly Registered User
 
Join Date: Oct 2019
Posts: 10
Thanks: 1
Thanked 0 Times in 0 Posts
NancyR is on a distinguished road
Re: Appending Table with a Picture

Quote:
Originally Posted by arnelgp View Post
what is the fieldtype, OLE or Attachment?
Attachment.

NancyR is offline   Reply With Quote
Old 10-06-2019, 11:29 AM   #8
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,425
Thanks: 0
Thanked 568 Times in 564 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Appending Table with a Picture

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] & "#"
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 10-06-2019 at 11:40 AM.
June7 is offline   Reply With Quote
Old 10-06-2019, 12:27 PM   #9
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,042
Thanks: 20
Thanked 382 Times in 375 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Appending Table with a Picture

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.
Mark_ is offline   Reply With Quote
Old 10-06-2019, 12:49 PM   #10
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,895
Thanks: 57
Thanked 1,289 Times in 1,270 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Appending Table with a Picture

Quote:
Originally Posted by Mark_ View Post
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.
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...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-06-2019, 01:40 PM   #11
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,042
Thanks: 20
Thanked 382 Times in 375 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Appending Table with a Picture

theDBGuy,

Didn't know that. Had run into an issue with it a while ago and had kinda sworn them off...
Mark_ is offline   Reply With Quote
Old 10-06-2019, 01:47 PM   #12
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,895
Thanks: 57
Thanked 1,289 Times in 1,270 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Appending Table with a Picture

Quote:
Originally Posted by Mark_ View Post
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!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-06-2019, 01:54 PM   #13
NancyR
Newly Registered User
 
Join Date: Oct 2019
Posts: 10
Thanks: 1
Thanked 0 Times in 0 Posts
NancyR is on a distinguished road
Re: Appending Table with a Picture

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?
NancyR is offline   Reply With Quote
Old 10-06-2019, 02:09 PM   #14
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,018
Thanks: 114
Thanked 3,014 Times in 2,741 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Appending Table with a Picture

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/m...lds/4594468763
Strongly advise you to get rid of them
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns Ė the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld

Last edited by isladogs; 10-06-2019 at 02:13 PM. Reason: Added link
isladogs is offline   Reply With Quote
Old 10-06-2019, 02:57 PM   #15
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,085
Thanks: 10
Thanked 219 Times in 207 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Appending Table with a Picture

We've been less help than normal because it's not something we'd find ourselves needing to do. Having said that, I Googled and oddly enough, it appears to be doable via code. The code samples or links in this thread might be missing or broken, but it's a start
https://access-programmers.co.uk/for...d.php?t=246743


Then there is (read down to find posted code)

https://social.msdn.microsoft.com/Fo...orum=accessdev

Probably something you'll have to Google

__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)

Last edited by Micron; 10-06-2019 at 02:59 PM. Reason: added link
Micron is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Appending From One Table to Another Table to Specific Data skilche1 Queries 9 01-17-2015 11:59 AM
Importing Data to a Table and Appending to Another Table Hulamom General 3 08-24-2014 09:48 AM
Appending to table WLCummings Tables 3 10-09-2012 12:43 PM
How To Copy Table Appending Date to Destination Table Name whimbrel Modules & VBA 6 09-03-2007 06:06 AM
Appending to a table superrob5 Queries 3 12-20-2003 12:03 PM




All times are GMT -8. The time now is 06:36 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World