How to check all fields of the recordset from ADO query?

Question

I would like to see if there is a command to show the whole record(row) at once. By now I only find method to show individual columns. I am using a ADO connection to the ms access's mdb. Thanks. By the way, I don't know how can I print a message in MS Access's VB form.......does vb provide a console to show that? Debug.Print don't give me anything, I only success with MsgBox...

   With cmdCommand
    .ActiveConnection = conConnection
    .CommandText = "SELECT * from tableA"
    .CommandType = adCmdText
   End With

   With rstRecordSet
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockReadOnly
    .Open cmdCommand
   End With

   If rstRecordSet.EOF = False Then
        rstRecordSet.MoveFirst
        Do

            MsgBox rstRecordSet.Fields(0) & " " & rstRecordSet.Fields(1)

            rstRecordSet.MoveNext
        Loop Until rstRecordSet.EOF = True
   End If

score 13 · Accepted Answer · answered Mar 23 '11 at 18:34

13

First off, Debug.Print prints to the Immediate Window in the VB[A] Editor. If it's not showing, press Ctrl-G.

Second, there is no single command to show the whole record, you'll have to assemble it the way that Xavinou does in his (her?) answer. Here's the VB syntax, ignoring recordset creation & EOF check (Note that I've declared the variables--you are using Option Explicit, yes?):

Dim fld As Field
Dim msg As String

    For Each fld In rstRecordSet.Fields
        msg = msg & fld.Value & "|"
    Next

Debug.Print msg    'or MsgBox msg

I think the pipe ("|") makes a better separator than a space, since it's less likely to occur in your data.

answered Mar 23 '11 at 18:34

RolandTumble

4,633
3
32
37

1

+1 NB if you want to quickly inspect the contents of an entire recordset then you can dump the whole recordset on to an Excel worksheet using the `CopyFromRecordset` method of an Excel range object - see http://msdn.microsoft.com/en-us/library/bb223288%28v=office.12%29.aspx – barrowc Mar 24 '11 at 00:13
You can also get it with View | Immediate Window... or Customize your Toolbar with a button for it. – RolandTumble Mar 24 '11 at 18:18
Odd, but for me, I could not put `As Field` or `As String` there. Had to leave it off or I'd get an error: `Expected end of statement`. Worked otherwise. Kudos! – vapcguy Jul 20 '18 at 21:48

score 2 · Answer 2 · edited Feb 22 '13 at 02:12

2

For the output console, I don't know (since I don't know VB), but for showing the whole record at once, you can use a foreach loop on rstRecordSet.Fields.

In C#, I would write it like :

string msg = "";
foreach (Field f in rstRecordSet.Fields)
{
    msg += f.Value + " ";
}
MessageBox.Show(msg);

Now, you just have to find the VB syntax...

edited Feb 22 '13 at 02:12

BryanJ

8,485
1
42
61

answered Mar 23 '11 at 08:32

Xavinou

802
1
6
18

Big McLargeHuge · Answer 3 · 2018-09-19T20:55:33.177

0

Instead of building your own string, piece by piece, you can use the GetString method of the Recordset object:

Debug.Print records.GetString(adClipString, 1)

An unfortunate side effect of this method is that it seems to remove the record from the record set.

edited Sep 19 '18 at 20:55

answered Sep 18 '18 at 21:28

Big McLargeHuge

14,841
10
80
108

How to check all fields of the recordset from ADO query?

3 Answers3