How to Manipulate Attachment Field in Access/VBA

[hvsummer]

I've decided to do an automaton, and it'll be whole new question that never ask by anyone else in internet.

Currently, My working flows is looklike this:

Import data to Access --> calculate everything --> Exit Access, Open Excel --> refresh all data and use Automaton outlook to send email (vba excel)

now I want to change it into this:

Import data to access --> calculate everything then open excel file that attached in table1 (inside attachment field), refresh all data (that connected directly to Access itself already) then save and close excel (save change into attachment field)
--> on Form, choose group to report, access open excel and run macro that prepaired in Excel file (excel still invisible), calling outlook automaton
--> new email appear, close excel file.

if you guy could advice any better method, I'll follow the light. Right now, I want to know how to do my new idea that just pop up in my mind :D

sothat, what vba code to manipulate attachment field ?

how to open excel attachment inside vba, run excel macro ?
how to close excel attachment and save it back with any modify into attachment field ? :D

Edit 1:
my current code:

Public Function MAttachment(RefreshAll As Boolean, Optional InputField As String)
 
Dim Rcs As Recordset2
Dim Rcs1 As Recordset2
Dim fld As DAO.Field2
Dim FullFileName As String
FullFileName = "D:\(REPORT) Tracking Volume and Price WholeSale LRB.xlsb"
 
Set Rcs = CurrentDb.OpenRecordset("SELECT * from TblExcel")
Set fld = Rcs("AttachExcel")
Set Rcs1 = fld.Value
 
    If FileExists(FullFileName) Then
        Kill FullFileName
    End If
 
    Rcs1("FileData").SaveToFile "D:\"
 
If RefreshAll = True Then
 
    If RefreshExcel(FullFileName) = True Then
        Rcs1("FileData").Delete
        Rcs1("FileData").AddNew
        Rcs1("FileData").LoadFromFile FullFileName
        Rcs1.Update
    End If
end if
end function
 

[zmbd]

I absolutely advise against the attachment field at all costs.
home > topics > microsoft access / vba > questions > table capacity> Post#3 Attachments will eat thru your available file space very quickly.

First I would rethink what is it in Excel that you need that cannot be done in Access? You mention doing the "calculations" in Access so it begs the question.

Second, You mention that you are already using some automation in the Excel file to handle Outlook - why not change your work flow a tad:
Import to Access > Calculate > Automation to open instance of Excel Using the file and refresh > Send your email > close instance of Excel. IMHO much better than attempting anything in the attachment field.

This is the basics that MS as about Attaching files and graphics to the records in your database.
1) Attachments in the database will still require that Excel be installed on the user's pc
2) When you open the file, it opens in a temporary location
3) Any changes have to have interaction back in Access to confirm the change.
4) I think you'll run into a lot of issues down the road using this method. The one that comes to mind is what happens if something corrupts the Excel file while it is open.

IMHO, you'd be better of with Automating the Excel instance.

Ahh... but you have an issue with the Excel file wanting to open your linked to Access tables in exclusive state: https://bytes.com/topic/access/answe...-closing-accdb You have the mode as share deny... as you've discovered, change that to: "Mode=Share Deny None" which should allow things to play together nicely.

(I've another thought about using MS Query instead of the linked tables; however, I'll leave that discussion in the afore mentioned related thread)


>> If you insist on the Attachment field we'll go from there.

[hvsummer]

@ZMBD: thank you, I've done attaching my report .xlsb into access attachment field, only 1 file so we don't have to worry about capacity.

your router's very clever
Access > Calculate > Automation to open instance of Excel Using the file and refresh > Send your email > close instance of Excel.
I was thinking about this too, but the fact that I'm lazy, so I found the Lazier way to do this.

I'll explain my working flow that I have done 4hours ago.

My Ex-workflow (not ex-girl friend) that I used to do everyday is like this:
Data raw --> import into access, calculate, prepair result, close access --> open excel, refresh all (connection), send email with my module (this module have 3 step, filter everything with my condition, save, open and copy all-ranges I choose to Outlook, even attachment itself to email - automaton part).

