Export Data from Access to a CSV File [VBA Programming]

[Music] hi thanks for tuning into the video before we get started i wanted to invite you over to my personal website at toddbrainer.com there you can find out more about me and you can even contact me through the website and now on to the video [Music] hi everybody todd here i hope this finds you doing well and staying safe in this video i want to show you how i export data from an access database to a csv file now i could easily open up a table filter the table however i want to or run a query copy and paste that data manually to an excel file then save it and we're good to go but what if i wanted to automate that process what if it's something i run on a regular basis a repeated basis and just becomes inefficient to just copy and paste and open up excel and do all that good stuff so what i've done is i've created a form to where i can filter my data and i can actually input the file path to which i want to save the csv so we can actually refine that process down to a couple of mouse clicks and be done with it so if you're ready let's get started okay so i'm in my database here this customer database and we have this main form to where you can view customers add a new customer view vendors i've added this export to csv section and what i'm going to do is set up a process by which i can select a customer state from this combo box and then i can put my file path in this text box and then when i click on export it'll export only the records from the customer table where the customers live in that state and then it will actually export the file name it and export it save it to the path that i put in this text box so the first thing i want to do is i want to create a query that will narrow the results from our table based on this customer state combo box value so let me go up to the create tab and i'll go over here to the query section click on query design table customer is already highlighted i'll click add i'll close this i'll open this up a little bit further i'll click on id make sure that's highlighted then i'll press shift press and hold shift and i'll click phone which is the last column from that table then i'll drag and drop all of those into our results let me click into the criteria row here and then i'll tab over until i get to the state column so i could click or i could type arizona and tab and then i can go up here to this results group in the design tab and click on datasheet view just to test that and sure enough it runs a query that brings back only records from the customer database where the state value is arizona so let me go back to design view but we don't want to hard code this we remember we want to code this or we want to set this up to be the value that's actually pulled from the form in that combo box so it'll be dynamic so the way we do that is we first of all we need to go find out in our form what the name of this actual combo box is because they all your elements will be named so let me go to right click on that tab and go to design view i'll click on the combo box and go over here to the properties sheet and note that i've named it cbo underscore cust state so let me go back to the query and remembering that i'll go ahead and start typing forms and then we see this show up i'll hit tab and it'll enclose forms in square brackets then i'll put an exclamation point and then i'm looking for the form the name of the form that we're pulling this value from and that's remember main up here that's our main form so i'm going to double click that and then i'm going to hit dot and then it's going to be the name of our actual element that cbo underscore cust state and now i'll hit tab and if i widen this a little bit you'll see the entire string so it just forms with an exclamation point then main which is the name of our form and then dot and then the name of our element that we're pulling the value from in the state so every time the user changes that state and then runs the export process it'll pull in the value from that combo box dynamically all right so i want to go up here to the tab where we see query one click on that x and yes i do want to save the changes i'm going to name the query qry underscore cust by state and we'll use this name in our vba here in just a second so i'll click on ok i can close that navigation pane now now i'm going to click on my export tab and then i'm going to go to the property sheet and that's cmd underscore export is the name then i'm gonna go on the event tab i'm gonna go to the click row and then over here in the on the right hand side there's this button with three dots that's the builder button we'll click on that i'm gonna select code builder from this choose builder list click ok and it's going to open up the vba code window for us our code editor and then it automatically starts this private sub for us and it's command underscore export which is the name of the button and then underscore click because that's the event so the easiest way to do this is we use this do command so it'll be d d o cmd and it'll be transfer text and as i start to click i mean i'm start to type transfer you see there's transfer database there's transfer spreadsheet we're looking for transfer text so i'll double click that and then i'll space and once i space it shows you in the intellisense the different arguments we can use and so i'm going to type ac export delimited and that's going to be right there i'll double click that type of comma i'm going to skip this argument it is an optional argument as denoted by the square brackets so i'm going to skip that argument now i need my table name and in this case we're not actually exporting a table we're exporting the results of a query so i'm going to between double quotes i'm going to put the name my query qry underscore cussed by state and then i'll type another comma then we need our file name so right now i'm going to go ahead and just hard code it to show you how it works and my path is going to be c and it's going to be let me make sure what is my path i want to send it to this csv exports path so i'm going to copy that and then go back over to my vba editor window and i'll just paste that in and then i'll type a name of a file i'll just say my export dot csv close that in double quotes and then another comma and for has file has field names i will just type true because i wanted to bring in the field name headers and then i am done with that so i'll just put a message box in here to let me know that it's done export is complete all right so again it's do command transfer text do command dot transfer text the first argument is ac export delimp delim then there's no second argument the third argument is our table name or our query name and then the fourth argument is the file name including the path and then we set has field names to true so we get all the header fields in the in the top row of our export so i'm going to close this then i'm going to go back to form view and i did select colorado there let's go ahead and select another state and then run this export and so it says export is complete let's go over to our path and there is my file my export csv now if i double click this open it up in excel we'll see we do have only records from the customer table where the customers are from the state of idaho so quite literally with one line of code and of course the creation of a query you can export data from uh microsoft access to a csv file like no problem and in fact you don't even have to have a query if you're not going to filter anything if you're just going to export the entire table you don't even need a query so what i'm going to do now is i'm going to show you how we can do a few other tweaks to this that sub procedure and first thing i need to do is i'm going to go to my my path text box and it's text underscore path or txt underscore path now i want to go back to that click event i'm going to set a couple of variables and now i could easily put um that in fact let me show you how to do that i'm going to remove this path portion of the file name and i'm just going to put that slash with the actual file name in double quotes with the csv on it i'm going to put txt underscore path and then i'll put an ampersand and so that's going to concatenate the actual path once i put it in that text box in the form and that'll be dynamic so if we were to change that path we can change it to another drive a network drive but we can do it in the form so we'll dynamically change and then just keep this naming convention so let me go back and just so you know text path is the form element that's the name of it doesn't need to be in double quotes it stands on its own it's not actually a text string so let me close that let's go back let's go to um form view and let me just make sure let's copy that and put it in our form we'll paste that in and we'll run the export export is complete let's go back to our folder what it does is it saves over it if it has the same name so let me try something different here let's go back to access let's go back to the form into the design view let's go back to our procedure and then let's make this my export one let's save that close the editor and then go back to form view and then click export again export is complete then we'll go back to our path and we do in fact have us a second file now so to take this as a step further we can actually use this value from the combo box where the state is and add that into our file name that gets saved to that that file path that way if you export you know value i mean export records from idaho it'll have idaho in the title or in the uh the file name or let's say you select california it'll also you know say california so that way you can kind of keep track of your exports so how do we do that well let's go back to form view i'm sorry design view and then with export highlighter we'll go back to that procedure click on that builder button and so what i'll do here in this argument i can do my export i can close that double quote put an ampersand and then now i'll do cbo cussed state another ampersand and then enclosing that dot csv in double quotes so what i've done here is now i'm bringing in the value from that combo box so it should say my export idaho actually what i would like to do is put an underscoring or yeah an underscore and then it'll separate my export from the state and then i'll put one here actually no that should be fine all right so then what it's going to do we've already got the text path coming in from that form element so it's dynamic and then the file name will be my export but it'll have the state in the actual file name so we can decipher one from another so let's go back let's click close and let's go to form view and now i'm going to select i'll just run this export for idaho i'll click ok and then if i want to run another one let's do one from main click export export is complete now let's go to our folder so see here we have an export with idaho we have an export with main if we open up that one from main you'll see that it is a record looks like there's only one from the state of maine so that's how you transfer data from microsoft access to a csv file and save it and actually how you can do it dynamically you can name the file dynamically with the actual state that you choose in your form so between a combo box and a text box you can actually dynamically and programmatically set up a process where you can take records from your customer table by state and then save that automatically as a csv to the file path that you choose here there you have it how to export data from a microsoft access database to a csv file and use a form to do it dynamically thanks for watching and have a great day [Music] you

Channel: Todd Brannon

Views: 1,056

Rating: 5 out of 5

Keywords: Microsoft Access, Access 2016, CSV, csv, Export to CSV, Export Database, Access, Access Database, VBA, Query

Id: JlkgnBD_iPw

Length: 14min 55sec (895 seconds)

Published: Tue Aug 25 2020