|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Convert Query SQL to VBA By Richard Rost 
6 years ago
Convert Query SQL to VBA Code. Add Line Breaks, Quotes, More. Do you copy a lot of SQL from the query designer SQL view over to the VBA editor to use in your code, and you spend a lot of time "fixing" the string, adding line continuation characters, adding quotes, etc. Well, in this video we'll make a little utility you can use to perform that task with one click. You can use this in a RunSQL statement or a Me.RecordSource change. John from Hancock, New Hampshire (a Silver Member) asks: 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 RunSQL command without doing all that manual editing? MembersI'll walk you through some additional modifications to the utility, including splitting long lines up, loading a listbox with all of the queries in the database, and one-click to copy the SQL to the editor textbox and convert it.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today! LinksReplace Function: https://599cd.com/Replace Addendums
ExtrasAlex's Online SQL to VBA Tool
IntroIn this video, I will show you how to create a simple tool in Microsoft Access to quickly convert SQL statements from the query editor into properly formatted VBA code, including all necessary quotes, line continuation characters, and tabs. You will learn how to use text boxes, command buttons, and the Replace function to automate this process so you can easily copy SQL into your VBA editor for RunSQL commands or record sources, saving you time and reducing manual errors.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, we are 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 are going to do it with one click. Today's question comes from John from Hancock, New Hampshire, one of my Silver members. He is 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 RunSQL command without doing all that manual editing? Yes, John, that can be a time consuming and cumbersome task if you have a query built. One of the things I teach in my developer classes is that I do not like having tons and tons of small queries in my database. You have big complicated ones you want to save, but if it is something small and relatively simple, I do not 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 will take those queries and we will put them directly in our VBA code using a RunSQL command. But you have to take that SQL that the query designer puts together for you, copy it over to VBA, then you have to put quotes around everything and separate out the variables and put line continuation characters on it if it is long. That can take a few minutes. 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 pastes, your job is done. Let me show you what I put together. We are going to start off with my blank customer template. You can download a free copy of this from my website. I will put a link down below in the description. Go grab it. This is the basic simple customer and contact database. We have got a customer list. We have got a customer form. Simple information. Customers with contacts. If you want to see contacts. I do not 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. Let's start with something simple. Let's just bring in the star and we will save this as my customer Q, my simple customer query. Now where do I find the SQL that Access generates? Go up to View and SQL View and that is what you have there. Let me copy that. Now I am going to drop that in Notepad for just a second. There is a sample Notepad. I am going to move this off the side of the screen. Move it over here. We will hang onto that for just a minute. We can close this. Now where would I use that you might be asking? I have 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 is in that query. Let's go to design view real quick. I will just copy and paste this guy. Let's put Filter in here. The query could be anything. This is just a simple example. But I want to change what records show up here by changing the record source. Right now it is just customer T. But I can come in here, right-click, build event. I do not want a macro. This has got a macro in it, so let's get rid of that macro first. Actually, let's give it a name: FilterButton. I like to give all my buttons a good name. Thank you, Alex, for teaching me that. For years, I just left them as Command9, but Alex kept on me to change those as it is bad code. He is right. Let's get rid of the event. There is an embedded macro in there. We are just going to delete that. Then I am going to hit the dot-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 is going to happen when you click that button. I am going to say Me.RecordSource = Now I could go CustomerQ, I could set it equal to CustomerQ and make a saved query. But what happens if I have a bunch of different filters I want to apply? I do not want to save those. I do not want to have to have 15 different queries in here for all this. That is where this comes into play. I want to put this SQL statement right in here. Then I do not need this query anymore. See how this works? Now I have a bunch of different stuff in here. To put that SQL statement in here requires this. I have to copy this and paste it here, but it does not come in very handy. Access makes the queries in multiple lines, which is nice, but now I have to get rid of that line break, put a quote on the end here. This is an extremely simple query. Sometimes you get ones that are multiple lines long. You have all kinds of other stuff, WHERE conditions, ORDER BY statements, and it takes a while to format this to get it nice and to fit in here. So what I would like is a tool where I can just say: Take this query text and convert it to what I need right here. You can also do this with DoCmd.RunSQL statements for things like update queries and append queries. Now if I come back into here, it is the same set of data. Then I go Filter. What happens if you want to say, select customer.*, from customer T order by last name? Sort it by last name, for example, and then go. See how it sorts them? That is why you want to have different filter buttons in here. I cover all this in my full classes. The point of this class is to say, how can we turn this quickly and easily into that? Let's go back to the main menu, design view. I have already got a status box here. If you have not watched the video where I build this customer template, go watch it. It is called the Blank Customer Template. There is 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 puts some stuff in a status box. That is what I want instead of message box things. It just makes it easier to get statuses and put stuff in there. So we are going to utilize that. Let's move the Hello World button up top. In fact, I am going to get rid of these buttons here. We do not need them. Let's put Hello World over here, and we are going to call this guy Convert. Now, I am going to take this and make it nice and big so we can see all the SQL in there. I do not think we need the main menu for now. Let's get rid of that too. We are not getting rid of my advertising logo. That stays on the screen. This guy is called StatusBox. Let's make a copy of this, and this is where we are going to paste our stuff into. So copy, paste. We are going to paste our SQL into this top box. Let's call this guy SQLText. SQLText. Let's just change the color a little bit. Let's make him yellow so we know the difference here. So we are going to grab this, copy and paste it into here, and then hit the button, Convert. Take the button right there, and then it will turn into this. We will format it properly. How do we do that? Manipulation. Let's close this. Save changes. Yes. Open it back up again so we have a fresh start. Now I am going to copy this stuff here from my clipboard. Copy, paste. In order to do this, you have to understand the concept of the VB NewLine. It is actually a combination of two characters: a carriage return and a line feed. It goes back to the old days with a typewriter. It would get to the end, you do a carriage return and a line feed. Line feed basically means you go to the next line, and a carriage moves the paper up. Then a carriage return sends the head back to the beginning. So it is NewLine and then carriage return or vice versa, it does not matter. That can be represented in Access with a constant called VBNewLine. It is actually a Chr(13) and a Chr(10). We do not have to worry about that right now. All we need to know is VBNewLine. That says go to a new line. So what we are going to do is replace this string everywhere we find a VBNewLine right here. We are going to replace that with a close quote, an ampersand, and an underscore character, because in here, we do not want to make this string super long. If Access made two lines, I want to make it two lines. So select customer.* like this. It is going to go where the VBNewLine was initially there, like that. So we are going to put close quote, ampersand underscore, that is the VB line continuation. Tab tab in here, and then the next line, we are going to have to put an open quote like that. When we are done with the whole thing, we will put a quote at the beginning and a quote at the end. We will get this string right here. How do we do that? We do it with a simple Replace function. If you have never used the Replace function, I have got lessons on that. I will put links down below in the description. So in our button, we are going to get rid of this. We do not need that. Let's Dim s as a string, temporary variable to hold all of our work in. Let's start off with s = SQLText. Let's grab the text from the text box and put it in s. I do not like working with stuff directly in text boxes. I like to take it and put it in the memory variables. It's just cleaner. For some reason, it works better. I have had problems. Here comes the Replace: s = Replace. What is the string we are replacing stuff in? s. What is the find string? I am looking for a VBNewLine. Access knows that is the Chr(13) and Chr(10). What am I going to replace it with? We are going to start off with inside of quotes, replace that with a close quote and underscore, and then a close quote for that. We need that VBNewLine there, and let's throw a VBTab in there too. VBTab is what tabs in. That is another character you need to learn. That's just a tab character. Now we have to close the quotes. Actually, this will be the open quote on the next line, so it is going to be quote, quote, quote, quote, quote, quote, close parentheses. Enter. I missed something in here. I got one too many right here. There. 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 was confusing for me at first too. It is called the Double Double Quote Problem, and I will put a link to that video down in the links down below. Now, when we are all done doing that, let's status s. Let's put it in there. That basically puts s in the next text box. Let's see what we get. Save it. Come back over here and hit Convert. Boom. I still got Hello World. Why do I still have Hello World? I have Hello World because I put this code in the wrong place. It is a tricky mistake. But I am not going back and redoing the video because you will make this mistake eventually too. I put this code in the wrong spot. I replaced that filter button click. You can tell right up here, CustomerListF. I have done this before. I guarantee you will do it too. So what we have to do is we are going to copy this stuff here. The filter button text is gone. We can redo that later if we want to. Come back over here, or you can use your Project Explorer if you want to open that up. I am not going to go that deep. Right-click, design view. Right-click, build event. Now I am right inside the HelloWorld button click. I am going to get rid of all this other code. We do not need it. Comment out this stuff here. Let's get rid of all that so it is cleaner. All we need is the Status function on top. There is Sub. Let's paste that code that we just took right there. That is the stuff we just wrote. Save it. Say yes. Close this guy. Close that guy. Let's open it back up again. Let's see if it works. Oh. Invalid use of Null. What happened? Debug. SQLText is Null. Why? Because I did not put anything in here. I closed the form and reopened it. I did not put any code in there. Again, a mistake you will make too. So we will just say right here: If IsNull(SQLText), then Exit Sub. Do not do anything. Now Convert. Nothing is happening. Why? Because there is no code in there. Let's go back over here. Grab this stuff. Put it in there. Now let's see if it works. Select customerT.*. Close those quotes. Ampersand underscore. Open the next line of quotes. There is a VBTab in there, but you do not see it in the text box. When you copy it, you will. Now we just have to add the opening quote there and the close quote there. That is easy to do. Come back over here. Here we will say, once we have done that, s = open quote & s & close quote. Like that. Save that. Convert. Boom. Let's blank this box too when it starts, so it does not put it in there twice. Right here we will say StatusBox (the second box) = blank. Save it. Now when I run it, there. You just get one set. Now this should copy over perfectly into here. Let's just go down here and test it for a second. Paste. It looks nice. See? You have got the quote there, close that quote, your line continuation character, a tab in, and then that. You may want to put a command in front of it so it does not look weird. Right here, put Me.RecordSource = or whatever. Or you can put your DoCmd.RunSQL there if you want to. Save it. Hit Convert. Now all I have to do is copy this guy. Click More here, and then paste it in. Look at that. See that? Maybe tab this in one more. We could put two VBTabs here if you want to. You do not want that there, though. How about we even take another step and copy it to the clipboard for the user? Make that easy. You are getting lucky, folks. These are tricks that I was saving for the extended cut. Usually, I show all these extra little tricks for the members, but I am giving you a bonus today since it is New Year's Eve. I am 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. Let's do this. Since we have statused it: StatusBox.SetFocus. Let's move to the StatusBox. Then DoCmd.RunCommand acCmdCopy. That copies whatever is in the current field to the clipboard. Just save that. Save it. Come back over here. Hit Convert. Boom. This is now in the clipboard. Just to prove it, let me close this. Open it back up again. Let me grab this. That should now be in the clipboard. Paste it here. Hit Convert. Boom. It goes here. Copy that text. Now I should be able to come right back over here and just go paste. Boom. See how easy that was. One more thing I am going to show you. What happens if you actually have criteria in here that has quotes in it? For example, save changes. Yes. For example, 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. I am going to hide that so we do not have to see double state. That is what that Show button does. Run this. I just see one person, but it is the person from New York. Now, the SQL has quotes in it right there. That throws a wrench in the monkey works because if I copy that (let's make this our new guy), put that in there. Those quotes are going to cause a problem. Let's close this. Save changes. No. Let's go back into our menu form. Now if I paste this in there and hit Convert, look what happens. That is not quite right. So if I copy this and go over here now and paste it, look, that is 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. We are going to say right here: s = Replace(s, Chr(34), Chr(34) & Chr(34)). Find any set of double quotes and replace them with double double quotes. I know, freaky, huh? We are going to change any of those into double double quotes. Any double quotes that happen to already be in there. I know, it is crazy. Ready? Let's test it now. Same stuff in here. Convert. Look at that. Looks good. It is valid. It works. Want to learn more? In the extended cut for members, I show you a bunch of new tricks. For example, we will learn how to break up long lines. Sometimes these SELECT statement lines can be super long. I will show you how to chop that up and break it up into smaller pieces, so when it converts it, it looks like that. Then I will 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 will show you how to click on one of these guys and the SQL from that query will immediately feed into this box so you do not have to copy and paste anything. Just build the query, save it, and then load it into this form. Then I will teach you how to analyze the text with the Chr and Asc functions, and teach you how each character has its own Asc code behind it. Lots of cool stuff in the extended cut. Remember, SilverMembers and up get access to all of my extended cut videos. How do you become a member? Click on the Join button below the video. SilverMembers and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. After you click the Join button, you will see a list of all the different membership levels that are available, each with its own special perks. But do not worry, these TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more and they will always be free. QuizQ1. What is the main purpose of the tool built in the video?A. To convert Access SQL into properly formatted VBA code automatically B. To export Access databases to Excel C. To generate SQL queries based on VBA code D. To create new database tables Q2. Why does the instructor prefer not to save every small query in the database? A. It makes the database cluttered and harder to manage B. It slows down database performance C. Access does not support many queries D. Queries cannot be edited once saved Q3. Which function is used to replace line breaks in the SQL text with VBA-compatible line continuation characters? A. Replace B. Left C. InStr D. Format Q4. What is VBNewLine in VBA? A. A constant representing a carriage return and line feed B. A function that removes new lines C. A command to start a new sub D. An object for new database records Q5. What character sequence is added in VBA to continue SQL code onto a new line? A. " & _" B. "+" C. ";" D. ":" Q6. What must you do when your SQL string contains quotes within criteria (e.g., WHERE State="NY")? A. Replace each double quote with two double quotes B. Delete all quotes C. Replace with single quotes D. Leave them as they are Q7. What happens if you try to format an SQL string to VBA without handling embedded quotes correctly? A. The code breaks with a syntax error B. It automatically handles it C. It saves as plain text D. The database crashes Q8. How does the tool help after converting SQL to VBA-compatible code? A. Copies the formatted SQL code to the clipboard for easy pasting B. Runs the SQL query automatically C. Saves the SQL as a new query D. Exports the SQL code to Excel Q9. What should you do before starting the conversion, according to the instructor? A. Make sure the source textbox contains the SQL text to convert B. Save all current queries C. Compact and repair the database D. Restart Access Q10. Which Access command is used to copy the formatted SQL to the clipboard? A. DoCmd.RunCommand acCmdCopy B. DoCmd.Copy C. Clipboard.SetText D. Application.Copy Q11. What quick fix is suggested if the input SQL text is null? A. Exit the subroutine without doing anything B. Display an error message C. Set the text to "SELECT * FROM Table" D. Use dummy data Q12. What is the benefit of putting the SQL string directly in VBA rather than referencing a saved query? A. Eliminates the need for multiple saved queries for different filters B. Queries run faster from VBA C. Access restricts query usage in VBA D. It adds extra database security Q13. In which scenarios might you prefer to use DoCmd.RunSQL with the converted statement? A. For action queries like UPDATE or APPEND B. Only for SELECT queries C. To create new tables D. For importing data from Excel Q14. In the extended cut, what is one of the additional features discussed? A. Loading a list box with all queries in the database B. Exporting queries to PowerPoint C. Auto-emailing query results D. Creating parameterized reports Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone covers a useful technique for anyone working with Microsoft Access: creating a tool that converts SQL statements from the Access query editor directly into VBA code with proper formatting. This greatly simplifies the process of taking SQL generated by Access and incorporating it into your VBA commands, such as with RunSQL, without the hassle of adding lots of quotes, line breaks, or continuation characters by hand.The inspiration for this tool came from a question submitted by John in Hancock, New Hampshire, who wanted to know if there was a way to format the SQL text of a query so that it would be ready for VBA's RunSQL command right away, eliminating all the manual editing. If you've used Access for any length of time, you know how tedious it can be to hand-convert Access SQL into a properly quoted VBA string. Access gives you clean, multi-line SQL in the editor, but when you need to put that in VBA code, you have to not only add quotes around the SQL, but also add line continuation characters for readability and handle any embedded quotes or variables. I teach in my developer classes that while you should keep complicated, important queries stored in your database, it isn't efficient to clutter your project with dozens of tiny one-off queries if you could just write those as strings in VBA. To address this, I've put together a tool that provides a simple solution: it allows you to paste in your SQL statement and with a single button click, it outputs a VBA-ready string, complete with quotes, concatenation operators, line continuations, and tab characters as needed. This saves a significant amount of time and reduces errors from hand-formatting. The starting point for this demonstration is my blank customer template, which you can download for free from my website. This template provides a basic customer and contact management database. It does not have any built-in queries, so I began by creating a simple customer query in the Access query designer and reviewed the SQL it generates. Normally, you'd grab the SQL from SQL View in the query designer, but if you want to use that SQL in VBA, you quickly find that it's not immediately usable. Access gives you line breaks and no quotes, but VBA requires all of that. For example, if you want to filter a form's records using an SQL string, you need to properly format the SQL for use in something like Me.RecordSource or DoCmd.RunSQL. Formatting by hand each time is tedious, especially for more complex queries. My approach is to create a simple form with a text box where you can paste your SQL statement, a second text box for the output, and a button to perform the conversion. For status feedback, I use a dedicated status box, which is just a text box for displaying any feedback or progress messages. The core of this tool is understanding how Access and VBA handle line breaks and string concatenation. In VBA, a line break in code can be accomplished with an underscore character and a line continuation, but in an SQL string, you need to replace actual line breaks (represented by the VBNewLine constant) with the proper combination of a close quote, space, ampersand, underscore (for line continuation), a tab (for indentation), and then an open quote. Two constants are worth knowing: VBNewLine stands for the carriage return and line feed, while VBTab inserts a tab character for readability. Handling this is straightforward with VBA's Replace function. First, grab the SQL from the text box and store it in a variable. Replace every occurrence of VBNewLine in that string with the necessary combination of characters that translates into a properly-formatted VBA string with line continuations and indentation. When finished, add another set of quotes at the beginning and end of the whole string so it is ready to paste into your VBA code. A common problem you might encounter is that Access SQL often contains double quotes inside WHERE clauses or other criteria. In VBA strings, you need to double these up to avoid syntax errors. This is fixed by replacing each double quote in the SQL with two double quotes as you process the string. I also added a convenient feature that copies the formatted VBA string to the clipboard automatically after conversion. Just paste your SQL from Access into the first text box, press Convert, and you can immediately paste your fully-formatted VBA string into your code module. Additional common mistakes, such as handling Null values if the text box is empty or making sure events are linked to the correct button, are addressed. These sorts of mistakes are common for developers of all experience levels. To round out the tool, I demonstrated how to handle embedded quotes in SQL criteria, ensuring your converted string is always valid VBA code. For those interested in more advanced features, in today's Extended Cut for members, I'll show how to break up particularly long SQL lines into multiple shorter chunks, making your code more readable. I'll also show how to automatically load a list of queries from your database into a list box on the form, and how to automatically pull the SQL from any selected query into the converter, eliminating even more manual steps. You'll also learn about encoding and analyzing text with the Chr and Asc functions for further insights into how text characters are handled in VBA. If you're interested in access to extended cut content, you can become a Silver Member or higher, which gives you entry to all extended cut videos, live sessions, and more. But don't worry: my core TechHelp videos will always be available to everyone for free as long as you keep watching them. You can find a complete video tutorial with step by step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListBuilding a tool to convert Access SQL to VBA codeFormatting Access SQL with quotes and line breaks for VBA Creating a simple query in Access Copying SQL from Query Design into Notepad Assigning SQL statements to a form's RecordSource in VBA Using a text box to accept SQL input in an Access form Manipulating text with VBNewLine and VBTab in VBA Using the Replace function in VBA for string formatting Handling double quotes in SQL strings for VBA compatibility Outputting formatted SQL to another text box Copying formatted SQL output to the clipboard via VBA Implementing error handling for Null input in VBA Adding line continuation characters in VBA-formatted SQL Testing formatted SQL by pasting into the VBA editor Generalizing the tool for different types of SQL statements Ensuring properly escaped quotes for SQL criteria in VBA |
||||||||||||||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access line breaks, vbnewline, vbtab, sql to vba, convert sql, convert to vba, line continuation character, runsql, recordsource, setfocus, copy to clipboard PermaLink Convert Query SQL to VBA in Microsoft Access |
