none
Microsoft.ACE.OLEDB.12.0 - System resources exceeded RRS feed

  • Question

  • Hello Support,

    We are using Microsoft.ACE.OLEDB.12.0 in our Excel addin to read the data ranges in the dataset. However, sometimes we do get the "System Resources Exceeded" error. We are using the following code to read the data range in the dataset:

                Dim objCmdSelect As OleDbCommand
                Dim objAdapter As OleDbDataAdapter
                Dim objConn As System.Data.OleDb.OleDbConnection
                objAdapter = New OleDbDataAdapter

                Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Helper.CurrentWorkbook.Path.ToString & "\" & Helper.CurrentWorkbook.Name.ToString + ";Extended Properties='Excel 12.0;IMEX=1;HDR=No;'"


                objConn = New System.Data.OleDb.OleDbConnection(strConn)

                objConn.Open()


                    'strRange = Helper.CurrentWorsheet.Name.ToString & "$A" & StartRowNo & ":" & "A" & LastRowNo
                    strRange =  "Sheet1$A8:A8"
                    objCmdSelect = New OleDbCommand("SELECT * FROM [" & strRange & "]", objConn)

                    objAdapter = New OleDbDataAdapter
                    objAdapter.SelectCommand = objCmdSelect
                    objAdapter.Fill(dsIRow)
                    objCmdSelect = Nothing
                    objAdapter = Nothing

    We tried closing and reopening the connection string just before reading the data, but still we are getting the same error. And strangely we are getting this error in some of the excel sheet and not all.

    Please can you guide us as to where we are going wrong.

     

    Tuesday, November 23, 2010 11:05 AM

Answers

  • You should be closing the connection after you are finished with the database operation. I don't see this in your code. In addition, it might help to know on which line of code this exception is generated.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by liurong luo Tuesday, November 30, 2010 7:36 AM
    Wednesday, November 24, 2010 2:51 PM

All replies

  • You should be closing the connection after you are finished with the database operation. I don't see this in your code. In addition, it might help to know on which line of code this exception is generated.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by liurong luo Tuesday, November 30, 2010 7:36 AM
    Wednesday, November 24, 2010 2:51 PM
  • Hello ranjana,

    You mentioned that this error happened "sometimes", would you please clarify the situation the error happens on?


    Best Regards,
    Roahn Luo
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com
    Friday, November 26, 2010 10:37 AM
  • -Hello Paul,

    We are closing the connection, just forgot to mention the same in the sample code. We are closing the connection immediately after the database operation is complete. ---------------------------------------------------------------- ------

    - Hello Roahn,

    The error happens on "objAdapter.Fill(dsIRow)" (please refer to the sample code which i have given before) line. We are reading data from various sheets of the workbook, but when we read a single cell data i.e. suppose a named range is $A3:$A3 then it give error saying "System resources exceeded". We tried closing and reopening the database connection just before reading this single cell, but still it gives error. Surprisingly the same code works if we are reading more than one cell data. Not sure what the single cell has to do with the data reading.

    Tuesday, December 7, 2010 8:02 AM
  • Unfortunately this error is rather generic and difficult to troubleshoot. Try disabling connection pooling to see if it makes any difference. To do so, just add "OLE DB Services = -2" to your connection string.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, December 7, 2010 1:44 PM
  • Hi Paul,

    Thanks for your reply, we did try disabling the connection pool but still we are getting the same error.

    Thursday, December 9, 2010 5:06 AM