SQL converter

[Leo_Polla_Psemata]

Hi everyone
I have found and created the below code in which we can convert an sql statement to VBA.
I have added my owb line which i just want to replace the '
' S = Replace(S, "'", """")

Now, the question is , if we have a very very long sql statement, how could we break it into more lines by adding the " & _

Private Sub Status(S As String)

StatusBox = S & vbNewLine & StatusBox
DoEvents

End Sub


Private Sub Cnvre_Click()
        Dim S As String

            StatusBox = ""
            S = SQLtext
            S = Replace(S, """", """""")

Private Sub Status(S As String)

StatusBox = S & vbNewLine & StatusBox
DoEvents

End Sub


Private Sub Cnvre_Click()
        Dim S As String

            StatusBox = ""
            S = SQLtext
            S = Replace(S, """", """""")
'            S = Replace(S, "'", """")
            S = Replace(S, vbNewLine, """ & _ " & vbNewLine & vbTab & vbTab & """")
            S = "Me.RecordSource = """ & S & """"

        Status S
     
        StatusBox.SetFocus
        DoCmd.RunCommand acCmdCopy


End Sub

In the attached, there is a form with two fields, we paste sql in the first, click, we see the conversion on the second field

Code source

 

[Gasman]

Do not reinvent the wheel

SQL to/from VBA Converter Add-In

This Access add-in is an updated version of my SQL to VBA And Back Again utility which makes it easy to convert query SQL to a VBA string (or vice versa). Additonal functionality includes creating example code from the VBA string and temp queries to test the SQL / VBA output.

isladogs.co.uk

 

[cheekybuddha]

@Leo_Polla_Psemata, double-check the code you posted - there appears to be an 'End Sub' out of place

 

[strive4peace]

@Leo_Polla_Psemata, for converting SQL to VBA (or TSQL), here's another useful link:

https://accessusergroups.org/sql-converter/

As for breaking long lines, in the SQL I like to replace comma with line-break, Space(3), comma (since I like to put commas at the beginning of the next line so it's easier to add and remove lines). I also add line breaks before keywords (which I have a list of). It isn't perfect, but it's a good start.

I used to put TAB in the SQL instead of Space(3) but stopped doing that because now TAB seems to cause issues

 

[strive4peace]

@Leo_Polla_Psemata, adding on ... here is a presentation about the online SQL converter I mentioned from AccessUserGroups

Access SQL ➔TSQL converter tool by Ben Sacherich and Aaron Kogan, host Juan Soto (47:08)

I actually don't do it this way myself as I've written my own tools, but a good place to know about

here's the link to do it:
https://accessusergroups.org/sql-converter/

 

[gemma-the-husky]

I believe there is a limit to the number of &_ you can include in a string.

When I have wanted to exceed the limit, I have to do this to construct very long strings.

s = "stuff"
s = s & "stuff"
s = s & "stuff"

 

[CJ_London]

I use vba generated sql all the time. The time taken to generate the query plan is minuscule and an existing query plan can be inefficient if the data parameters can change - for example a query plan based on linked tables with a few records may be different and more efficient when in a production environment with thousands of records - but the plan is

But at the end of the day it is a matter of personal preference.