Solved Large cte problems

Kingz

Member
Local time
Today, 03:35
Joined
Mar 19, 2024
Messages
63
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?
 
Export it to a text file :
Code:
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
 
Last edited:
Shorter version :
Code:
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
 
Last edited:
Export it to a text file :
Code:
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.
 
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
 
To show my ignorance what does the TLA cte mean in this context. The only cte I know is a brain condition!
 
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
 
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.
 
Last edited:
Hi Kingz,

I see you’re having trouble viewing long SQL statements in the Immediate Window, which is a common challenge when working with complex or lengthy queries in VBA. I’ve faced similar issues myself, which led me to develop a solution that I call the Nifty Long Messages tool.

How It Works:
The Nifty Long Messages Tool is designed to help you manage and review long SQL statements that might otherwise get truncated when displayed in a standard message box or the Immediate Window. Here's a quick overview of how it operates:

Recording SQL Statements:
When you call the Nifty Long Messages function, it can record your SQL statements (or any other long text) into a dedicated table instead of trying to display them in a message box. This way, you don’t lose any part of the SQL statement due to length limitations.

Silent Mode:
You can choose to run the function in "silent" mode, where it records the statement directly to the table without any user interaction. Alternatively, if you need immediate feedback, it can display a message box with the SQL statement before recording it.

Query Creation:
After recording the SQL statement, you can automatically generate a query from the recorded data to quickly test your SQL. This helps in ensuring your SQL syntax is correct and functions as expected before deploying it in your main code.

Review and Debug:
By storing these long SQL statements in a table, you can easily review them, sort them, and even track changes over time. This method is especially useful when debugging complex queries, as it allows you to compare different versions or see exactly where something went wrong.

Next Steps:
I’ve created a sample database that includes the Nifty Long Messages system. --- SEE ATTACHED --- I encourage you to download it, experiment with it, and see how it could help you with your current project.

YouTube video tutorial:
I draw your attention to the video tutorial:-

Nifty Long Messages​


Which explains the system in more detail. Subscribing to my YouTube channel would be a great to me as I continue to share helpful tips and tools for Access developers.

Looking forward to hearing how it works out for you!

Subscribe to nifty access YouTube channel:- https://www.youtube.com/user/UncleGizmo?sub_confirmation=1

This post was generated by ChatGPT, from the combination of the OP's question, the transcript of the video, and my development notes, written, whilst creating the "Nifty Long Message Tool".

I added some specific instructions on how the blog should be compiled. The post was created within five minutes. The video took over an hour to create if you take into account the time spent editing. I might have to run this paragraph through ChatGPT to correct spelling mistakes and punctuation!
 

Attachments

Last edited:
Other possible options:

UNBOUND textbox on form.

SQLView of query builder.
 
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
 

Users who are viewing this thread

Back
Top Bottom