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:

BECOME A MEMBER:

LINKS:

ADDITIONAL RESOURCES:

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.

[scottaxton]

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

[johnmcfarlane]

Remarkable stuff from our favourite MVP!

[colinhursell]

Thank you! Really useful information!

[timfulton]

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.

[teemoto]

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

[ssdusd]

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

[ashishtrivedi]

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

[nairobi]

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

[zoomingby]

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