Corrupted Data mapped to Worksheet

[hatman]

I inheritted a workbook that performs data exchanges with an Access Database. Without getting into extraneous minutiae, one of teh code fragments I started with was:

Public Sub UpdateAll()
    Dim rs As ADODB.Recordset
 
...
 
    Do While Not rs.EOF
 
...
 
            Lab_WS.Range("A" & lngRow).Value = rs("ProgCustomer").Value
            Lab_WS.Range("B" & lngRow).Value = rs("Priority").Value
            Lab_WS.Range("C" & lngRow).Value = rs("Area").Value
            Lab_WS.Range("D" & lngRow).Value = rs("AreaPriority").Value
            Lab_WS.Range("E" & lngRow).Value = rs("TargetDate").Value
            Lab_WS.Range("F" & lngRow).Value = rs("ItemName").Value
            Lab_WS.Range("G" & lngRow).Value = rs("PartNumber").Value
            Lab_WS.Range("H" & lngRow).Value = rs("Op").Value
            Lab_WS.Range("I" & lngRow).Value = rs("Trace").Value
            Lab_WS.Range("J" & lngRow).Value = rs("EndItemAssembly").Value
            Lab_WS.Range("K" & lngRow).Value = rs("Status").Value
            Lab_WS.Range("L" & lngRow).Value = rs("Comments").Value
            Lab_WS.Range("M" & lngRow).Value = rs("HoldStatus").Value
            Lab_WS.Range("N" & lngRow).Value = rs("Who").Value
            Lab_WS.Range("O" & lngRow).Value = rs("Rig").Value
            Lab_WS.Range("P" & lngRow).Value = rs("StopDate").Value
            Lab_WS.Range("Q" & lngRow).Value = rs("CommitDate").Value
            Lab_WS.Range("R" & lngRow).Value = rs("ChargeNumber").Value
            Lab_WS.Range("T" & lngRow).Value = rs("Id").Value
 
...
 
        rs.MoveNext
 
    Loop
 
...
 
End Sub

Which I replaced with:

Public Sub UpdateAll()
    Dim rs As ADODB.Recordset
 
...
 
    Do While Not rs.EOF
 
...
 
            Lab_WS.Range("A" & lngRow & ":T" & lngRow) = Get_Record_Values(rs)
 
...
 
        rs.MoveNext
 
    Loop
 
...
 
End Sub

and added this function:

Function Get_Record_Values(rs As Recordset) As Variant
    Get_Record_Values = Array(rs("ProgCustomer").Value, _
            rs("Priority").Value, _
            rs("Area").Value, _
            rs("AreaPriority").Value, _
            rs("TargetDate").Value, _
            rs("ItemName").Value, _
            rs("PartNumber").Value, _
            rs("Op").Value, _
            rs("Trace").Value, _
            rs("EndItemAssembly").Value, _
            rs("Status").Value, _
            rs("Comments").Value, _
            rs("HoldStatus").Value, _
            rs("Who").Value, _
            rs("Rig").Value, _
            rs("StopDate").Value, _
            rs("CommitDate").Value, _
            rs("ChargeNumber").Value, _
            "", _
            CLng(rs("Id").Value))
End Function

The change was a part of my micro-optimization effort. For a few records, the time difference is non-existant, but for 500 records, the savings is several hundred milliseconds, and my database is only going to get bigger.

But there's a problem. When the routine runs on a blank worksheet, it works fine. But when data is updated in teh database by another client, and the routine pulls changed records from the database, the above code tries to OVER-WRITE data in the worksheet. The original code worked fine, but the new code shifts the data (2) cells to the right each time an over-write occurs. I stepped through the code (10) ways to Tuesday, and the recordset data is being mapped into the array properly... period. As soon as I map the array into the worksheet range, the data is inexplicably shifted to the right, even if I use the Range().ClearContents method first.

In the short term, I have re-released the application with the original code in place, but I just can't figure out WHY the second code option doesn;t work. I thought the array base number (1 versus 0) might be the problem, or the mapping of database nulls into the range object might be the source, but that doesn't seem to be a problem. I also can't seem to be able to reproduce this behaviour artificially outside of my application. Any attempts I have made to reproduce this in some other workbook has resulted in expected proper behaviour. Am I missing someting?