ADODB Recordset fields access order gives empty values

Question

I got an SQL 2005 table with many (84 to be specific) fields (actually it is a query returned by a procedure) It looks like when I access recordset fields placed later then some field placed earlier becomes empty while server had actually returned a value for it Had anyone such problem? My solution is to put such disappeared field at the end of a table so when it is accessed later by a code (here VBA) its value is still accessible BUT I see it as a big problem in ADODB.Recordset 2.8 as I should not care about field order I know that question is not very specific but maybe someone had a similar issue?

user2370125 user2370125 · Accepted Answer · 2013-12-12T21:43:16

One way to to make sure the field values are there is to pass on the recordset to a array like (You will have to build your own connection function):

Function getStoredProcedure() As Variant

Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim conn As ADODB.Connection
Dim values As Variant

Set conn = getConn("Server", "Database")
Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "StoredProcedureName"
cmd.Parameters.Item("@TODAY") = today

Set rs = cmd.Execute
If Not rs.EOF Then
    values = rs.GetRows
Else
    Exit Function
End If

Set cmd = Nothing
getStoredProcedure= transposeArray(values)

End Function

From there you can always retrieve the values from the array. Otherwise, without seeing your code or understand what you are trying to do, I cannot tell if this is really an issue with ADODB because I cannot recreate this issue when pulling field items in any order I want such as: rs.Fields.Item(i).Value for i = any number in any order.

ADODB Recordset fields access order gives empty values

1 Answers