Solved Large cte problems

[Kingz]

Hi,

I'm reading in a long CTE SQL via text file, and replacing a few parameters via VBA-Code. When I try to look at the final SQL in the immediate window, I only get to see the bottom half of the statement. How can I see all of it?

 

[KitaYama]

Export it to a text file :

Declare PtrSafe Function apiShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
   ByVal hwnd As Long, _
   ByVal lpOperation As String, _
   ByVal lpFile As String, _
   ByVal lpParameters As String, _
   ByVal lpDirectory As String, _
   ByVal nShowCmd As Long) _
   As Long


Public Sub CreateTextFile(pth As String, ByVal Contents As String)
'
    Dim App As String
    Dim fso As New Scripting.FileSystemObject
    Dim txt As Object
    Dim var As Variant
 
    If pth = "temp" Then
        pth = Environ$("temp") & "\temp.txt"
   End If
 
    Set txt = fso.CreateTextFile(pth)
    txt.WriteLine (Contents)
 
    var = apiShellExecute(hWndAccessApp, "Open", pth, vbNormalNoFocus, vbNullString, vbNormalNoFocus)
 
End Sub

Instead of
Debug.Pring sql
Use
CreateTextFile "Temp", sql

You need a reference to Microsoft Scripting Runtime

 

[KitaYama]

Shorter version :

Public Sub CreateTextFile(pth As String, ByVal Contents As String)
'
    Dim fileNum As Integer
    fileNum = FreeFile
    Open pth For Output As #fileNum
    Print #fileNum, Contents
    Close #fileNum

End Sub

CreateTextFile "D:\mysql.txt", strSQL

 

[Kingz]

Export it to a text file :

Declare PtrSafe Function apiShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
   ByVal hwnd As Long, _
   ByVal lpOperation As String, _
   ByVal lpFile As String, _
   ByVal lpParameters As String, _
   ByVal lpDirectory As String, _
   ByVal nShowCmd As Long) _
   As Long


Public Sub CreateTextFile(pth As String, ByVal Contents As String)
'
    Dim App As String
    Dim fso As New Scripting.FileSystemObject
    Dim txt As Object
    Dim var As Variant
 
    If pth = "temp" Then
        pth = Environ$("temp") & "\temp.txt"
   End If
 
    Set txt = fso.CreateTextFile(pth)
    txt.WriteLine (Contents)
 
    var = apiShellExecute(hWndAccessApp, "Open", pth, vbNormalNoFocus, vbNullString, vbNormalNoFocus)
 
End Sub

Instead of
Debug.Pring sql
Use
CreateTextFile "Temp", sql

You need a reference to Microsoft Scripting Runtime

Thanks for the prompt reply and solution, but there seems to be something missing after the declare function.

There's a compiling error regarding the declare statement anyway.

 

[KitaYama]

Thanks for the prompt reply and solution, but there seems to be something missing after the declare function.

There's a compiling error regarding the declare statement anyway.

Use the one sugessted in #3.
It has no API

 

[DickyP]

To show my ignorance what does the TLA cte mean in this context. The only cte I know is a brain condition!

 

[Kingz]

Thanks guys! #3 did the trick.

 

[theDBguy]

To show my ignorance what does the TLA cte mean in this context. The only cte I know is a brain condition!

CTE is a SQL Server construct and stands for Common Table Expression

 

[GPGeorge]

To show my ignorance what does the TLA cte mean in this context. The only cte I know is a brain condition!

CTE in SQL Server Examples

In this article we cover what a Common Table Expression CTE is and how to write a CTE for doing selects, inserts, deletes, updates and more.

www.mssqltips.com

 

[cheekybuddha]

CTE is a SQL Server construct and stands for Common Table Expression

Hmmm...

Is it not simply an SQL construct?

Postgres CTE
MySQL CTE
Oracle CTE
SQLite CTE
... SQL CTE

 

[DickyP]

Thanks to the members who put me straight - I wonder when they started to be called CTEs - they were just 'WITH expressions' in my days of using major SQL variants remotely. (A long time ago). Using just Access SQL these days one forgets things like INTERSECT and EXCEPT which I used in days past.

 

[June7]

Other possible options:

UNBOUND textbox on form.

SQLView of query builder.

 

[Isaac]

I don't blame anyone who asked, though, as "reading in a CTE" is a very strange and distracting way to say, really, Reading in a dataset!
Made me wonder for a while too