0

Hi,

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_Connection
    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
    Loop
           
    If rsBasicInfo.EOF And rsBasicInfo.BOF Then
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    With rsBasicInfo
        i = 3
        Do While Not .EOF
               'some proccesing
              .MoveNext
        Loop
    End With
    ... 'formatting
err_place:

    If Err.Number <> 0 Then
         MsgBox "Error", vbCritical, "Raport_ore_locatie"
    Else: MsgBox "Success!", vbInformation, "Raport_ore_locatie"
    End If
err_delete:
   'save
    
    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,
Ionut

2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by vb5prgrmr
0

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 topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.