ok, now, my lazy level increase to new level. I ask my self, why don't we code access to do everything ?
at that moment, there arre 2 options pop-up in my mind like popcorn (I feel like want to eat some)

(this part is the same for 2 options)
attach the report.xlsb into 1 new table that having attachment field, to make sure whether I have or not that report excel at the moment I open access, I will never lose it.
but the problem come out, if I attachment this into access, what is the fastest way, least code to do that ?
I go sulfing internet and they use envinr() to get the temp folder and concenater with file name :D well, could work but more problem will appear.
then I findout DAO.recordset2 type can be use to load/save/delete attachments field. after 1hours seaching, I tho I got my lifesaver.
I sticked to it and wrote somecode to craft everything together.

On this part, my function will save my report file from attachment field into "D:\" if that file does not exist.
next, it refresh all (connection), then save workbook, delete oldone, load my file back to attachment field.

(this part is the different)
Now I have 2 options to code automation outlook:

1/ rewrite everything with later binding automaton for outlook in access (or just copy from excel report file then tweak)

2/ make excel run macro inside it then close excel file, leave the outlook new email visible.

when it come to this, my lazy level suddenly increased 2 level at the same time.
As the result, I use

dim xlapp as object
set xlapp = createobject("excel.application")
'then with, set, bla bla
xlApp.run "function-name"
 
'end code
 

to finish this code part ==

now I have new working flow:

Data --> import/Calculate/Prepair + Open excel file from attachment/Refresh All/Save/Load back --> press 1 last button "Send Report" to open outlook email :D

please don't hate me when I told the shortened version of story from my Code battlefield.
I'm too lazy now :P :D

[zmbd]

My Ex-workflow (not ex-girl friend) that I used to do everyday is like this:
Data raw --> import into access, calculate, prepair result, close access --> open excel, refresh all (connection), send email with my module (this module have 3 step, filter everything with my condition, save, open and copy all-ranges I choose to Outlook, even attachment itself to email -
automaton part).

So why not send the information directly from access as a report? Acc2010 with addin can send the report, as formatted to a PDF file and Acc2013 does that with the built-in PDF generator.

[hvsummer]

@zmbd: the report template has been created within excel file. I decorated, and summary information.

Thank for the advice, I'll think bout it in another situation ^^

[mbizup]

Just seconding zmbd's advice *against* using attachment fields.

But to directly answer your question, Microsoft offers the LoadFromFile and SaveToFile methods to manipulate attachment fields:
https://msdn.microsoft.com/en-us/lib.../ff835669.aspx

Straight forward, right?

Note that there is no built-in method for *removing* attachments (eg: if you want to remove a file and replace it with an updated/different file)

The attachment field can contain multiple values for each record within your table. So to remove an attachment, you need to open a recordset within the 'FileData' field in order to delete the attachment (repeat for each record in your table you want to remove an attachment from).

So if your reason for using attachments stems from 'being lazy' (most programmers want to do things in the most efficient way possible), you are actually creating significantly more work for yourself by using attachments.

... another reason to avoid attachments, as almost globally recommended ;-)

[hvsummer]

I don't think I have more workload when using attachment field, because I only attach 1 file into 1 table content only 1 record.

and my code will be simple like this:

Dim Rcs As Recordset2
Dim Rcs1 As Recordset2
Dim fld As DAO.Field2
 
Set Rcs = CurrentDb.OpenRecordset("SELECT * from TblExcel")
Set fld = Rcs("AttachExcel")
Set Rcs1 = fld.Value
 
'Save attachment to D:\
    Rcs1("FileData").SaveToFile "D:\"
 
'process code here
 
'load back into table
    Rcs.Edit
    Rcs1.Delete
    Rcs1.AddNew
    Rcs1("FileData").LoadFromFile FullFileName
    Rcs1.Update
    Rcs.Update
 

I don't even have to loop, sothat, what part can bring me more workload ? ==

and you're not right this part "there is no built-in method for *removing* attachments". please look at my code, recordset.delete is the built in method to remove attachments. ==

I don't think microsoft stupid enough not to let people delete any recordset type in table.

[zmbd]

hv, what mbizup is saying, and I can only surmise that there is a language gap here, is that, without deleting the record, there is no really easy method to remove an attachment from the attachment field via VBA.

