Solved

Access insert into Attachment field file from Path

Posted on 2013-12-11
4
5,843 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 500 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 47

Expert Comment

by:Dale Fye (Access MVP)
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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 …

740 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