Good day to all, I am also new in VB Programming. I am making a program that shows the data from SQL 2000 server database to a datagrid in VB and after that, export it to MS Excel.

The program runs like this, the user will select the range of date of the transactions they want to see, when they select a short range like from Jan. 1 to Jan 2, and it will produced only 200 to 1000 rows in the datagrid, there was no error. But when they are producing a large no. of data, the program got the "Run-time error '-2147467259 (80004005)': Data provider or other service returned an E_FAIL status." when the program hit the highlighted code below.

Sub GetData()
    If cboDeptDesc.Text = "Select" Then
        strSQL = " SELECT * FROM MOTIVATION_MIKE M "
        strSQL = strSQL & " WHERE M.TRAN_DATE >= '" & Format(txtDateFrom, "yyyy/mm/dd") & " '"
        strSQL = strSQL & " AND M.TRAN_DATE <= '" & Format(txtDateTo, "yyyy/mm/dd") & " '"
    Else
        strSQL = " SELECT * FROM MOTIVATION_MIKE M "
        strSQL = strSQL & " WHERE M.DEPT_DESC = '" & cboDeptDesc.Text & "' "
        strSQL = strSQL & " AND M.TRAN_DATE >= '" & Format(txtDateFrom, "yyyy/mm/dd") & " '"
        strSQL = strSQL & " AND M.TRAN_DATE <= '" & Format(txtDateTo, "yyyy/mm/dd") & " '"
    End If
        Set rsData = New ADODB.Recordset
        rsData.CursorLocation = adUseClient
        rsData.Open strSQL, RRString, adOpenKeyset, adLockOptimistic
        Set DataGrid1.DataSource = rsData
        lblrecords.Caption = rsData.RecordCount
        lblrecords.Refresh
        totalRec = Val(lblrecords.Caption)
End Sub

I hope to get a quick response to this problem, thanks so much in advance.

Best Regards,
Mike

Put a Breakpoint before this, and with the next statment the caption setting, hover over rsData.RecordCount and see if it shows a value in the tooltip that pops up.

If that behavior doen't take place, Highlight rsData.RecordCount, then right-click the highlight, and add it to the watch window.

Then look in the watch window to see if there is any value in the object, or if it is an error instead.

Thank you so much for your response Mr. Brian, I really appreciate it but, can you show me how to do it?