Create Array

[jo15765]

I am using ADO to query SQL Server and the result set that is returned I want to store in an arry. Should be pretty straight-forward, but for some reason, my count returns a -1? This is my syntax, what should I change in order to have the results store in the array? If I run my syntax directly against my SQL Server installation, there are 8 rows returned. What do I need to alter in order for the accurate data to be stored in the array?

Public Function PrintIT()
Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Dim f As ADODB.Field
Dim i As Integer
Dim conn As String
Dim arrResults() As Variant
Dim pth As Variant
Set c = New ADODB.Connection
Dim accapp As Access.Application
Set accapp = New Access.Application
With c
    .Provider = "sqloledb.1"
    With .Properties
        .Item("Data Source") = "Server"
        .Item("Initial Catalog") = "database"
        .Item("PassWord") = "password"
        .Item("User ID") = "userid"
    End With
    .Open
    Set r = c.Execute("SELECT DISTINCT(redlight) FROM [Database].[dbo].[Table]")
    i = 0
    r.MoveFirst
    Do Until r.EOF
        arrResults(i) = r.Fields(0)
        i = i + 1
    Loop
    r.Close
    Set rst = Nothing
    c.Close
    For Each pth In arrResults
		Debug.Print (pth)
    Next pth
End With
End Function

[ranman256]

arrays went away back in the 80s.
A recordset IS an array,
a collection is a smart array.

[jo15765]

arrays went away back in the 80s.
A recordset IS an array,
a collection is a smart array.

How would I take the recordset returned from my query and iterate? For example, let's say that my sql query returned

Red
Blue
Green
Orange

How would I iterate that, like I try to do in my foreach loop below?

[pbaldy]

Second the motion on arrays. The only time I use them is when I need the Split() function to break a string down. Your Do loop does the same thing as the For loop. You'd do what you need with the items there.

[jo15765]

Second the motion on arrays. The only time I use them is when I need the Split() function to break a string down. Your Do loop does the same thing as the For loop. You'd do what you need with the items there.

How do I access the elements in the returned result set? For example, in an array I would use

    
For Each dbPath In arrResults
    Debug.Print (dbPath)
Next dbPath

What would go here?

	Do Until r.EOF
        Debug.Print (?)
    Loop

[pbaldy]

Using your own code:

Debug.Print r.Fields(0)

[orange]

You can iterate arrResults using the following. LBound =lower bound Ubound=Upper Bound

For i =Lbound(arrResults) to Ubound(arrResults)
  Debug.print arrResults(i)
Next i

I often use arrays when creating test data. And with Split as Paul mentioned.

[jo15765]

Using your own code:

Debug.Print r.Fields(0)

This never leaves the 1st result returned from my select statement. How do I iterate all results returned?

    Do Until r.EOF
        Debug.Print r.Fields(0)
    Loop

[pbaldy]

Sorry, missed that you didn't have a MoveNext:

  Do Until r.EOF
        Debug.Print r.Fields(0)
        r.MoveNext
  Loop

[pbaldy]

And if that misses a record I always use:

Do While Not r.EOF