Solved

Access insert into Attachment field file from Path

Posted on 2013-12-11
4
5,367 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

914 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

18 Experts available now in Live!

Get 1:1 Help Now