Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access insert into Attachment field file from Path

Posted on 2013-12-11
4
Medium Priority
?
6,502 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 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 48

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

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…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 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