How to run the output of Access vba SQL statement to a temp table

[BenMason]

I'm trying to step through VBA code to look at the data that is being created. How can I get the output of a VBA select statement to show it as I step through the code?

For instance, a select statement sSQL="SELECT ........
Is there a way to create a quick #Temp table that takes that vba statement to dump into a vba temp table to display the output?

I tried DoCmd.RunSQL sSQL but get an error that it requires an argument consisting of the sql statement

Please help.
Ben

 

[plog]

Debug.Print sSQL

That will print what's in sSQL to the immediate window. If you truly want to put it in a table you could make a table with one text field and then run an INSERT query to that table and put what's in sSQL into the table.

DoCmd.RunSQL "INSERT INTO DebugTable (DebugField) VALUES ('" & sSQL & "'")

 

[The_Doc_Man]

To expand on plog's answer, which was correct: Unlike UNIX, the Windows CMD prompt, and a few other operating system environments, there is no "visual shortcut" to redirect output of SQL. For CMD prompt, you could use > or < or >> to redirect the OUT, IN, or CMD (default) channels. But for SQL, you have to give an explicit destination for your output. Otherwise, your default (for a SELECT) is the GUI for Access in Datasheet view. Part of this stems from the fact that SQL isn't treated like a command-line interpreter, which therefore differs from CMD prompt, UNIX C:> prompt, or OpenVMS's DCL prompt. Therefore, you have to be explicit in what you tell it and WHERE you tell it to put things.

An INSERT INTO clause lets you do an INSERT INTO tablex (field list) FROM select-query. So you could think of this form of INSERT INTO query as a "wrapper" for your SELECT clause that you wanted to redirect.

 

[MajP]

I interpret this differently. You want to write a sql string in vba and see the results.

Public Sub ViewSqlString(StrSql As String)
    On Error GoTo ViewSqlString_Error
  Dim qdf As QueryDef
  DeleteViewQuery
  CurrentDb.CreateQueryDef "ViewQuery", StrSql
  DoCmd.OpenQuery "ViewQuery"
  CurrentDb.QueryDefs.Delete ("ViewQuery")
   
    On Error GoTo 0
    Exit Sub

ViewSqlString_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ViewSqlString, line " & Erl & "."
    DeleteViewQuery
End Sub
Public Sub DeleteViewQuery()
  Dim qdf As QueryDef
  For Each qdf In CurrentDb.QueryDefs
    If qdf.Name = "ViewQuery" Then
      CurrentDb.QueryDefs.Delete ("ViewQuery")
      Exit For
    End If
  Next qdf
End Sub

Public Sub Test()
  ViewSqlString "Select * from tbldemo"
End Sub

In this demo I can type a string and see the query.