VBA ADO Query Run-time Error: System Resources Exceeded

[bs0d]

I'm running an ADO query through Excel to insert up to 50k rows into a table. The query uses dSUM to get a running total for items by date, so it's likely that it is "calculation heavy". Even running directly in Access, it takes a moment to complete.

When running it in Excel however, I get the subject runtime error after exactly 10,051 records have been inserted.

What can I do in this situation? Here is my code:

sSQL = "INSERT INTO tmp_CUMS_by_Date ([item_KEY], [ReadingDate], [CUM1], [CUM2], [CUM3], [Username]) " _
& "SELECT [item_KEY], [ReadingDate], " _
& "DSum('value1','tblDailyReadings','[iteml_KEY]=' & [item_KEY] & ' And [ReadingDate]<=#' & [ReadingDate] & '#') AS CUM1, " _
& "DSum('value2','tblDailyReadings','[item_KEY]=' & [item_KEY] & ' And [ReadingDate]<=#' & [ReadingDate] & '#') AS CUM2, " _
& "DSum('value3','tblDailyReadings','[item_KEY]=' & [item_KEY] & ' And [ReadingDate]<=#' & [ReadingDate] & '#') AS CUM3, " _
& "'" & Module1.UserNameWindows() & "' AS [Username] " _
& "FROM [tblDailyReadings] INNER JOIN [tblItemInfo] ON [tblDailyReadings].[item_KEY] = [tblItemInfo].[item_IDX] " _
& "GROUP BY [item_KEY], [ReadingDate], [" & db_field & "]" _
& "HAVING ((([" & db_field & "]) =" & Chr(34) & ActiveSheet.cboGroupFieldList.Value & Chr(34) & " )) " _
& "ORDER BY [ReadingDate];"

    Set myConnection = New ADODB.Connection
    FilePath = "L:\db\history.accdb"

    'Proceed with INSERT:
    With myConnection
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Open "Data Source = """ & FilePath & """"
        .Execute sSQL, adExecuteNoRecords
    End With

    'Close connection
    If CBool(myConnection.State And adStateOpen) = True Then
        myConnection.Close
        Set myConnection = Nothing
    End If

 

[bs0d]

The specific error # is: -2147467259 (80004005), which I see others have associated with an automation error.

The error points to this line:

.Execute sSQL, adExecuteNoRecords

If I hold my mouse over each variable when in debug mode, the sSQL shows the query, and adExecuteNoRecords shows "128".

 

[bs0d]

On a final debugging note, I found that the code works perfect if the data set is < 10k rows.

Is there a way I can insert at 10k intervals? Might this resolve the issue? I'm open to ideas here. Thanks

 

[bs0d]

Anyone experience this before?

More testing results. If I limit the query to include a single DSum field, it errors out @ 30,167 rows . So that's about right, +/- 10,05x rows (max) per dSUM it seems in my case...

Taking half a step back, I felt running two queries seemed less memory intensive than rolling everything into one massive query (if that would even be possible)...

I don't think you can INSERT via the MS Query method... Might DAO offer any alternatives that may be more efficient in this specific case? Still open to any feedback. Thanks again

 

[bs0d]

Still looking for possible solutions. One of them is to ditch the idea altogether since (even if I was able to get beyond the error), the query takes a long time and I'm not certain what the end-user would get out of it any how... Especially if multiple users were to make the same request at nearly the same time.

 

[bs0d]

I haven't found a solution yet. Once so many records are involved, the error surfaces. If I use an MS Query instead, the error changes to a 1004 runtime error and points to the ".refresh: background query=false" line. I'm certain it's the same issue, just that the ADO method provides a more descriptive error.

As more records are added daily, the query will only take longer to execute. Even in Access, it now takes 2 minutes to complete (even after a compact & repair).

Would converting to a SQL database be a solution here? It's my understanding it's more efficient for larger data sets. But unknown if it would eliminate my error or not.

Also- any thoughts on error trapping this? Some will run if the record count for the criteria is small enough. So I'd like to keep the functionality, just catch the error and send a message to the user that the data set exceeds system resources.

THANKS