When loading data from mssql database in a recordset all fields get populated correctly. If I inspect the recordset in vba debugger all fields have values. As soon as I access one field f.e to assign the value to a variable, some of the field values just seem to disappear. When I then check in debugger the same fields have "Empty" as value. Any idea what the issue might be?
Sub FillData(query)
On Error GoTo eh
Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")
rs.Source = query
Dim cn As Object
Set cn = GetConnection()
Dim cnstr As String
cnstr = GetConnectionString()
cn.Open cnstr
With rs
.ActiveConnection = cn
.Open
If rs.EOF And rs.BOF Then
Else
Do While Not rs.EOF
'if i check here in debugger all fields have values
article = rs.Fields("ARTICLE")
lst_nr = rs.Fields("LST_NR")
desc = rs.Fields("DESC")
CurrencyCustomer = rs.Fields("CUR_CUST")
CurrencyPartner = rs.Fields("CUR_PART")
'if i check here again, some lost the values. Also not all variables are populated...
'processing values
rs.MoveNext
Loop
End If
.Close
End With
cleanUp:
If Not (rs Is Nothing) Then
If (rs.State And eState.adStateOpen) = eState.adStateOpen Then
rs.Close
Set rs = Nothing
End If
End If
If Not (cn Is Nothing) Then
If (cn.State And eState.adStateOpen) = eState.adStateOpen Then
cn.Close
Set cn = Nothing
End If
End If
GeneratePdf
Exit Sub
eh:
MsgBox Err.Description
GoTo cleanUp
End Sub
It seems, the ADODB.Recordset does not like it when fields are accessed in a different order when they were populated (SQL-SERVER). At least my recent tests pointed in this direction. As a workaround, i loop through the recordset and fill the fields in dictionarys and store the dictionarys then in a collection. This way a can still access the fields through their name property. As a bonus, I don’t have to keep the connection open as long as I need the recordset and can close it immediately after filling the collection.
...
Dim articles As New Collection
Dim article As Object
Do While Not rs.EOF
Set article = CreateObject("Scripting.Dictionary")
For Each fld In rs.Fields
article.Add fld.Name, fld.Value
Next
articles.Add article
rs.MoveNext
Loop
...