view recordset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Sorry, the previous post escaped from me.
I execute a SQL statement in code and would like to look at the recordset
returned in order to debug. How do I display the recordset? I prefer to do
this from the VBA screen, but anything will do.
TIA
 
How about (air code):

Sub DebugRecordset(rs As DAO.Recordset)
Dim f As DAO.Field
For Each f In rs.Fields
Debug.Print f.Name,
Next f
Debug.Print

If rs.EOF And rs.BOF Then
Debug.Print "No Records"
End If

rs.MoveFirst

Do Until rs.EOF
For Each f In rs.Fields
Debug.Print f.Value,
Next f
Debug.Print

rs.MoveNext
Loop
Set f = Nothing
Debug.Print "EOF"
End Sub

This code lacks any error handling. You can call it from code or from
the Immediate window if you are debugging. It places tabs between
fields, but those won't help much if the data has variable text length.
The debug window only holds 200 lines, so you may want to repeat the
field list at the bottom.

I hope this gives you enough to start with...

Kevin
 
Hi, Sam.
I execute a SQL statement in code and would like to look at the recordset
returned in order to debug. How do I display the recordset?
Click to expand...

One way to do this is to create a new query and name it qryTemp. Then paste
the following code in a standard module:

' * * * * * Start Code * * * * *

Public Function showRecSet(sqlStmt As String) As String

On Error GoTo ErrHandler

Dim qry As QueryDef

Set qry = CurrentDb().QueryDefs("qryTemp")
qry.SQL = sqlStmt
DoCmd.OpenQuery "qryTemp"
showRecSet = "Success"

CleanUp:

Set qry = Nothing

Exit Function

ErrHandler:

MsgBox "Error in showRecSet( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear
showRecSet = "Fail"
GoTo CleanUp

End Function

' * * * * * End Code * * * * *

Save the module and compile the code. The next time you need to debug with
the Recordset, press <CTRL><G> to open the Immediate window. Copy the SQL
statement and paste it into the Immediate window with a call to the function.
The following is an example (watch for word wrap -- should be three lines):

? showRecSet ("SELECT F1.FeeID, F1.Fee, SUM(F2.Fee) AS RunningTotal " & _
"FROM TblFees AS F1 INNER JOIN TblFees AS F2 ON F2.FeeID <=
F1.FeeID " & _
"GROUP BY F1.FeeID, F1.Fee;")

Then press <ENTER> to execute the function. If it's successful, then the
word "Success" will appear below in the Immediate window. If it fails for
some reason, then the word "Fail" will appear below in the Immediate window.

Move to the Access window to view the Recordset in a datasheet. When
finished viewing, close the query. (The code won't do it for you). That
way, the next time the function is executed, the temp query's SQL Property
will be changed, and then the temp query will open again. If the temp query
is open from a previous run of the function, the SQL Property will _not_
change, and the temp query will still display the former Recordset.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. (Only "Answers" have green
check-marks.) Remember that the best answers are often given to those who
have a history of rewarding the contributors who have taken the time to
answer questions correctly.
 
And what's wrong with this:

While Not RS.EOF
Debug.Print RS.Fields("F_Fieldname")
RS.MoveNext
Wend

Too easy?
Johan
 
Back
Top