Convert Query SQL to VBA Code in Microsoft Access. Add Line Breaks, Quotes, vbNewLine, and More.

welcome to another tech help video brought to you by accesslearningzone.com i am your instructor richard ross in today's video we're going to build a little tool to convert the sql that access generates in the query editor directly into vba code that we can use in the vba editor without having to do a lot of extra work adding quotes and line breaks and line continuation characters and all that stuff we're going to do it with one click today's question comes from john from hancock new hampshire one of my silver members he's also a five-year learning connection member john asked me is there a way to format the sql text of a query with the proper line breaks and quotes so i can copy it to my vba editor for a run sql command without doing all that manual editing yes john that can be a time-consuming and cumbersome task if you have a query built and one of the things i teach in my developer classes is that i don't like having tons and tons of small queries in my database you have big complicated ones you want to save but if it's something small and relatively simple i don't like making an extra query for it because it just makes your database cluttered with junk so what i teach in my developer classes like john knows is we'll take those queries and we'll put them directly in our vba code using a run sql command but you have to take that sql that the query designer puts together for you copied over to vba then you got to put quotes around everything and separate out the variables and put line continuation characters on it if it's long and that could take a few minutes so there is no way to automatically do this in access however with a little bit of work we can set up our own little editor where we can copy and paste in the sql that access gives us and then convert it over to something that will work very easily in vba so with a couple of copy and paste your job is done let me show you what i put together we're going to start off with my blank customer template you can download a free copy of this from my website i'll put a link down below in the description go grab it this is the basic simple customer and contact database we got a customer list we've got a customer form right simple information customers with contacts if you want to see contacts now i don't have any queries in this basic template so let's create a couple let's go to query design let's make a customer query now let's start with something simple let's just bring in the star and we'll save this as my customer queue my simple customer query now where do i find the sql that access generates we'll go up to view an sql view and that's what you got there all right let me copy that now i'm going to drop that in notepad for just a second there's a sample notepad i'm going to move this off the side of the screen all right just move it over here we'll hang out at that for just a minute all right we can close this now where would i use that you might be asking well i've got my customer list here let's say i want to add another button so i can filter the results up here based on whatever's in that query okay so let's design view real quick and i will just copy and paste this guy all right let's put in here filter all right now the query could be anything this is just a simple example all right but i want to change what records show up here by changing the record source right the record source property which right now is just customer t but i can come in here right click build event well i don't want a macro this has got a macro in it so let's get rid of that macro first uh actually let's give it a name filter button right i like to give all my buttons a good name thank you alex for teaching me that uh for years i just left him as command nine but alex kept on me he's like you gotta change those it's bad code and he's right let's get rid of the event there's an embedded macro in there we're just going to delete that goodbye then i'm going to hit the dot dot button in here if you get asked pick code builder you want to go into the code builder and right in here is what's going to happen when you click that button i'm going to say me dot record source equals now i could go customer queue all right i could set it equal to customer queue and make a saved query but what happens if i got a bunch of different filters i want to apply and i want to save those i don't want to have to have 15 different queries in here for all this so that's where this comes into play i want to put this sql statement right in here then i don't need this query anymore see how this works and i've got a bunch of different stuff in here okay now to put that sql statement in here requires this i have to copy this all right paste it here but it doesn't come in very handy all right it access makes the queries in multiple lines yeah that's fine that's nice all right but now i got to get rid of this i gotta get rid of that line break put a quote on the end here and this is an extremely simple query sometimes you get ones that are multiple lines long you got all kinds of other stuff where conditions order by statements and it's just it's a it takes a while to format this to get it nice and to fit in here so what i'd like is a tool where i can just say take this query text and convert it to what i need right here all right you can also do this with do do command run sql statements for things like update queries and uh append queries and things like that all right but now if i come back into here it's the same set of data right and then i go filter what happens if you want to say let's say select customer star from customer t right order by last name all right sort of my last name for example and then go see how it sorts them okay that's why you want to have different filter buttons in here and i cover all this in my full classes but the point of this class is to say how can we turn this quickly and easily into that all right let's go back to the main menu design view now i've already got a status box here if you haven't watched the video where i build this customer template go watch it it's called the blank customer template there's a link to it down below where you grab this file from watch the video it explains what this is this is basically just a button that that puts some stuff in a status box that's what the message box things just makes it easier to get statuses and put stuff in there all right so we're going to utilize that all right let's move the whole hello world button up top in fact i'm going to get rid of these buttons here we don't need them let's put hello world over here and we're going to call this guy convert all right now i'm going to take this and make it nice and big so we can see all the sql in there i don't think we need the main menu for now let's get rid of that too we're not getting rid of my advertising logo that stays on the screen all right so this guy's called status box let's make a copy of this and this is where we're gonna paste our stuff into so copy paste all right we're gonna we're gonna paste our sql into this top box let's call this guy sql text alright sql text all right and let's just change the color a little bit let's make him yellow so we know the difference here okay so we're going to grab this copy and paste it into here and then hit the button convert stick the button right there and then it'll turn into this all right we'll format it properly how do we do that with some string manipulation all right let's close this save changes yes open it back up again we got a fresh start now i'm going to copy this stuff here from my clipboard right copy paste all right now in order to do this you have to understand the concept of the vb new line okay it's actually a combination of two characters it's a carriage return and a line feed it goes back to the old days with a typewriter right it would get to the end you do a carriage return and a line feed all right line feed basically as you go to the next line and then a carriage you know feed the paper up right and then a carriage return sends the head whoops sends the head back to the beginning so it's it's new line and then carriage return or vice versa it doesn't matter and that can be represented in access with a constant called vb new line all right it's actually a chr13 and a chr10 we don't have to worry about that right now all we need to know is vbnewline that says go to a new line okay so what we're going to do is first thing we're going to do is replace this string and everywhere we find a vb new line right here all right we're going to replace that with a close quote an ampersand and an underscore character because in our in here okay we don't want to make the string super long all right and this looked like this initially so i'm going to make this two lines if access made of two lines i'm going to make it two lines all right so select customer star like this it's going to go we're going to put the vb new line was initially there all right like that so we're going to put close quote ampersand underscore that's the vb uh line continuation right tab tab in here and then the next line we're gonna have to put an open quote like that and then when we're done with the whole thing we'll put a quote at the beginning and a quote at the end and we'll get this and we'll get this string right here all right how do we do that what we do with a simple replace function if you've never used the replace function i've got lessons on that i'll put links down below in the description okay so in our button we're gonna get rid of this we don't need that let's dim s as a string temporary variable to help to hold on what we're working on all right let's start off with s equals sql text let's grab the text from the text box and put it in s i don't like working with stuff directly in text boxes i like to take it and put it into memory variables it's just it's cleaner for some reason it works better i've had problems now here comes the replace s equals replace what's the string we're replacing stuff in s what's the find string i'm looking for a vb new line all right access nodes that's a chr10 and 13. okay so what am i going to replace it with well we're going to start off with inside of quotes start off with replace that with a close quote all right and underscore and then a close quote for that and we need that vb new line there and let's throw a vb tab in there too vb tab so it tabs in that's another character that you need to learn all right that's just a tab character and now we got to close the quotes actually this will be the open quote on the next line all right so it's going to be quote quote quote quote close parentheses all right enter and i missed something in here what i miss i got one too many right here there all right this double double quote becomes a single double quote when it converts over to a text string if that's confusing for you i have a whole video on it it's confu it was confusing for me at the first two okay it's called the double double quote problem and i'll put a link to that video down in the links down below all right now when we're all done doing that let's status s let's put that basically puts s in the next text box let's see what we get all right save it come back over here and hit convert boom and i still got hello world why do i still have hello world aha i have hello world because i put this code in the wrong place rookie mistake but i'm not going back and redoing the video because you know why you'll make this mistake eventually too all right i put this code in the wrong spot i replaced that filter button click and you can tell right up here customer list f all right i've done this before i guarantee you'll do it okay so what we have to do is we're going to copy this stuff here yeah the filter button text is gone want to redo that later if we want to come back over here or you can use your project explorer if you want to open up the project explorer i'm not going to go that deep right click design view right click build event all right now i'm right inside the hello world button click i'm going to get rid of all this other code we don't need it the command 10 this stuff here let's get rid of all that so it's cleaner all we need is the status function up top or sub all right let's paste that code that we just stole right there okay that's the stuff we just wrote save it say yes close this guy close that guy let's open it back up again alright let's see if it works go oh invalid use of null what happened debug all right sql text is null why because i didn't put anything in here right i closed the form and reopened it i didn't put any code in there again rookie mistake you'll make it too so we'll just say right here if is null s sql text then exit sub all right don't do anything now convert oh nothing's happening why because there's no code in there all right let's go back over here grab this stuff put it in there now let's see if it works go perfect right select customert.star close those quotes ampersand underscore open the next line of quotes there is a vb tab in there but you don't see it in the text box when you copy it you will okay now we just have to add the opening quote there and the close quote there that's easy to do all right come back over here here we'll say once we've done that we'll say s equals open quote and s and close quote like that save that convert boom okay let's blank this box too when it starts so it doesn't put it in there twice all right right here we'll say status box that's the second box is blank all right save it now when i run it there you just get one set all right now this should copy over perfectly into here well let's just go down here test it for a second paste all right yeah it looks nice see you got the quote there close that quote your line continuation character a tab in and then that all right want to put a command in front of it so that it doesn't look weird all right maybe put right here put the me dot record source equals or whatever all right right here put me dot record source equals or you could put your do command.run sql there if you want to alright save it hit convert now all you gotta do is copy this guy copy come over here right and then paste it in and look at that see that yeah maybe add this in one more we could put two vb tabs here if you want to right vb tab and vb tab all right you don't want that there though how about we even take another step and copy it to the clipboard for the user make that easy right you're getting lucky folks these are these are tricks that i was saving for the extended cut usually i show all these extra little tricks for the members but i'm giving you a bonus today since it's new year's eve i'm feeling generous there is going to be an extended cut with a lot more stuff in it but i was saving this stuff for the extended cut but let's do this let's say since we've statused it status box dot set focus let's move to the status box then do command dot run command ac cmd copy that copies whatever is in the current field to the clipboard all right just saved you a step all right save it come back over here hit convert boom and this is now in the clipboard just to prove it let me close this open it back up again let me grab this that that should now be in the clipboard okay paste it here hit convert boom it goes here copies that text now i should be able to come right back over here and just go paste boom see how easy that was all right one more thing i'm going to show you and what this is what happens if you actually have criteria in here that has quotes in it for example save changes yes for example whoops in here design view let's say you want to see all this stuff but you want to see it where the state is equal to new york okay and i'm going to hide that so we don't have to see double state all right that's what that show button does run this all right i just see one person but it's the person from new york now the sql has quotes in it right there that throws a wrench in the in the monkey works because if i copy that let's make this our new gotta put that in there all right those quotes are gonna cause a problem so let's close this save changes no all right let's go back into our main menu form now if i paste this in there and hit convert all right look what happens that's not quite right so if i copy this and go over here now and paste it look that's not right because this basically closes that quote so we have to just convert these guys over to double double quotes when we first process this line so what we're going to do is we're going to say right here all right s equals replace s find any set of double quotes and replace them with quote quote close that up i know freaky huh we're going to change any of those into double double quotes any double quotes that happen to already be in there i know it's crazy all right you ready let's test it now same stuff in here convert all right look at that looks good that should be in our clipboard now let's come over here and paste and look at that it's valid it works want to learn more in the extended cut for members i show you a bunch of new tricks for example we'll learn how to break up long lines sometimes these select statement lines can be super long i'll show you how to chop that up and break it up into smaller pieces so when it converts it looks like that then i'll show you how to load up a list box with all of the queries that are in your database so when you hit load query list or when this form opens this will show all of the queries in your database then i'll show you how to click on one of these guys and the sql from that query will immediately feed into this box so you don't have to copy and paste anything just build the query save it and then load it into this form then i'll teach you how to analyze the text with the chr and asc functions i'll just teach you how each character has its own ascii code behind it lots of cool stuff in the extended cut and remember silver member is an up get access to all of my extended cut videos how do you become a member click on the join button below the video silver members and up will get access to all of my extended cut tech help videos live video and chat sessions and other perks after you click the join button you'll see a list of all the different membership levels that are available each with its own special perks but don't worry these tech help videos are going to keep coming as long as you keep watching them i'll keep making more and they'll always be free if you enjoyed this video please give me a thumbs up and feel free to share it wherever you think it might help people who are interested in access make sure you subscribe to my channel which is completely free and click on the bell icon to select all to be notified every time i post a new video youtube no longer sends out email notifications when new videos are posted so if you'd like to get an email every time i post a new video click on the link below to join my mailing list click on the show more link below the video to find additional resources and links you'll see a list of other videos additional information related to the current topic free lessons and lots more now if you have not yet tried my free access level one course check it out now it covers all the basics of microsoft access it's over three hours long and you can find it on my website or on my youtube channel i'll include a link below you can click on and if you like level one level two is just one dollar and that is free for all members of my youtube channel at any level want to have your question answered in a video just like this one visit my tech help page and you can send me your question there if you have a specific problem you need help with or you'd like to discuss having a database built for your needs i do offer one-on-one consulting be sure to follow my blog and find me on facebook twitter and of course youtube once again my name is richard ross thank you for watching this tech help video brought to you by accesslearningzone.com i hope you enjoyed this video and you learned something today i'll see you again soon you

Channel: Computer Learning Zone

Views: 23,284

Keywords: microsoft access, ms access, #msaccess, #microsoftaccess, line breaks, vbnewline, vbtab, sql to vba, convert sql, convert to vba, line continuation character, runsql, recordsource, setfocus, copy to clipboard

Id: vt7hh3UJ34A

Length: 21min 0sec (1260 seconds)

Published: Thu Dec 31 2020