Solved

Access insert into Attachment field file from Path

Posted on 2013-12-11
4
5,210 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 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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now