CODEWITHSUNDEEP
excelvba
Carlos80
Carlos80

Reputation: 433

VBA Recordset with varying records

I have a slimple loop code that pulls data from an SQL query. The SQL query itself only pull back Top 5 records. However depending on which account I'm looking at I won't always get 5 records sometimes its just one or two. I was wondering how I can get the code to just loop through what is there without erroring when it can't find row 3,4 or 5?

Thanks in advance

For i = 0 To 4

    Sheets("Exposures").Range("F36").Offset(i, 0).Value = rst2.Fields("Country")
    Sheets("Exposures").Range("I36").Offset(i, 0).Value = rst2.Fields("Value")
    rst2.MoveNext

Next i

Upvotes: 0

Views: 35

Answers (2)

Alex K.
Alex K.

Reputation: 175766

Loop, but only while there are available rows:

do while not rst2.EOF

  ...

  i = i + 1
  rst2.MoveNext
loop

Upvotes: 1

iDevlop
iDevlop

Reputation: 25252

You could use the Range.CopyFromRecordset Method. See here.
Dynamic, and probably faster than a loop.
I think I remember it has a bug if you have very large (above 2KB) text fields however. If it's not your case, go for it.

Sheets("Exposures").Range("I36").CopyFromRecordset rst2

Upvotes: 1

Related Questions