I have a little problem with a macro I'm working at. I need to pull data from a database and then show it in an excel sheet. Everything work fine till the moment when I want to close the recordset . Then my application freezes and I have to kill the process from Task Manager.
Here is the code

Dim spBasicInfo As ADODB.Command
Dim rsBasicInfo As ADODB.Recordset

... 'some code
On Error GoTo err_place
    Set spBasicInfo = New ADODB.Command
    Set rsBasicInfo = New ADODB.Recordset
    With spBasicInfo
        .ActiveConnection = dbConnection
        .CommandType = adCmdText
        .CommandText = "Raport_contacte_ora"
        .CommandTimeout = 10000
        .Parameters.Append .CreateParameter("@year", adInteger, adParamInput, 4, objCmdEdit.Text)
    End With
    rsBasicInfo.Open spBasicInfo, , adOpenStatic, adLockReadOnly, adCmdStoredProc
    Do While ((rsBasicInfo.State And adStateOpen) <> adStateOpen)
        Set rsBasicInfo = rsBasicInfo.NextRecordset()
        If (rsBasicInfo Is Nothing) Then Exit Do
    If rsBasicInfo.EOF And rsBasicInfo.BOF Then
        Exit Sub
    End If
    Application.ScreenUpdating = False
    With rsBasicInfo
        i = 3
        Do While Not .EOF
               'some proccesing
    End With
    ... 'formatting

    If Err.Number <> 0 Then
         MsgBox "Error", vbCritical, "Raport_ore_locatie"
    Else: MsgBox "Success!", vbInformation, "Raport_ore_locatie"
    End If
    If (rsBasicInfo.State = adStateOpen) Then
        rsBasicInfo.Close 'here it stops
    End If
    Set rsBasicInfo = Nothing
    Set dbConnection = Nothing

I use Excel 2002 with Microsoft VB 6.3, MSSQL 2005.
The recordset has at least 10000 records(maybe helps to know).

Thank you,

Line 19 Do While Rs.EOF = False
Line 20 Starting here is where you should do "some processing"
Line 21 rs.movenext
Line 22 loop
Line 23 rs.close
Line 28 move to line 18 and put oppsite of it at line 24
Line 24 to 35 delete

Good Luck

This article has been dead for over six months. Start a new discussion instead.