Append Query containing Attachment fields

Question

I have a Access 2007 database where as part of an update process I need to copy a table from an external database into this database. The tables contain a number of fields with one attachment field. The attachment field contains photos.

Answer 1

Thank you, I have abandoned the whole idea of trying to manipulate attachments programmatically.

Answer 2

Thank you Nathan

I have found that I can still run the Append query if I do not use the "*" function but list all fields except the attachment. I can then manually copy the attachment column (CTRL C) and paste it (CTRL V) into the updated table. This is not the optimum solution, however it gets me by until I can find a better one.

The article refers to using an SQL query (below) to do an Append function but I don't understand how the Values and Where statements work.

INSERT INTO Issues ( AssignedTo.[Value] )

VALUES (6)

WHERE ID = 10;

Do you know which database the article refers to as this may help in understanding how the sintax works.

Thank you

Macka

Answer 3

Hi Macka,

Unfortunately, you cannot use an append query that references a table that contains a multivalued field.  The article below discusses this.

Using multivalued fields in a query - http://office.microsoft.com/en-us/access-help/using-multivalued-fields-in-queries-HA010149297.aspx

If you aren’t concerned with the multi-valued field, you could try excluding it from your query to see if this helps.

Best Regards,

Nathan Ost

Microsoft Online Community Support

Answer 4

Hi Nathan

I had a user initiated update process that used an append query to import data from an external table which worked perfectly, however the database containing the new data has been changed and now contains an attachment field. I have updated the query but when I run it I get an error message that says "An INSERT INTO query cannot contain a multi-valued field.". On searching Help I find commands that import/export a file using SQL but no reference as to how to use an append query.

Thank you

Macka

Answer 5

Hi Macka52,

Are you not able to simply import the table from the external database into this database?  If not, what problem are you running into when you try and do this?  Or if you are trying to do something else, could you provide a few more details on what you are trying to accomplish and what is failing.

Best Regards,

Nathan Ost

Microsoft Online Community Support