CODEWITHSUNDEEP
vbaado
lamwaiman1988
lamwaiman1988

Reputation: 3742

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

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

Upvotes: 7

Views: 29498

Answers (3)

Big McLargeHuge
Big McLargeHuge

Reputation: 16066

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.

Upvotes: 0

Xavinou
Xavinou

Reputation: 802

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...

Upvotes: 2

RolandTumble
RolandTumble

Reputation: 4703

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.

Upvotes: 13

Related Questions