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.

 

[KitaYama]

I do something similar to @MajP, but don't see the benefit of deleting it.

Public Sub CheckSQL(Optional ByVal sql As String)

    Dim qdf As DAO.QueryDef
    Dim vw As Integer
    Const qryName = "qrySelect"

    Set qdf = CurrentDb.QueryDefs(qryName)
    qdf.sql = sql
    If Left(sql, 6) = "Select" Then
        vw = acViewNormal
    Else
        vw = acViewDesign
    End If
    DoCmd.OpenQuery qryName, vw

End Sub

Then Debug.Print sql shows the sql in immediate window and I only add a CheckSql.
If the sql is an action sql, it opens in design view instead of running it. I can change view to DataSheet manually to check the records that are going to be affected.


I don't have error trap because it's always been used in developing process of the DB and not in run time.
I don't see any privilege in handling errors. I prefer to have the Help & Debug Buttons on the error message given By Access, rather than a user defined message box.

 

[Pat Hartman]

How can I get the output of a VBA select statement to show it as I step through the code?

You can just look at it. If you are stepping through the code as you process a recordset, just either use debug.print or hoover over the variable names as you move them from one place to another.

If you are asking how to intercept an append query as it is running, you can't. You can only step through code loops. SQL is executed outside of the Access workspace because it is not executed by MSAccess.exe. It is executed by the database engine. Either Jet or ACE.