The method you are using to "delete" the attachment is using a cannon to kill a mosquito.

hv, you shouldn't be so ready to tell people that they are wrong, especially people that have been working in the industry for many years.

With that said, there is a way to delete the attachment; HOWEVER, this isn't as easy as the LoadFromFile and SaveToFile methods that mbizup has mentioned.

The following is aircode, it may have a few typos ;-)

Sub B965025_201512012330()
    Dim zDB As DAO.Database
    'from the MS lit, the v2 of the following has the functionality to work with the backstage where the MVF/LUF/Attachments hide
    Dim zRS As DAO.Recordset2
    Dim zRS_Attachemts As DAO.Recordset2
    Dim zFLD As DAO.Field2
    '
    'and a scratchpad
    Dim zFileToRemove As String
    Dim zSQL As String
    '
    Dim z911 As Long
    '
    On Error GoTo zErrorTrap
    '
    z911 = 0
    '
    'Ok, write the SQL to find the table with the attachment field one could add this as a passed in value etc...
    zSQL = "SELECT pk, somefield, attachmentfield" & _
        " FROM tbl_namehere" & _
        " WHERE ([pk]<100);"
    '
    Set zDB = CurrentDb
    Set zRS = zDB.OpenRecordset(Name:=zSQL, Type:=dbOpenDynaset)
    '
    'of course this would have to be the attachment name
    zFileToRemove = "Reference.txt"
    '
    'now loop thru every returned record and remove the attachment with the specified name. 
    If zRS.RecordCount Then
        zRS.MoveFirst
        Set zFLD = zRS("attachmentfield")
        '
        Do
            Set zRS_Attachemts = zFLD.Value
            If zRS_Attachemts.RecordCount Then
                zRS_Attachemts.MoveFirst
                z911 = 0
                Do
                    ' found this quite by accident, appears that the [FileName] is the backstage field used to store the attachment
                    If zRS_Attachemts("FileName") Like zFileToRemove Then zRS_Attachemts.Delete
                    zRS_Attachemts.MoveNext
                    z911 = z911 + 1
                    If z911 >= 1000 Then Err.Raise Number:=-2147220991, Source:="Attachment Loop", Description:="Loop fail safe at 1000"
                Loop Until zRS_Attachemts.EOF
                z911 = 0
            End If
            If Not zRS_Attachemts Is Nothing Then
                zRS_Attachemts.Close
                Set zRS_Attachemts = Nothing
            End If
            zRS.MoveNext
            z911 = z911 + 1
            If z911 >= 1000 Then Err.Raise Number:=-2147220992, Source:="Record Loop", Description:="Loop fail safe at 1000"
        Loop Until zRS.EOF
    Else
        Err.Raise Number:=-2147220993, Source:="Record Set", Description:="There are no records matching the specified criteria"
    End If
    '
zcleanup:
    If Not zRS_Attachemts Is Nothing Then
        zRS_Attachemts.Close
        Set zRS_Attachemts = Nothing
    End If
    If Not zRS Is Nothing Then
        zRS.Close
        Set zRS = Nothing
    End If
z911Exit:
    If Not zDB Is Nothing Then Set zDB = Nothing
    Exit Sub
zErrorTrap:
Zerrtrap:
    MsgBox Prompt:="ErrS: " & Err.Source & vbCrLf & _
        "ErrN: " & Err.Number & vbCrLf & _
        "ErrD: " & Err.Description, _
        Title:="Oh Bother an Error"
    If z911 <= 0 Then Resume z911Exit
    z911 = -1000
    Resume zcleanup
End Sub

[hvsummer]

@zmbd: thank you to notice, but I have to say I'm not ez to say someone wrong, I said he was not right.

your compare "using a cannon to kill a mosquito." make me laught too much...
but I can't find ezier way to remove a record without deleting it.
I could use bigger cannon to kill the whole mosquito's cave (I mean table).

btw, I don't understand what you mean " there is no really easy method to remove an attachment from the attachment field via VBA".
so the normal field like text or number we can remove it without delete :-? may be I don't understand this language gap :D

