Attachment data type and VBA

Hello,

I'm trying to create one record from another one. One field is attachment data type. I just create a normal query (INSERT INTO) but it' not working (impossible with multiple value field). Do you have a suggestion about inserting or updating an attachment datatype field (with or without VBA)?

Thank you a lot, I really appreciate.

Interesting Question - I hadnt thought about this
Did you do a
insert into ......... select ..........
query?

I belive that this field is actually a hidden table that gets automatically created - I wonder if you have to address this table directly

Can I ask if you do end up finding the answer somewhere else please post the solution here

Yes, it's just a query like

INSERT INTO tblFichesEspacesClos ( PhotoGenerale )
SELECT tblFichesEspacesBkup.PhotoGenerale
FROM tblFichesEspacesClos
WHERE (((tblFichesEspacesBkup.NoFicheEspaceClos)=77));

PhotoGenerale is an Attachment data type (and the key field is AutoNumber)

It's not working. So, I wonder if I can do it using VBA (like OpenRecordSet, CurrentDb.QueryDefs, etc).

Yes if I got an answer about this amazing thing I will post the information here.

Thank you

I find a part of the solution with the help of Palvo (http://sites.google.com/site/msaccesscode/). It's Loop within the main Loop,

This is my code to copy a record with a attachment data type :

Dim rstFrom As ADODB.Recordset
Dim rstTo As ADODB.Recordset
Dim rstMVF As ADODB.Recordset
Dim rstMVT As ADODB.Recordset

Set rstFrom = New ADODB.Recordset
rstFrom.Open "select * from a where Notransaction=1", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Set rstTo = New ADODB.Recordset
rstTpen "b", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Do While rstFrom.EOF = False
rstTo.AddNew
rstTo!Description = rstFrom![Description]
Set rstMVF = rstFrom!Photo.Value ' Photo is the attachment data type
Set rstMVT = rstTo!Photo.Value
' Copy all the attachment in the field Photo (attachment datatype)
Do While rstMVF.EOF = False
rstMVT.AddNew
rstMVT!FileData = rstMVF!FileData
rstMVT!FileFlags = rstMVF!FileFlags
rstMVT!FileName = rstMVF!FileName
rstMVT!FileTimeStamp = rstMVF!FileTimeStamp
rstMVT!FileType = rstMVF!FileType
rstMVT!FileURL = rstMVF!FileURL
rstMVT.Update
rstMVF.MoveNext
Loop
Set rstMVF = Nothing
Set rstMVT = Nothing
rstTo.Update
rstFrom.MoveNext
Loop
rstFrom.Close
rstTo.Close

I havent tested it but thanks for the post

Hi LouisLouis,

In your code it looks like your are trying to copy the attachments in table "a" to table "b". I actually want to copy the attachment of the current record to a new record. Do you know how I would modify the code? I am very unfamiliar with ADO so I woulld appreciate any help that you can give me.

-Mark-