VBA for Working with Access Attachments

hey everybody welcome to our video on the Microsoft Access attachment data type started with oars in 2007 Microsoft gave us a new data type the attachment data type which used compression for storing documents and images directly in our tables now there are plenty of people who would rather us not stored documents directly in our tables but instead to continue to store the full path for instance to a document and door the document itself on the file system and use the path in the database to get to that document and that's fine but if you would like to store documents directly in your tables I think that the attachment intact theirs is a better way to go than the Ola the attachment use a compression and it's gonna make for smaller tables as well as some of the built-in tools and Microsoft so provided for the data type it's fairly convenient and with the attachment data type Microsoft went one step further and decided that they would allow us to store multiple documents on a single database record now they tell us not to worry that they're not actually storing multiple values in a single column like it appears whether they have normalized these storage of the data more than likely there's a hidden table that we don't see and as multiple rows in it which then is is joined to the record that we see in the data table so in this video I want to explore the attachment data type we're gonna show a simple form and some of the built-in functionality Microsoft provides then I've got two additional forms to provide a different view of the attachments the first form has zero code in it at all and the second form replicates uses VBA to replicate the built-in functionality to Microsoft provides us not something to do the replication but because it gave or it provided a nice framework or example of how to work with the attachments so let's take a look at one of our example tables I have a table here that I used in a previous video called images on how you can see I have three records and over here we see that the first record has three attachments the second one has two attachments and a third record has one attachment and the form I have bounded this it's a very simple form all right it's we've got the abusing the built-in navigation bar the bottom here and we're showing a single record at a time of course we walk through it using the e for the backward buttons and you can see what I have here and let's take a look this really quickly in design of you all we have here is two text boxes between the record ID and the image ID which is nothing more than string identifying the the record and then here we have an attachment control this is a control is specifically built to work with the attachment data type in its default behavior it's gonna show us the first attachment it's on this record if we click it as you see we get a pop-up menu over here and we have arrows where we can move through the attachments now we're on the first record in the table we see here we have three living up here we have three attachments here so back down here we can walk through three attachments like that we can also click on the paperclip and it gives us a pop up dialog box here you can see a list of the documents that are on this record we see all three of them here and they got built-in functionality here for adding a new attachment we can remove one we can open an existing one it's popping up in Excel instance of Excel on the other screen we pull it over there good and we also save the selected document to disk we're not a lot of doing that so I find this view a little bit limiting and that you will get to see one attachment at a time so I thought why not build a continuous form that allows us to see all the documents they're attached so to record more or less than one I shot let's close these guys want to open up a different table here it's attachments table is pretty much the same thing we just had I changed the names of the columns here to be a little more generic so what I've built here the no the no code form out of the way this guy up with him over here maybe there we go this is more like a a header detail form although not exactly because the detail area is actually data coming from these same records being displayed at the top so we have here in terms of functionality is we are we can see that we have three attachments on this first records in database and here are all three being displayed something happened nothing happens when we click on these attachment controls we can navigate to the second record in the table we can see all four documents that are attached here because here the floor up here a third record we have one attachment okay so that's I think of a better view of what we have let's take a look at the design view of this form now what I've done we have a form and it is simply bound to the attachments table so have three controls at the top two text controls that are bound to two of the columns and this third is the attachment control that's meant for working with the attachment now I modified the behavior this guy by changing the display as property over here we can say display image icon which is the default and then just play the form what you get is you can actually see what it'll show you is the first attachment on the record and that's not what I want here because we're displaying the documents down here so I had no desired to to try to display any sort of image up here in this control one of that control simply to to get us to that pop-up dialog that allows us to work with the attachments which is why I chose the paperclip appear for display as because it'll show us how many documents are attached while still giving us that pop-up menu if we click on it a single time in this paperclip view nothing happens but if we double click on it it'll pop up that menu excuse me pop up that dialog box where we can add another attachment let's say add in flow circle and close now something interesting here we just add an attachment but notice the bottom of our form did not change we have to either provide a refresh button to make this subform require itself or have to navigate off that record and navigate back on in order to see that fourth document appear so let's take a look at the sub form in design view double click on it notice I've got this not bound to a table but bound to a query and before we look at the query let's take a look at a query of our attachment table see what Microsoft shows us close that notice here the in our attachment column we've got three additional columns column name the column name of the attachment column inside there you have a file data a file name and a file type notice the dot notation document dot file data dot file name and dot file type let's close that we can use that in a query that we write so our subform is bound to a query I'm selecting the record ID from the record we're gonna need that because we need to join these we need to join the subform make sure that we have the right record and a subform displaying for the same record on the header so we're providing the record ID and then we're providing each of those three columns document file data document file name and document file type from the same table this is an odd header detail type of form because like I said we're showing the same record up here in the parent form as we are down here let's back out a so form and hit the subform container okay just with just a single click and notice here that I am linking it on record ID and that's how we're gonna get away with navigating around without any code this this subform linkage here this built-in functionality is gonna help us back in the sub for mercy I've got to set up as continuous forms which is so that's how we get the the records let I listed one above the other and also one more thing to point out I've got up here denote double click to manage the attachments that's how we're popping up that dialog box I had to point out that I do not have any code attached to the double click event that was built in functionality with the Knigge the attachment control so as far as the controls that are on the subform we have an attachment data type here as well then again to text box controls down here so I'm excited that we can do this much with no code at all it's the only shortcoming I see with this form even though it has no code is if we just choose to add or remove a document here see the the user interface itself does not update itself until until we need a navigator way or if I were to provide our refresh button up here it was cool we can do this much with no code but I think that it's more fun to learn how to deal with the attachment data type in code and a good way to do that is to emulate or replicate this built-in behavior over here with VBA and Dao so that is our next that is our next form so this guy looks pretty much similar again we have what appears to be a header detailed type of arrangement loose arrangement okay same text boxes up top saying similar continuous form at the bottom what's different here though is I've added some code to display each of these images if we double click on them and of course it pops up on my other monitor there it is I've got a spreadsheet here with pretty much nothing in it but its name tip Excel other functionality here we click on ADD and we get a a file open dialog box to add an attachment let's see what I don't have over here this ad and this guy right here you notice what I've done here I've got a refresh built in now now that we're in control of this code we weren't in control the Microsoft pop-up dialog box where there was no place I could put code they could refresh that subform because we lost complete control when that Microsoft dialog box opened up our code our code did not wait for that dialog box to close which was odd which I thought was odd to me most alo boxes of the open our code of pauses and waits but in that particular for that particular dialog box our code ran to completion before that dialog box has ever even finished opening so eyewear was no place for me to attach any sort of refresh code and unless I did it with a button click which I didn't want to provide I wanted to do it in this form so we've got the add functionality here we've got delete functionality here we're gonna give us a give ourselves a check here before we do the delete and then we also have save functionality we can click on one of these suck on one of these and save it and get a save dialog save it to our our file system so let's look at some code let's look at what we do under the add button first just the code window for the other forms notice there was no code there I'm going to show that earlier so here is our second form this is the header part of the form of the D parent form if you will the outer form and here's our command add button I've got two record sets in here one for the the table itself which I'm calling our s parent and then one for the attachment column which I'm calling RS attachment this is our child record set we have to use the record set to when we're working with that child type record set I'm also got a variable here to hold our query I hear they're able to hold our file name we've over the file dialog box so we got the office dot file dialog here I've got a video out there for that already we're not going to spend much time on that another video we're gonna talk about that so the first thing we need to do when we want to add something is we're gonna ask the user what what are we gonna add right by popping up this box so as we're doing here with us at eff dialog equal to be you file dialogue it was a file dialog file picker time dialog we got a width set up here to work with all the stuff inside the dialog we're setting our title equal to choose the document to add to the form I'm studying multi select equal to false I want them to choose one document at a time because that it's true and then you need to have a loop in here to work with with each document and then that would be kind of cool too but but not today and I'm just giving us a initial folder to start in if that show is true that means if they open the dialog box we go inside here if a set of atoms count is equal to zero meaning they didn't select anything we want just go ahead and going out exit our subroutine however they did select something we get to go down here and we're gonna loop through the selected items even though we're fully allowing them to choose one I always use this for next loop anyway even if I've got multi select equal to no it's just easy to remember that way it's copied in and don't have to worry about it we're gonna grab the file name from the selected item which is here it's a now string or STR file name holds the file that the user wants to import we use VBA and Dao to insert it into the table so first we're going to get whatever record we're on on the form into a record set so here we have a record ID right from the record we're on I'm going to select our record from the attachment table where record ID equals record ID this we've got displayed in that text box we're gonna hold up the record set with our query we're opening in and a dinah set this time and the reason why is we're going to update this record set so we can't do a snapshot can't do afford only cursor it's got to be either dinah set or a table data type and all that's going to go into the RS parent record set I'm gonna test to see if we got anything back from our query if we didn't we're going to tell the user we have some weird problem we've got a record we can display but we can't actually query which is something I wouldn't expect to find but we're gonna handle it just in case if we did get a hit we're gonna put this record set in edit mode because we're about to do something to it right we're about to add a record to it then we're going to take our attachment column which we've called the document the value of that okay document bag and when loaded into our child record set our s attachment now the whole goal of doing this whole exercise was to add a document to this attachment column so ours attachment dot add new and adding into the file data column we usually load from file method and we give it the path that we just obtained from the file dialog box as we've done that our attachment update that's our inner or our child record set and then the outer or the parent record set we're update it as well this is where we get the subform to update itself okay we name the subform dot re query so over here we're gonna add click add let's add something a dollar sign yeah there you go sign add it to the bottom so next let's take a look and I've delete functionality pop-up dialog box asking them are they sure give them the name of the file so they can double check they click no nothing happens if we click the S of course we want it to disappear over here on our parent form again we have a delete button this code should look fairly similar okay now the same parent and child record sits at the same file name here we need to file them from our child record set because we need to know which child record we the user wants us to delete over here we have to pay attention to which one of these has been clicked so we have clicked test doc let's say it's right here we're going to grab the text file name which is here from our subform and store it in this string then we're gonna pop up by my sis box from using them as flux as a function okay we use get test the value returned from its miss box here's the question we're asking there's a filename being plugged in VB question pops up that question mark right there no wait and the next one BB yes/no tells the nest box to show the yes or no buttons only yes see the question plus VB yes/no here's our title and like I said we're using as a function so if the value returned from here equals VB know we're gonna get out of our subroutine and not do anything since we only providing a yes or no don't click no it must be yes right and if it's yes we're going to head on down here and perform our delete so our top queries for much the same way except that I'm only selecting the document column here from attachments where record ID is equal to the same record ID this being a split in the form so we're getting them to the correct record we're gonna edit we're putting it in opening and data set again we're gonna edit the record set our living into the RS parent record set now check out record can't we got nothing we've got a problem we're gonna get out if we did get a hit and going down to our delete code we're gonna load the contents of the document column into our child record set like check to see if we got any hits and our child record set and if the record count is not equal to zero we're gonna go ahead and go on into our into our if and perform the delete we need to find ok now this has gotten us in the case of the record we're all in this gotten us four rows ok we have four rows on this first record four documents in this attachment column so if we were on the first record we're deleting one document from there this record set holds four rows right now we have to find the one that the user want us to delete the one the user wants to delete is stored in this string file name so we use the record set fine first method file name equal to and I've got a tick mark there because that's a string a string column or a file name equal to string file name if no match we've got a problem again right we can see it on the form but we can't get to it with what the query that's that's a weird problem want to display that error and get out but if we did get a match in our else all we got to do is ours attachment not delete this will delete whatever row you're currently on and then again we're gonna re query our subform delete test doc yeah delete test doc yes you can see the R you query refreshes display next the save attachment code okay we can't use we can select a a document and save it to a folder somewhere we're just gonna save us in this test saves folder that popped up a dialog telling us successful I'm gonna slide a Explorer window over here and go to the test savings fault you see our guy sign ended up in there we're expected it to and the code behind the Save button is a little bit different from the other code because I have a situation here where I've got some code I wanted to share with another event on the forum and that is the double click event of the attachment control down there in order to display these documents we need to first save them to disk somewhere so that we could use the shell execute API which takes a file path as an input so I've got a need here to do a save to file I also have a need here to do save to file so I wanted to go ahead and use the same Co same basic core code to perform that save so let's head over to our code window again here is our Save button click again we're going to start off with a file dialog they have they have selected a row in the table they want to save and now we want them to tell us what folder to save that file in so I'm open about frog dialogue as a file dialogue folder picker this time give it a slightly different title I'm still a multi-select equal to false again I'm giving them a default folder to start in to start saving and or start browsing in all this code is pretty much the same we're gonna see if they selected anything if not we're gonna get out if they did we're going to loop through the items again willing line 1 selection so we'll only go through this loop this for it for each next loop one time so get our second item we're gonna store the folder name they chose in the variable string path here we're gonna call a function to do our save however the function calls save at the file that save attachment to file and it's taking several variables it's gonna take the record ID of the parent record set let's go take the path to fold go take the path to save it to and it's gonna take the file name from our subform that's the actual document itself and this function returns a true or false true as successfully saved false it did not so on the true condition the true return that message box that says file saved on a false is gonna return some sort of error occurred all right let's take a look at this shared code I put it in a public module and the reason I did that back over here I've got a save attachment button up here on the parent forum and I have the other event that I wanted to use that same code in on a subform so I decided have to put that code in a forum anywhere but instead to put it in a public module that both forms could get to easily so our primers going into this was a record ID of the parent table the outer table the file path to save to any file name of the document it's in our attachment record set or our child record set yes for turning a boolean value can we got similar code we've already seen we've got a parent record set attachment record set SQL as a string and a file name I'm gonna set the value of my function equal to false at the top so if we get out of here some catastrophic win don't even make it to our our our error code with still returning the right value of false I'm gonna queer your attachment table and get that loaded into our parent record set in this case I am opening a snapshot I don't need to be able to to update this record set so a snapshot is fine again the same record count again if we don't get a hit we're gonna get when I go down to no doc found and what I've got down there no doc found there's a message box saying couldn't find it the reason I've done this instead of embedding it right up here is I actually need to do this from three different places so let's not have the same code in three places let's just have the code in one place so the go to take us to here and in our air area pop up message box and we go to sub exit just there clear out our record set in accent function if we did find a record we're looking for the one that's being displayed on the form we're gonna know the attachment column into our child record set record set - it's ours attachment again their check to see if we got any hits did we get any rose back if our record count is not equal to zero I'm going in here and we use the same fine first method that we had before we're gonna look for looking for the file name being passed in so our stats were not fine first file name equals to the file name passed in if no match I got some weird problem gonna get out if we did get a match when you just we need to build the full path file name we want to use when saving this file so string file name is gonna hold file path which was passed in that was the folder the user picked got a slash to it and the file name from our child record set so we got a folder and then the actual file name itself being tacked on to the end of it okay check to see if this file already exists if it does we're gonna delete it and then save over it using our attachment file data save the file there's a file name and if we get to here then we're set the value of our function - true true we just successfully save this file now back up to this this might not be the assumption you want okay you might not want us with anything be deleting files from your customers hard drive a my example here it was easy to do this I'm not worried about it assisting the example application I would advise not doing this I would advise if you find this file right here it's probably message box time right this file already exists do you want me to delete that file or give them some sort of options either that or perhaps you want to if you find the file at existing maybe you want to append some sort of number to the end append a number and check again you know so you have to build some sort of loop here right append a number check if that one exists as well and come in that number check again and keep going through that loop until you find a value of that store that filename does not exist so you can do your save maybe you limit the number of times you go through that loop to ten or something like that and then get out with a message box or some sort of error anyhow so down to here we go we have saved our file to the folder everything goes well we fall down here to our sub exit label and close our record set and set it equal to nothing now one quick note here we had two record sets up here if you want to close both if you want to close both and set both to nothing that's fine although I have found it's setting just the outer one closing it setting it nothing also closes and destroys the child one and I will tell you if you close and set to nothing the outer one first and then try to close and set the inner one second even with this on-air resume next you will get an error in your code will stop and it will pop up the debug window for you so if you are going to explicitly close both close the child first the innermost and then close the parent the outer most that's it back over here let's say we save our tip Excel to test save all saved and there we go got our spreadsheet saved there so what's last one then is this display code double click Excel pops up and here is our Excel spreadsheet being displayed for us so the thing about this is Microsoft is aligned it's a store pretty much any type of document in here that we want right I've got two images I've got a spreadsheet on my other record here I've got a Word document spreadsheet two images we have a couple ways we can go here you can you can test this file type and hard code which executable you want to use to open and display that file you can test the file type and use I think it's so I think there's a VBA shell command I've never used it but you have to tell it the name but again the alias to be executable to use I'm not too into that Microsoft has built-in way to do this right the the shell execute API will read our will read our registry to figure out what executable to use to open a document write a file so we're going to so we're gonna wander into the world of Windows api's very briefly now I people don't like these there they find them ugly I don't particularly like them myself they can be confusing and documentation on them it can be hard to find but as far as api's go the shell execute is one of the more benign ones so here we define it declare a function shall execute library is the name that DLL where this function live the Windows DLL where this lives you can give an alias if you want which to call it by and here all the parameters that this function takes now I have some code down here it calls it but let's go back to before we look at this code let's take a look at I can find it have to take this kind of design mode here click on our subform under the code in our subform so here is our double event the attachment control what we're gonna do here is we need to save that document to a temporary location and then use the shell execute to open it so I've got a hard-coded temporary folder here this might not be the way you want to do it you might want to store that somewhere in a table instead but for our purposes here is to make a simple hard-coded folder for a temporary storage location I'm gonna call the save add to file function we just looked at we're not gonna look at again but we know that if we get a true return out of this this file was saved into this string path was saved into this temporary location and we're going to build the full path to that file name so we've got the string path we're gonna add our little slash here and in the text file name text-box file name that's the name of the document itself so this holds the full path to the document that we want to then open with the shell execute now I have a function here that I've built it's gonna call the shell execute and we'll take a look at why in just a minute the call if s cute we're gonna pass to it the full path and also the folder that is holding in this string return is a string our function that built is gonna return a string to us it's gonna return you to success for most successful open your document or it's gonna return to us an error message so if it does not return the word success we're gonna pop up message box and we're gonna display this string message I'll take a look at where those come from it is a second so back over here - shell execute so here is our function our windows api and all the parameters that it takes and down here is where I'm going to call them calling it we just called the call cell execute function when we passed in the full file name and the folder and it's gonna be in we have some constants that Microsoft's provided for us to help interpret the return code from the shell execute this guy returns a string so I'm going to send equal to success the top and then if we have an error will reset it down below so here's our call to shell execute this the first primer takes is the handle to our application access application so application dot H wind access app that's the handle the next parameter takes is the operation we want it to open the file we're giving it and next is the file the actual file I want to open that string file is the full path and file name that LP parameters we're not getting any parameters this time LP directory that's the like the default directory we don't need that in this case but we're gonna pass it just to be safe and then the show command this is to show when it opens the document and the X and whatever application that needs to use this is then I have one this is show normal the execute returns a number do we have a a long we're gonna put that in and then we're gonna test the value that long and compare it Dalia's different constants these cost us tell us what type of error it had at the rivet at the return code is less than less than equal to 32 we had an error so we have a case statement here we test the value and long return and we just let me just compare it to all these various constants and provide it with whatever the error code means and this is code this is these are values that I got directly from Microsoft's website I'm gonna load these values these are pair messages into the return value of our function itself and then we get out go back to our subform again what we've tested the value being returned if it's not success we have a message box if it is success we just get out I said for this video I of course I just noticed at the end now that I had a bizarre title at the top of my form English having difficulty tonight with I guess order of and got it corrected now apologize for that a purpose for for this video wasn't necessarily to do build a continuous form displaying attachments but more to give me an excuse to to write the code for working with them anyhow I hope that you got something out of this video as usual I have a link to the all the code the VBA in the description down below and we'll see you next time

Channel: Access Jitsu

Views: 30,353

Rating: 4.8816566 out of 5

Keywords: Microsoft Access, VBA, Visual Basic for Applications, Microsoft Access Attachment Datatype, Access Attachment Column, VBA with Access Attachments, Microsoft Access and Win 32 APIs, ShellExecute VBA, ShellExecute Microsoft Access, VBA LoadFromFile, VBA SaveToFile, DAO and Microsoft Access Attachments, Ray Harvey

Id: Gm5fch0xaI4

Length: 35min 48sec (2148 seconds)

Published: Sat Oct 03 2015