All about those recordset objects

[berika]

I'm trying to do the following

• Send a query to a SQL Server DB
• Receive a dataset (in the form of a recordset object)
• Store all of the rows of a specific column of the recordset data into a dynamic array (which would require something like redim array(1 to recordset.recordcount).
• I can use the resulting array to make a report, since I know how to use arrays.

I understand the concept of a cursor, though I think much better with array indexes. The movenext movelast movefirst stuff makes sense, but I have no idea how to use it.

So far I have the following code, and it works, up until I want to receive the resulting data set as a recordset, and then put it into an array. I have exhausted my ability to google this issue.

Sub place_recordset_into_array()

[INDENT]Dim query_string As String
Dim array_size As String
Dim myarray() As String

[/INDENT]
        query_string = "SELECT DISTINCT [field] FROM [table]"

[INDENT]Call run_query(query_string)
[/INDENT]
        [INDENT]array_size = record_set.recordcount 'Gives me an error saying "Object Required"
[/INDENT]
        ReDim myarray(1 To array_size) 'I'm only getting 1 record when I look in the local variables window during debugger, even though there should be 5 or 6 records
[INDENT]
For i = 1 to array_size 'Haven't gotten this far yet, but I suspect it won't work.
[/INDENT]
[INDENT=2]myarray(i) = record_set.fields([field_name]),Item(i)
[/INDENT]
[INDENT]Next i
[/INDENT]


End Sub



Function run_query(query_string As String) As ADODB.Recordset

    Dim server_connection As ADODB.Connection
    Dim server_command As ADODB.Command
    Dim record_set As ADODB.Recordset
    
    Set server_connection = New ADODB.Connection
    server_connection.Open ("Provider='SQLOLEDB'; Data Source='[server name]'; Initial Catalog='[DB Name]'; Integrated Security='SSPI';")
    
    Set server_command = New ADODB.Command
    server_command.ActiveConnection = server_connection
    server_command.CommandText = query_string

    Set record_set = server_command.Execute(query_string)

End Function

 

[RoryA]

Welcome to the forum.

Currently your function doesn't return anything. It should read:

Function run_query(query_string As String) As ADODB.Recordset

    Dim server_connection As ADODB.Connection
    Dim server_command As ADODB.Command

    
    Set server_connection = New ADODB.Connection
    server_connection.Open ("Provider='SQLOLEDB'; Data Source='[server name]'; Initial Catalog='[DB Name]'; Integrated Security='SSPI';")
    
    Set server_command = New ADODB.Command
    server_command.ActiveConnection = server_connection
    server_command.CommandText = query_string

    Set run_query = server_command.Execute(query_string)

End Function

and your sub should declare the recordset variable:

Sub place_recordset_into_array()

Dim query_string As String
Dim array_size As String
Dim myarray

    Dim record_set As ADODB.Recordset

    Set record_set = run_query(query_string)

myarray = application.transpose(record_set.GetRows)


End Sub

 

[berika]

You live up to your reputation! However, I'm running into some new issues now.

When I run the "query_string" in SQL, I get 7 records. When going through the debugger, my function is only pulling one record, and record_set.RecordCount = -1 (which is both not what I wanted and doesn't make sense since I got at least one record). Here's a picture of the locals window during debug right after I run "Set record_set = run_query(query_string)":

 

[berika]

http://i895.photobucket.com/albums/...0-004F-44BB-9FB4-734901AD8D1B_zpsbidamfzg.png

 

[RoryA]

The recordcount won't be an accurate reflection until you move to the end of the recordset and back. How are you testing the number of records?