Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access insert into Attachment field file from Path

Posted on 2013-12-11
4
Medium Priority
?
6,689 Views
1 Endorsement
Last Modified: 2013-12-14
Hi Experts,

I have a text field i a form. In that text field there is a full path to a file. How do i use this path to insert that file into a attachment field i a Table using vba?
1
Comment
Question by:DCRAPACCESS
  • 2
4 Comments
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 2000 total points
ID: 39710807
Working with the Attachment data type in code requires using recordsets.


Code Example 7.1: Programmatically Adding a New File to an Attachment Field
On Error GoTo Err_AddImage

Dim db As DAO.Database
Dim rsParent As DAO.Recordset2
Dim rsChild As DAO.Recordset2

Set db = CurrentDb
Set rsParent = Me.Recordset

rsParent.Edit

Set rsChild = rsParent.Fields("AttachmentTest").Value

rsChild.AddNew
rsChild.Fields("FileData").LoadFromFile ("c:\Sunset.jpg")

rsChild.Update
rsParent.Update

Exit_AddImage:

Set rsChild = Nothing
Set rsParent = Nothing
Exit Sub

Err_AddImage:

If Err = 3820 Then
MsgBox ("File already part of the multi-valued field!")
Resume Next

Else
MsgBox "Some Other Error occured!", Err.Number, Err.Description
Resume Exit_AddImage

End If

Open in new window

From this tutorial: Working with the Attachment DataType

You would change these two lines:

Set rsChild = rsParent.Fields("AttachmentTest").Value   

Open in new window

rsChild.Fields("FileData").LoadFromFile ("c:\Sunset.jpg")

Open in new window


to reference the attachment field and the text box with the file path.

Example:

' change MyAttachmentFiled to your actual field name (not the control name)
Set rsChild = rsParent.Fields("MyAttachmentFiled").Value

Open in new window

' change txtFullFilePath to be your actual text box control name
rsChild.Fields("FileData").LoadFromFile (Me.txtFullFilePath)

Open in new window

0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 39711257
My first question would be:  Why do you want to do this?  

Although recent versions of Access do this without too much bloating of the database, you are still limited in the size of your datafile, and attachments, whether they be .doc, .pdf, .ppt, .xls, .jpg can rapidly increase that size and overwhelm your database.

Personally, I prefer to use the technique you appear to be using now, storing the path to the data in your database and then using a hyperlink method to open that file when needed.  This has potential down-sides as well, in that unwitting personnel can delete the file or the folder and "break" the relationship between the pointers in your table and the actual location of the file.  However, you can take steps to make the file location sufficiently well protected (and backed up) to avoid or mitigate this problem.
0
 
LVL 21
ID: 39712127
If you are storing a few images, like a Logo, to print on reports then the Attachment data type can be very useful.

If you are want to store lots of documents (PDF, DOC/DOCX, etc)  then I agree with fyed that this may not be the best method.

It all depends on what you are wanting to do which is the best approach.
0
 

Author Closing Comment

by:DCRAPACCESS
ID: 39719001
Worked. Thanks
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question