"With that said, there is a way to delete the attachment; HOWEVER, this isn't as easy as the LoadFromFile and SaveToFile methods that mbizup has mentioned."

I didn't say mbizup isn't "working in the industry for many years"

but in fact, his link don't have Delete method which mention on the rest link I can find on Google, so I have to say he's not right because he don't have enough information and conclude too soon.

Like this link show-up Save/Load/Delete attachment field
http://sourcedaddy.com/ms-access/wor...nt-fields.html
Recordset2 object (attachment field)
https://msdn.microsoft.com/en-us/lib.../ff197737.aspx

your code really great, traped almost every error could happen.

but if someone need to delete attachment, I think he will know not to place multi attachment in 1 row to make everything harder. just make table like
Table1 = {ID, attachment}
then each attachment have their ID, if you want to delete attachment, just delete that ID with delete query.

That is the better way to handle task "delete attachment".

I'm lazy and don't want to make thing harder, so I'll predict everything and find the laziest way to finish my task fastest possible :D

[mbizup]

hvsummer,

You misread my post...

<<
Note that there is no built-in method for *removing* attachments ...
So to remove an attachment, you need to open a recordset...>>

I never said there was *no* way of deleting attachments - just that there is no 'built-in' way such as LoadFromFile and SaveToFile... so you have to program your own via recordset code.

Even if you do only have one attachment per record as described, it is good practice to use a looping method as zmbd showed, to identify a specific attachment. That will make your code more generic and 'portable' to other applications.

PS: It's not obvious from my profile, but I am a 'she'. :-)

[hvsummer]

@mbizup: Welcome "she", but what is "she", that variant not indentify..
I tho you did

Dim MySelf as She

:D

"Even if you do only have one attachment per record as described, it is good practice to use a looping method as zmbd showed, to identify a specific attachment. That will make your code more generic and 'portable' to other applications. "

I don't know whether my method or his could be more usefull,

but in general, everyone want to use SQL to handle thing instead using VBA.
and if we can use SQL, then we don't force our self to write lot of code and debug, debug, debug when something go wrong ==

you could give File name to ID field, then instead of loop through those recordset, you can simple use

DELETE *
FROM Table1
Where ID = "File Name"

will be easier for everyone == even who don't know bout VBA.

3 line vs 80 line :D I'm faster, sorry zmbd ;)

[zmbd]

+ thank you for the complement, The code I posted is a modification of one from one of the very few databases where I use document attachments; thus, it has had a lot of real-time teaks to trap errors that have arisen.

The reason I do not delete record, as you've indicated, is because the record is related to other records in the database; thus, if for some reason I need to remove the attachment I cannot simply delete that record in the database without effecting the parent or child records. I also may be able to delete/move the attachment from a record to a physical drive; however, the attachment field may have a word, excel, a few images, etc... stored with that particular record (not my favorite design - this is forced by sharepoint).

In the other database I use attachments for, I do normalize the table. One table with the attachments 1:M with the related information. Here though, I "update" an attachment by deleting the old document and then uploading the new document. Once again, there is a related record. Now, one could go in and add a new record, upload, change the related record FK-ToRecord and that works well when one isn't pushing the 2GB upper limit :)


+ As for "not right" vs. "Wrong" - semantics.
This has taken me years to learn and decades to even start to master - instead of saying, "that's not right;" "you're not right;" "...wrong...;" etc... it is often better to gently offer the new information...
"Hey, were discussing [topic] and you said [context] and I found this [source] that gave this [information] and I thought that you might be interested."
or
"in your post you mentioned [context] and I found this information that seems to apply to this ... "

or even... " I know you said this [context]; however, I thought it could be accomplished by doing [information] "

See, this way, the conversation remains academic and factual. Whereas, when one uses "not right" or "wrong" (even it is true :) ) then opinion and emotions can/are brought into "play" and in a text based setting - without body language - such statements can be taken in the wrong manner, even with emoticons.

And let's be honest here, even I still make the gaff/mistake in how I write something. When it happens, I try to apologize, as I really don't mean to be offensive, and get things back to at least a neighborly level of discourse.
:)

[hvsummer]

