New Access User needs help with Connection to different Database frm Current database

[June7]

The code you posted was designed for standard multi-value field. I conclude this because of the .Value property. This is not a property of the items in an Attachment field so this code will not work as is. The properties of Attachment datatype items are FileData, FileName, FileType. I tried adapting the code but no success.

Here is a MS article about multi-value fields http://office.microsoft.com/en-us/ac...492971033.aspx
It does not directly address the Attachment datatype but can see an example in one of the images.

You might consider the following which are possible:


Import the table with attachment
Copy/Paste the attachment field from one table to another - even between two open database files

[ped]

June7, thank you soo much for helping...
I really need this to work but still wont work....
I'll try to refer to the link and see if that helps...


I'll update.

Thanks again
Pedie

[June7]

That link won't help with code. It might give a better understanding of what multi-value fields are.

I am starting to think that the only way to programmatically transfer Attachment items from one table to another is to:
1. Save the files from Table1 to a folder
2. Add the files from folder to Table2
3. Delete the files from the folder

This article has code for saving and adding attachments http://blogs.office.com/b/microsoft-...cess-2007.aspx

I do find it odd that the sample code uses .Value property which seems to contradict what I thought about the Attachment field type not having this property.

Edit: FOUND IT!!! http://www.access-programmers.co.uk/...d.php?t=180349
This uses ADO recordset so need to set Reference in the VBA editor to Microsoft ActiveX Data Objects 2.8 Library. Tables have to be in same project. I give up trying to programmatically connect to other database. I set a link to the other table then this code works on the two tables. Change name Table2 to the linked table name.

Sub TransferAttachments()
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 "Table1", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Set rstTo = New ADODB.Recordset
rstTo.Open "Table2", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Do While rstFrom.EOF = False
    rstTo.AddNew
    Set rstMVF = rstFrom!Attach.Value 'Attach is the attachment datatype field
    Set rstMVT = rstTo!Attach.Value
    ' Copy all the attachments in the field Attach (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
End Sub

[ped]

June7, you rock!
Perfect perfect! At least if we can do this within same project, we'll soon figure out the other way too to transfer it to different database...

this part of the code...
what is FileData, FileFlags, FileTimeStamp, FileURL?
Dooes all this make one file/attachment?

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

And again, thank you very very Much....!

[June7]

Those are properties of the file items in the Attachment field. Build a query and in the query designer for that field you will see FileName, FileData, FileType. I don't know if the others are required for the item to add properly, they were in the code I found and did not test without. Actually, more accurately, those are probably field names from the hidden table that stores the info for each attachment item.

I tried to make connection to other database just as I have many times in other procedures but it would not work. The link accomplishes the same, maybe better.

[ped]

June7, thanks again for helping...
I'll try to also do this myself and let you know if things comes to my favour!

Regards
Pedie