How to Extract Files from an Access Attachment with VBA

do you know about the attachment data type that was introduced to microsoft access back in access 2007 yes okay that's great but have you ever had the challenge to get out all the files that were stored inside an attachment column in your access database that's what i'm going to talk about today in this video hi i'm philip from codecabinet.com and today i'm going to talk about the attachment data type in microsoft access and specifically about extracting data that are files stored inside the attachment column inside your access database and the background why i do this video and why i do it now is last week i was working for a client and someone inside the organization created in microsoft access database and they used the attachment data type to store files inside the database and the basic idea is actually quite good because they have records about some of their internal processes and they need to basically attach files like pdf documents word documents sometimes images to their process records and so it's only natural to try to store them inside the database so that they are always available when you look at their process documentation but over the years the database um grew and grew and grew because there were more and more files added to it and finally in recent months the database was approaching the two gigabyte file size limit of access and once the hard limit is reached you cannot work with your access database and so they are resorted to find old documentation records and deleting the files from them or deleting the whole records so that they can continue to work with the database and then they approached me and asked me can we change this can we store the files in the file system and not inside the database and i said yes of course we can do that we we need to change the the database a little bit that it works with files that's not what i'm going to talk about today but it obviously this change obviously involves extracting all the images inside the access database back to the file system to get the access database back down to a manageable size and that is one use case where you would need what i'm going to talk about today in this video another use case is when you need files for some processes for example if you want to send an email there are multiple ways to send emails and attach files to then email in an automatic process but as far as i'm aware for all commonly used components you can only attach files from the file system to an email but not directly from an access attachment field so that would be another scenario where you would need the code i'm going to show you today because as i talked about my client earlier they have thousands of images inside their access database so it's not really feasible for someone some user manually going through the database and saving each file uh one after the other back to the file system of course theoretically that would be possible but it would be one two or three persons working on that straight for weeks basically to make sure um there is no error and they get all the files out so today i will show you how you can extract all the files using vba code okay enough of an introduction let's look at code so i've got a database here that only contains two tables and a couple of uh actually only one real relevant module and a query let's look at the table first and now this is what i want to talk today the attachment field called pro pic in this example table here and there is not much to see in design view so we just close this again and instead i run or rather i i show this query in design view and here is the the attachment field and you see it is in the field list but it is an hierarchical field there are sub columns basically inside this pro pic field here and that is that are attributes of the attachment if we run this query as it is then you will see the the id and the title with which has not edited and this is basically our attachment column and if i double click that i get this nice dialog here which has add remove open buttons to work with the files and one important detail here is you can add multiple files to one record in your table where the attachment field is that is a really important detail you need to keep in mind beyond that i'm not going to talk much about this dialogue because it should be pro pretty much self explaining and i would like to direct your attention to this column here or this record there are two attachments stored inside that record and we will go back to the design view of the query now we can this is basically the the top level attachment field as you would add a normal field to a query but we can also go to this subfield sub columns inside the attachment and let's add the file name down here and you see that it's basically um visualized here as pro pic the original column name dot file name but i can work with that as i would with other columns i can query for is null or much more interesting here is not null and the file name is an attribute that is managed internally by microsoft access whenever you add a file to an attachment field access internally will store the file name in the file name sub column basically of the attachment so we can rely on the file name always being filled with the file name basically when there is an attachment it is not possible to add an attachment to the table without also the file name field being filled so if we query for is not null towards the file name then we can just run this and it will show all records where there is a file inside the database and the reason why i show it this way is that you see the original file name here that would not be possible if we just would have used the attachment field and i would like to draw your attention to this two records here oh sorry so the important bit is not the file name but the record id here so this is the primary key of the main table we are looking at so and that is the record where there were two attachments added to the attachment field so if i display any data from inside the attachment basically these sub-columns like file name then i will also get two or more if there are three attachments i would have seen three records here in this query that's something to keep in mind it is like there is another table joined to that table in an one too many relationship and actually that is exactly what is happening with attachment fields behind the scenes you cannot really see this table on the many side of the relationship but it is there under the covers and that is basically the reason why we now see two records because we we see each main record combined with each record from the attachment field and so it will display two records in the query output here so that that is just background information here we we save that and now we get to the real topic of today's video we want to extract our attachment data from the table and i already prepared a little um procedure here so here we go but it is missing a great deal here basically this red rectangle that is where some code needs to go but let's look at the code that is already there or here so i switch to procedure view to um to focus on that and i also get rid of the project explorer so now first we've got two records here two variables here the main records and the record set attachment both are record set types but the important bit is they are of type record set 2 that is a new record set type that was also introduced with access 2007 to support some extensions to the database um data types and database functionality with that access version and if you want to use these extensions you need to use the record set to type for your record sets but then just opening a record set works all the same as it did before it's just database dot open record set and you can pass in a table or as i do here query string and when we look at the query string we should also look at the arguments i pass into my function that is table name attachment column name and here's a two directory which is also a string even though you just can't see it and the two important bits are the table name important for now which goes here and the attachment column name which goes here and also here and so we build up the dynamic sql where i can from the outside pass into the function i want the data from this table and this is the name of the attachment column that's basically what i'm doing here and finally the where condition is what i've just shown you inside the normal query in axis just to filter records where there actually is an attachment in there so the next bits here are totally standard stuff i loop through the record set i i move to the next record and there's the end of the loop and finally i close the record set and set my variables to nothing that is box standard code i don't think i need to to say much about that but now let's think about our attachment type and usually we would use something like this our main record fields and we want to get to the attachment column name dot value and with normal data types we would just assign that to a variable like a string a number type or a variant and then we have stored our value in a local variable and can do something with that it is a little bit different with attachments well it is almost the same but not entirely because as i've showed you earlier there is basically a hidden table involved storing all the records inside the attachment field and so we need another record set here to get to that hidden table and that is what my rs attachments is about and i assign my record set attachments which is also record set too i assign that to the value of the attachment field from our main record set and now we will do another loop here through the attachment record set and that is basically the same procedure as we do it with a normal record set but it is slightly surprising that there is another record set inside a field of our record set that is what you need to know basically to get to the attachments and we can also add a record set attachments dot close here because there will be a new record set for each record in the main table so we can close that after the loop before we move to the next main record down here now what we need now is an output file name which is a string here we go and that is where the to directory comes into play that is basically what i um the directory that i want the files to go to because you remember our goal here is to get all the files out of the database so part of our output file name will be the to directory and the other part will be the actual file name and that is luckily stored inside our database and we can get to it simply by using the file name field from our recordset attachments so so in the first line here we get simply get the file name that is stored inside the attachment column and in the next line we concatenate that with um the path separator windows and the output file name here now we got a valid hopefully valid path to some directory on the hard disk where i can store this and the next thing we need to do is actually save the attachment file to the disk and that is luckily very easy we just need to address the file data sub column inside our record set here and this has a method that is not initially displayed but it's there you can look it up in the object catalog and it is safe to file and that will already save our file from the database to disk and we only need to pass in the output file name to tell um to tell the record set field where the data should go to and that is basically all we need to do i already prepared a call here to uh this function in the immediate pane i pass in the table name which is tbi product local pro pic is our attachment column as i shown you before and this is basically the attachment field here in our um in the file system and now i just hit enter to run this and it appears like not much happened but in fact i need to interrupt myself for a short advertisement here i created a full online course on vba programming where i teach you everything you need to know to get from a beginner or intermediate level in vba programming to a quite advanced level where you are able to solve complex problems and develop whole applications in this course i cover the vba development environment variable declaration scope and data types procedure types and arguments object-based programming control flow error handling debugging and how to put all that together to a working application if you want to know more about the course go to codecabinet.com courses quite a bit happen because now you see the four files i showed you earlier in the query are here in the file system and i can just open them as normal files that takes a minute um until this all shows up here but that is because my computer is pretty much maxed out and eventually it will show this nice picture of the cathedral in frankfurt on mine but that that is of minor interest here i just wanted to show you that the files are ready to use once they are extracted you don't need any post processing they are just normal usable files so we could be done here but we aren't because there is a minor detail i would like to address here you might have noticed that there are two tables here our tbi product local which we worked with um until now and one with this yellow icon and you also see the tooltip it is a sharepoint table or rather a sharepoint list that has been linked as a table into my access database and these sharepoint lists i don't want to go into too much detail but just that you understand the context these are lists on the sharepoint website hosted at the microsoft 365 web or cloud platform and these are basically lists where you can store data and also files and you can link these lists into your microsoft access database and then they look like this and they can also contain attachment fields like the ones i showed you before and in theory it should work all the same with our procedure to extract these from from the table to the file system and i quickly need to run another procedure that is empty directory and i need to pass in the directory name that is just going to delete all the stuff in that directory because if i would run that again there would be conflicts in file names and there would be an error so i just cleaned the file system here to make it ready for our expert and now i changed the table now we are getting data from our sharepoint list and this will result in an unpleasant surprise we get a runtime error with a stupid number here that is basically a com edge result with an unknown error message and that h result repeated and that is pretty much a non-descript error message i'd say and we going to debug this and if we look at our output file name you will see the problem right away this is basically a web url here it is not the file name it is on the web and well it's basically impossible to store this data to the local disk because the file name is not valid on disk and well there are two problems with that the first one you have seen the error message is total garbage um microsoft just forgot to add a proper error message for the situation when the output file here is not a valid file name that is a minor problem if you know what is the actual cause of the error the slightly bigger problem is that you now get the url of the file on the sharepoint site here in your code and that is obviously a little bit of a problem and we cannot use the code as is but i created a small helper procedure down here that is extract file name from full path and it will take a path originally designed for a file system path and it will just trim away the whole path and just return the file name bit without the path and i added an optional argument here the path separator which defaults to the windows backslash file separator and this function will just give us the file name here and so we need to use this function here uh extract file name from full path and we pass in the file name from the file name from our attachment field but we also need to tell it to use the forward slash to trim or extract the file name from the url on the sharepoint site so let's get rid of this stuff and run the procedure again and it's still running that takes a moment to extract the files but now it completed and you see there are even more files because um in that sharepoint list there were a couple of other files like this pdf which wasn't there before okay so oh just one remark why i say it is a bug that the url is returned in the file name you might say yeah that that is actually helpful to know where the file lives on the internet on the sharepoint site correct it is absolutely correct that would be helpful but there is actually a different field that is hidden but it is documented that is also inside the table and that is called file url and that will by definition according to the documentation always contain the url on the sharepoint site if it is a sharepoint list we are talking about and the file name by documentation should only contain the file name not the full url so that's why i say this behavior here is a bug but we addressed that and solved that and so we're basically done for today i hope you learned something new here and it is something that is useful to you and will rescue you when you need to extract attachments from an access database [Music] you

Channel: codekabinett.com/en

Views: 6,805

Keywords:

Id: jHIgay9goWo

Length: 30min 51sec (1851 seconds)

Published: Wed Mar 30 2022