ok, I'll use "I don't see this way, I'll go that way" ;)
anyway, everyone have their own plan, organize, arrangement. So we can only choose the best for us, not the best for everyone.

in my case, I plan that, and I predict that so I make a table like that then I can delete attachment like that.

but in your case, if you want to change code, you will need to change a lot of thing relative, so that method not suit for you coz it 'll increase workload.

I sometime don't want to change something coz it'll take me much time.
but when my lazy come out, I'll force my body to tweak those problem and I can be lazier next time :D

[jforbes]

hvsummer, I'm not trying to upset you with this post, but I feel maybe you could benefit from some intervention.

You are really making things hard on yourself going about this the way you are. These are the reasons I say that:

First Reason: Maintaining code in Access is much easier than maintaining code in Excel. It's not obvious at first, but you will find out after your code has been used a while.

The first problem is that it eats up ridiculous amounts of storage. Code in Excel is saved along with the Spreadsheet. This makes the Excel file larger than it needs to be, because the file contains a copy of the Data and the Program. Instead of just the result of the Report like a PDF would. And in nearly all cases, the first thing someone does with an Excel file after it has been created is email it to a list of people. which means a bunch of people are getting a copy of that original code, and they will never use it...
Hopefully they wont, which is the next set of problems with developing an application in Excel. If it is a success, someone will want to take the process/file and want to copy it and use it in a slightly different manner. It wont matter how the roll out of the new process is managed, the eventuality is that there will be multiple copies of the code in different states of development and for different purposes scattered throughout the organization. And if there is a bug that shows up after it is in the wild, there is the possibility that that bug will never get completely fixed because the code is everywhere.

I speak of this from experience. This exact situation developed where I'm currently working. It happened before I came back to fix everything. I've rounded up about 5,000 excel files scattered across the network at an average of 500k per file. That 2.5Gigs of file space, just on the fileserver, who knows how much email space it is taking up. The same data in a PDF format takes up about 60Megs, about an eighth of the space and MS-Office isn't needed to open the file. To compound the problem, when the company upgraded their office version, the code in the spreadsheets would no longer work, which causes a failure OnLoad of the spreadsheet stopping it from loading. So there were 5,000 broken spreadsheets on the network. I refused to fix that mess.

So, only using Access, it's much easier to troubleshoot broken code due to an Office version upgrade as there is one place to maintain the code.


The Second Reason: Just because you need to create a report in Excel, doesn't mean you have to have code in your Spreadsheet. You can create an Excel file from scratch out of Access. There are a couple methods at doing this. TransferText works if your not that particular of the format. The other, if you need the format just right is to use VBA to create the File and populate it or to take an Excel file as a template and populate it. Our company creates Bill of Materials (BOM) in Word and they have since about 1995 when they switched from creating them in Wordperfect, before that they were typed on a typewriter. They are very comfortable with their BOMs in Word format and I am currently in the process of changing their minds. It will take a while, but my first step was to create code to be able to Import a Word BOM into Access and then create code to Export the BOM out to Word. I can share this code if you would like. It has afforded me a way to create and edit BOMs within Access without totally throwing off their current workflow. I'm leading by example. They have now decided they would like to know how to start using Access to create BOMs. The point of this is that Data is coming from Word and being Reported in Word and I didn't create a single piece of code outside of Access. So the Code is again maintained in one place.


The Third Reason: Do not save attachments in Access! Save them on Fileserver instead and store the URL to the File in Access. All this trouble you are experiencing goes away. All of it! It's actually pretty easy to do if you give it a shot, and again, if you want code examples, they are available.


The Forth Reason: Email from Access if you have to. The reason that you want to email from Access is that hopefully at this point all of the rest of your code is in Access. So maintenance and debugging will be considerably easier.

On a side note, I try my hardest to deter the use of email reports. There are a lot of reasons, but the biggest is that mostly they are a waist of time. People think they need them, but they really don't. I'm not saying do nothing, instead, I'm saying provide the information that people actually use in a different manner, like a web page or a Form within Access. This as a large subject on it's own, but there are some slick ways to create inline reporting in whatever system your users use everyday. If you want, search the internet for "Key Performance Indicators" sometime.