Problem with adding records to an ADO recordset RRS feed

  • Question

  • Hi,

    could some please help me with the following problem. I have a function that returns an ADODB.Recordset. The function queries a local database, retrieves data from it, saves the data to a recordset and returns it to the calling procedure. Unfortunately, when the recordset is being populated with data it suddenly breaks without an obvious reason. I first thought that it might be related to the provider that gets the data from the local database so I decided to save the data to an array before passing it to the recordset. This proved that it was not related to the provider as the data retrieved by the provider is always saved to the array without any problems. The problem always occurs somewhere between the lines .AddNew and .UpdateBatch but at random lines. Occasionally (very rarely) all the data is saved to the recordset without any problems. In other words, if I run the macro 10 times, and I pass exactly the same query to the function (= the same set of data is returned), the problem will occur 8-10 times. Is something wrong with the recordset declaration? Or maybe there is some size limit? The 'biggest' data set returned by the query has some 50 000 rows. I use Excel 2007 SP2.

    Private Sub CreateReport() Dim sWhereClause As String Dim rsReportData As ADODB.Recordset Set rsReportData = GetReportData(sWhereClause, sProjectCode, dteDateFrom, dteDateTo) End Sub Private Function GetReportData(ByVal sWhereClause As String, ByVal sProjectCode As String, ByVal dteDateFrom As Date, ByVal dteDateTo As Date) As ADODB.Recordset Const sSOURCE As String = "GetReportData()" Dim iError As Integer Dim sQuery As String Dim cEdlQuery As EdlQuery Dim objConnection As ADODB.Connection Dim vaQueryData() As Variant Dim lCounter1 As Long Dim lCounter2 As Long On Error GoTo ErrHandler DisconnectFromServer Call ConnectToServer(iError, gsSERVER_NAME, msDATABASE_NAME) ' connect to the server If iError <> 0 Then MsgBox gsERR_DATABASE_CONNECTION_ERROR, vbOKOnly + vbExclamation, gsERR_DATABASE_CONNECTION_ERROR_MSG Exit Function End If sQuery = "" ' here goes a lengthy SQL query that uses the variables passed to the function, i.e. sProjectCode, dteDateFrom, etc. Set cEdlQuery = gEDLConnection.OpenQuery(sQuery, edlClientSnapshot, edlReadOnly, edlOptDontParse) ReDim vaQueryData(0 To 30, 0 To cEdlQuery.NumRows - 1) cEdlQuery.FetchFirst For lCounter1 = 0 To cEdlQuery.NumRows - 1 Step 1 For lCounter2 = 0 To 30 Step 1 vaQueryData(lCounter2, lCounter1) = cEdlQuery.ColumnNr(lCounter2 + 1).Char Next lCounter2 cEdlQuery.FetchNext Next lCounter1 cEdlQuery.Close ' Close the provider recordset Set gEDLConnection = Nothing 'Clear the provider connection DisconnectFromServer

    Set GetReportData = New Recordset With GetReportData .Fields.Append "Stanowisko kosztowe", adVarWChar, 74, adFldMayBeNull .Fields.Append "Konto księgowe", adVarWChar, 83, adFldMayBeNull .Fields.Append "Dziennik", adVarWChar, 49, adFldMayBeNull .Fields.Append "Dłużnik", adVarWChar, 84, adFldMayBeNull .Fields.Append "Wierzyciel", adVarWChar, 84, adFldMayBeNull .Fields.Append "Towar - numer", adVarWChar, 41, adFldMayBeNull .Fields.Append "Towar - opis", adVarWChar, 71, adFldMayBeNull .Fields.Append "Towar", adVarWChar, 104, adFldMayBeNull .Fields.Append "Grupa towarów", adVarWChar, 104, adFldMayBeNull .Fields.Append "Opis transakcji", adVarWChar, 71, adFldMayBeNull .Fields.Append "Data raportowania", adDate .Fields.Append "Miesiąc", adInteger .Fields.Append "Rok", adInteger .Fields.Append "Nr dowodu", adVarWChar, 31, adFldMayBeNull .Fields.Append "Nr faktury", adVarWChar, 19, adFldMayBeNull .Fields.Append "Magazyn", adVarWChar, 48, adFldMayBeNull .Fields.Append "Kategoria 1", adVarWChar, 204, adFldMayBeNull .Fields.Append "Kategoria 2", adVarWChar, 204, adFldMayBeNull .Fields.Append "Kategoria 3", adVarWChar, 204, adFldMayBeNull .Fields.Append "Waluta", adVarWChar, 14, adFldMayBeNull .Fields.Append "Klasa 1", adVarWChar, 104, adFldMayBeNull .Fields.Append "Klasa 2", adVarWChar, 104, adFldMayBeNull .Fields.Append "Klasa 3", adVarWChar, 104, adFldMayBeNull .Fields.Append "Klasa 4", adVarWChar, 104, adFldMayBeNull .Fields.Append "Zlecenie", adVarWChar, 18, adFldMayBeNull .Fields.Append "Wn", adDouble .Fields.Append "Ma", adDouble .Fields.Append "Wartość rzeczywista", adDouble .Fields.Append "Wartość budżetowana", adDouble .Fields.Append "Ilość rzeczywista", adDouble .Fields.Append "Ilość budżetowana", adDouble .CursorType = adOpenKeyset .CursorLocation = adUseClient .LockType = adLockBatchOptimistic .Open End With With GetReportData For lCounter1 = 0 To UBound(vaQueryData, 2) Step 1 ' ############### errors occur in the code below .AddNew "Stanowisko kosztowe", CStr(vaQueryData(0, lCounter1)) .Fields("Konto księgowe").Value = CStr(vaQueryData(1, lCounter1)) .Fields("Dziennik").Value = CStr(vaQueryData(2, lCounter1)) .Fields("Dłużnik").Value = CStr(vaQueryData(3, lCounter1)) .Fields("Wierzyciel").Value = CStr(vaQueryData(4, lCounter1)) .Fields("Towar - numer").Value = CStr(vaQueryData(5, lCounter1)) .Fields("Towar - opis").Value = CStr(vaQueryData(6, lCounter1)) .Fields("Towar").Value = CStr(vaQueryData(7, lCounter1)) .Fields("Grupa towarów").Value = CStr(vaQueryData(8, lCounter1)) .Fields("Opis transakcji").Value = CStr(vaQueryData(9, lCounter1)) .Fields("Data raportowania").Value = CDate(vaQueryData(10, lCounter1)) .Fields("Miesiąc").Value = CInt(vaQueryData(11, lCounter1)) .Fields("Rok").Value = CLng(vaQueryData(12, lCounter1)) .Fields("Nr dowodu").Value = CStr(vaQueryData(13, lCounter1)) .Fields("Nr faktury").Value = CStr(vaQueryData(14, lCounter1)) .Fields("Magazyn").Value = CStr(vaQueryData(15, lCounter1)) .Fields("Kategoria 1").Value = CStr(vaQueryData(16, lCounter1)) .Fields("Kategoria 2").Value = CStr(vaQueryData(17, lCounter1)) .Fields("Kategoria 3").Value = CStr(vaQueryData(18, lCounter1)) .Fields("Waluta").Value = CStr(vaQueryData(19, lCounter1)) .Fields("Klasa 1").Value = CStr(vaQueryData(20, lCounter1)) .Fields("Klasa 2").Value = CStr(vaQueryData(21, lCounter1)) .Fields("Klasa 3").Value = CStr(vaQueryData(22, lCounter1)) .Fields("Klasa 4").Value = CStr(vaQueryData(23, lCounter1)) .Fields("Zlecenie").Value = CStr(vaQueryData(24, lCounter1)) .Fields("Wn").Value = CDbl(vaQueryData(25, lCounter1)) .Fields("Ma").Value = CDbl(vaQueryData(26, lCounter1)) .Fields("Wartość rzeczywista").Value = CDbl(vaQueryData(27, lCounter1)) .Fields("Wartość budżetowana").Value = CDbl(vaQueryData(28, lCounter1)) .Fields("Ilość rzeczywista").Value = CDbl(vaQueryData(29, lCounter1)) .Fields("Ilość budżetowana").Value = CDbl(vaQueryData(30, lCounter1)) .UpdateBatch ' ############### errors occur in the code above Next lCounter1 End With ErrExit: On Error Resume Next Exit Function ErrHandler: On Error Resume Next MsgBox gsERR_LEAD_INFO_GENERAL & Err.Source & " (" & sSOURCE & ") " & Err.Number & " - " & Err.Description, vbCritical + vbOKOnly GoTo ErrExit End Function

    • Edited by Igor_M Thursday, February 5, 2015 10:45 AM
    Thursday, February 5, 2015 10:10 AM


  • Hi Igor_M,

    Theoretically there's no limit to the Recordset row count, unless you set the MaxRecords property.
    If you return the Recordset to the CreateReport method immediately when you get it from the ADODB connection, please make sure you keep the connection opened, or else you can't read the Recordset in the CreateReport method.

    I think there might be some errors in that code snippet adding new record to the Recordset. You can modify it like this:

    For lCounter1 = 0 To UBound(vaQueryData, 2) Step 1
    			' ############### errors occur in the code below
                .Fields("Stanowisko kosztowe").Value = CStr(vaQueryData(0, lCounter1))
                .Fields("Konto księgowe").Value = CStr(vaQueryData(1, lCounter1))

    Or use field list instead just like the code snippet in this document:


    UpdateBatch method is usually to post the new records into the underline database, in your case, actually you don't point the Recordset to any database. You actually use it like a Collection or Array.

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Caillen Sunday, March 1, 2015 12:13 PM
    Friday, February 6, 2015 9:59 AM