Excel VBA - Loop through recordset

Question

I have an issue when looping through a recordset; here is the code:

    Dim query as String
    query = "SELECT * FROM test WHERE " & filter

    ' Declare variables'
    Dim objMyConn As ADODB.Connection
    Dim objMyCmd As ADODB.Command
    Dim objMyRecordset As ADODB.Recordset
    Dim objRst As ADODB.Recordset

    Set objMyConn = New ADODB.Connection
    Set objMyCmd = New ADODB.Command
    Set objMyRecordset = New ADODB.Recordset

    'Open Connection'
    objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyDB;User ID=test;Password=test"
    objMyConn.Open

    'Set and Excecute SQL Command'
    Set objMyCmd.ActiveConnection = objMyConn
    objMyCmd.CommandText = query
    objMyCmd.CommandType = adCmdText

    'Open Recordset'
    Set objMyRecordset.Source = objMyCmd
    objMyRecordset.CursorLocation = adUseClient
    objMyRecordset.CursorType = adOpenStatic
    objMyRecordset.Open

    Dim FindRecordCount As Integer
    If objMyRecordset.EOF Then
        FindRecordCount = 0
    Else
        objMyRecordset.MoveLast
        FindRecordCount = objMyRecordset.RecordCount ' In this case it returns 4
    End If

     Do Until objMyRecordset.EOF = True
        ' Get variables

        ...

        ' Move to next Record
        objMyRecordset.MoveNext
     Loop

When I check how many rows has the recordset , it returns 4, but goes only once through the loop. In this case, it should loop 4 times.

Rory · Accepted Answer · 2014-09-15 11:34:25Z

4

You used movelast before the loop so you'll need to movefirst if you want to actually iterate all the records.

answered Sep 15, 2014 at 11:34

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mike · Accepted Answer · 2016-06-20 07:56:42Z

1

You don't need to write "EOF=true" the "EOF" formula is true in exactly the same situations :)

answered Jun 20, 2016 at 7:56

Mike

111 bronze badge

Collectives™ on Stack Overflow

Excel VBA - Loop through recordset

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related