VBA ADODB.Recordset values disappear suddenly-CodePudding

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

CodePudding user response：

Some of the things you are doing seem verbose and redundant.

We can shorten the preamble using the .OpenRecordset command. The cleanup is also not necessary. Objects that have no references get garbage collected. If we use a With block that will ensure our recordset object has no object references when the Sub is complete.

Option Explicit

Public Sub FillData(ByVal query As String)
    On Error GoTo eh

    With CurrentDB.OpenRecordset(query, dbOpenDynaset)
        If Not (.BOF And .EOF) Then
            Do While Not .EOF

                'if i check here in debugger all fields have values 

                article = .Fields("ARTICLE")
                lst_nr = .Fields("LST_NR")
                desc = .Fields("DESC")
                CurrencyCustomer = .Fields("CUR_CUST")
                CurrencyPartner = .Fields("CUR_PART")
                
                'if i check here again, some lost the values. Also not all variables are populated...
                'processing values
                .MoveNext
            Loop
        End If

        .Close
    End With

eh:
    If err.Number <> 0 Then
        MsgBox Err.Description
    End If

    GeneratePdf
End Sub

CodePudding user response：

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
...