SQL converter (1 Viewer)

[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 VBA & Back Again

This is an enhanced version of a utility, by Allen Browne which makes it easy to convert a query from SQL to VBA (or vice versa)

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/

 

[Pat Hartman]

There is no advantage to embedding static SQL in your VBA code. It is also less efficient. When a querydef is executed the first time, Access calculates an execution plan and saves it. Access then uses the saved execution plan. When you execute embedded SQL, Access cannot store an execution plan so it must calculate a new one each and every time you run the code.

Variables to not modify an execution plan so for this purpose:
Select ... From ... Where Somefield = " & Forms!yourform!somefield;

is static and therefore can (and in my opinion, should, be saved as a querydef)

The most common dynamic SQL you will create is that used for searching. You might have a complex form with a dozen options. Rather than making a complex querydef with a bunch of expressions like:
(SomeField = Forms!yourform!somefield Or Forms!yourform!somefield Is Null)
AND (SomeField2 = Forms!yourform!somefield2 Or Forms!yourform!somefield2 Is Null)
AND (SomeField3 = Forms!yourform!somefield3 Or Forms!yourform!somefield3 Is Null)
AND (SomeField4 = Forms!yourform!somefield4 Or Forms!yourform!somefield4 Is Null)
I create at least the Where clause using VBA. Saving a query with this type of criteria as a queyrdef is the reason that many people hate the QBE. Access rewrites this criteria to suit itself to make it easier to display in the design view and the result is monstrous.

For many years of writing embedded SQL in COBOL for DB2, i used to dream of a tool such as the QBE where I could point and click. Now that I have one, I am not too proud to use it Use your tools to make your job easier. If your query's criteria is so complex you can't create it easily with the QBE, then type it longhand in VBA. You don't always need to use one method. I name my querydefs so that I can tell whether or not I can reuse them. Some are intended to be reused and they get a "_base" suffix. It actually makes testing easier.

 

[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"

 

[Pat Hartman]

In my example with the &, the & does NOT become part of the string. VBA converts the concatenated value to make it part of the string. What gets executed is:
Select ... From ... Where Somefield =12345;

You don't use an & when building the querydef. You just reference the form control. Access handles passing it to the query just as the @ works in SQL Server

The same query built with a querydef is:
Select ... From ... Where Somefield = Forms!yourform!somefield;

I don't know what the rules are for embedded SQL since I rarely use it. I do know that there are limits when you put SQL strings in a RecordSource or RowSource rather than a querydef name or a table name. That method is buggy as well and it has gotten me more than once so I NEVER use SQL strings in my RecordSource and RowSource properties. Those are always querydefs unless the tables are trivial and I'm willing to just reference the table.

 

[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.