Convert Query SQL to VBA Code in Microsoft Access. Add Line Breaks, Quotes, vbNewLine, and 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.
LEVEL: Developer (SQL and VBA coding)
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?
BONUS FOR CHANNEL MEMBERS:
Silver Members and up get access to a 30 MINUTE EXTENDED CUT of this video which walks 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.
MEMBERS ONLY VIDEO:
• Convert Query SQL to V...
BECOME A MEMBER:
/ @599cd
LINKS:
Replace Function: 599cd.com/Replace
Me.RecordSource: 599cd.com/Reco...
Double Double Quotes: 599cd.com/Doub...
SetFocus: 599cd.com/SetF...
Intro to VBA: 599cd.com/Intr...
ADDITIONAL RESOURCES:
Get on my Mailing List: 599cd.com/YTML
FREE Access Beginner Level 1: 599cd.com/Free1
FREE Blank Customer Template: 599cd.com/FRL2
$1 Access Level 2: 599cd.com/1Dollar
TechHelp: 599cd.com/Tech...
Twitter: / learningaccess
en.wikipedia.o...
products.offic...
microsoft.com/...
Email Me: [e-mail]
WHAT DOES LEVEL MEAN:
My Access classes are divided into multiple levels. BEGINNER requires little working knowledge of Access. EXPERT assumes you know relationships and how to use functions. ADVANCED users know macros and events. DEVELOPER uses SQL and/or VBA programming.
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
QUESTIONS:
Please feel free to post your questions or comments below. Thanks.

[@scottaxton2513]

5-Stars! This video alone just saved me HOURS of work converting a db using macros from the 1990's to this century. Kudos and Thanks! Scott

[@599CD]

My pleasure, Scott.

[@johnmcfarlane8805]

Remarkable stuff from our favourite MVP!

[@599CD]

Technically "former" MVP. The Microsoft MVP award is an annual prize. I was awarded it in 2014 and 2015, but then due to health reasons I took some time off and wasn't actively involved in the tech community for a few years. Hopefully they'll reconsider me in 2021. I love helping folks, posting my videos, and answering emails, which is one of the things they consider.

[@timfulton6920]

Looks good and I love the concept. I hadn't done any lessons on SQL in VBA and formatting but this was easy to follow. I did find I ran info an error when trying to use the SQL statement with "CurrentDb.OpenRecordset" "Error Number: 3131- Syntax error in FROM clause." I found it was because my SQL statement didn't have a space at the end of the FROM Line. "FROM QRY_SO_ALL" & _ (Incorrect) "FROM QRY_SO_ALL " & _ (correct) I didn't see in the video a spot that addressed that in the video. I hope this helps anyone with a similar issue.

[@599CD]

Yeah that could be. It's a very "version 1.0" piece of software.

[@colinhursell9176]

Thank you! Really useful information!

[@599CD]

Glad you like it.

[@zoomingby]

The fact that Access creates SQL in such a way that can't be readily used in VBA is inexcusable and absurd.

[@mohammedalsarraj9963]

Great review

[@599CD]

Thanks

[@ssdusd]

Greate!. But how will you go back to the base query after changing RecordSource?

[@599CD]

I get a ton of questions every day, and I don't have time to answer them all here on KZbin. Feel free to submit your question on my website at: 599cd.com/AskYT

[@teemoto3923]

One the first functions i add to my dbs is one that adds vbnewlines and give it a short name. for example cr(2) returns two lines. Saves on typing

[@mafgeology]

Great 👍

[@599CD]

Thank you 👍

[@nairobi203]

This is like Parmesan Cheese on a nice plate of Spaghetti with Tomato sauce.... Perfect... -:)

[@599CD]

Don't fuggetta bout da meat BALLS!

[@ashishtrivedi8253]

With docmd.runqul statement or me.recordset statement error saying required sql statement... I search on net it says only update or similar statement can run by this .. select statement can't! I can see your doing it.. please tell me how? Thx in advance

[@599CD]

Without seeing your statements, it's impossible to tell what the problem is. I did have one user who discovered that if you use SPACES in your field or table names, my converter doesn't work. But my Rule #1 that I teach in Access Beginner Level 1 is DON'T use spaces in field or table names. :)

[@ashishtrivedi8253]

@@599CD sorry for late response on this but that point of time i was desperate ;) but finally i figured it out, Docmd cant execute select statement. If anyone wants to try that can use ADO and it will work.

[@dbdata9836]

thanks

[@599CD]

Welcome