在遍历从Redshift数据库中提取的记录集时，VBA冻结 [英] VBA freezing while traversing recordset pulled from Redshift database

问题描述

我有这个VBA代码，可以从Redshift数据库中提取相对较少的记录(大约200条).

I have this VBA code to pull a relatively small number of records (about 200) from a Redshift database.

Dim cnDB As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim sqlContent As String

RS.ActiveConnection = cnDB
'RS.CursorLocation = adUseClient 'tried this, but results in recordcount "<data provider or other service returned an E_FAIL status>"
RS.Open sqlContent

x = 0
 For Each fld In RS.Fields
      With ws
        x = x + 1
        .Cells(1, x).Value = fld.Name
        colIndex.Add x, fld.Name
      End With
 Next fld

If Not RS.EOF Then
    RS.MoveFirst
Else
    Debug.Print "No Results Returned."
    Exit Sub
End If

'Populates row data
rN = 1
While Not RS.EOF
    rN = rN + 1
    With ws
        For Each fld In RS.Fields  'problem is occurring somewhere in here while processing 2nd record
            With .Cells(rN, colIndex(fld.Name))
                .Value = fld.Value
            End With
        Next fld
    End With
    RS.MoveNext
Wend

在我们从Vertica数据库迁移之前，此代码没有问题.根据我在研究问题时发现的一些信息，似乎我可能会遇到麻烦，因为游标在Redshift中的工作方式有所不同，但是如果这是问题，我不知道如何解决.

This code had no trouble before we moved from a Vertica database. Based on some information I found while researching the problem, it seems like I might be running into trouble somehow because cursors work differently in Redshift, but if that's the issue I don't know how to fix it.

在DSN连接选项中，我尝试使用使用声明/获取"而不是将整个结果检索到内存中"，但现在我真的只是掌握了吸管.我看不出有什么区别.

I tried "Use Declare/Fetch" instead of "Retrieve Entire Result Into Memory" in the DSN connection options, but I'm really just grasping at straws now. It didn't make a difference that I could see.

我直接针对Redshift db运行查询，它运行良好，返回了所有预期的记录.因此问题不存在.

I ran the query directly against the Redshift db and it's working fine, returning all expected records. So the problem isn't there.

在VBA中运行查询后，我看到一个记录集计数为-1，但是肯定有记录在里面.当我在遍历字段时观察记录集时，一切看起来都很好，直到有几个字段进入第二个字段为止记录集，然后我得到了圆形光标，并且无法无限期地执行任何其他操作.我必须杀死Excel并重新开始.

After the query runs in VBA, I'm seeing a recordset count of -1, but there are definitely records coming in. When I watch the recordset while looping through the fields, everything looks alright until a few fields into the second recordset, then I get the circle cursor and can't do anything else, indefinitely. I have to kill Excel and start over.

预先感谢您可能会给我的指导.

Thanks in advance for any pointers you might be able to give me.

推荐答案

感谢冻糕为您提供帮助！解决方案(我想-自从它再次开始失败-参见下面的评论)是先替换整个段

Thanks Parfait for your help! The solution (I thought - it's since started failing again - see comment below) was to first replace the entire segment

'Populates row data
rN = 1
While Not RS.EOF
    rN = rN + 1
    With ws
        For Each fld In RS.Fields  'problem is occurring somewhere in here while processing 2nd record
            With .Cells(rN, colIndex(fld.Name))
                .Value = fld.Value
            End With
        Next fld
    End With
    RS.MoveNext
Wend

使用

ws.Cells(2, 1).CopyFromRecordset RS

，然后我将查询中的浮点数转换为小数，将时间戳转换为日期(不确定是否必须同时进行两种转换，但是既然可以正常工作，我就不做任何修改了.)

, then I cast the floats in my query to decimals, and the timestamps to dates (not sure if both castings were necessary, but I'm not going to tinker with it now that it's working).

CAST(fieldThatWasFloat as DECIMAL(6,5)),
CAST(fieldThatWasTimestamp as DATE)

请注意，时间戳记丢失了一些细节，但这在这里并不重要.可以肯定的是，在日期格式上可以使用一些技巧来重新获得它.

Note that some detail is lost from the timestamp, but it wasn't important here. Pretty sure it can be regained with a little trickery on the date format.

这篇关于在遍历从Redshift数据库中提取的记录集时，VBA冻结的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！