OleDb exception System.Data.OleDb.OleDbException (0x80004005): System resource exceeded

0

VB2013: I am getting the exception System.Data.OleDb.OleDbException (0x80004005): System resource exceeded. When I query an MS Access database.

Here is what I do in my code:

'Make the connection to connDB
Public connDB As OleDbConnection
connDB = New OleDbConnection
With connDB
     .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DbFile & ";Persist Security Info=True;Jet OLEDB:Database Password=xxxxxx;"
     .Open()
 End With

 'iterate through some 2500 obects. each object has a set of codes and we will get their description here
  GetSysDefinitions (list of codes for an object)

'Close the connection to connDB;

Public Function GetSysDefinitions(sysCodes As List(Of String)) As String
    Try
        'check to see if the db is available
        If connDB Is Nothing Then Return ""

        'set up the SQL to get the System Codes and Definitions
        Dim sCodes As String = "'" & String.Join("', '", sysCodes) & "'"
        Dim sql As String = "SELECT * " & _
                            "FROM SYS_Codes " & _
                            "WHERE CODE IN(" & sCodes & ") ; "
        Dim daLs As New OleDbDataAdapter(sql, connDB)
        Dim dsLs As New DataSet
        Dim dtLs As New DataTable
        daLs.SelectCommand.CommandTimeout = 60 '60 seconds for the command to timeout
        daLs.Fill(dsLs, "Sys")  '<=== Exception here at some point
        dtLs = dsLs.Tables(0)

        'do something with records returned
    Catch ex As Exception
        Debug.Print(ex.ToString)
    End Try
End Function

This all works great. At some point however I get the exception System.Data.OleDb.OleDbException (0x80004005): System resource exceeded at the line daLs.Fill. I just am not sure why resources are being exceeded and what I need to do to avoid this exception. Seems like I make one connection and then use it to make many queries. Should work right?

sql
vb.net
exception
resources
oledb
asked on Stack Overflow May 20, 2019 by sinDizzy

1 Answer

0

Thanks Çöđěxěŕ and Andrew Morton. Dont remeber where I got the code snippet but have been using it for years. I guess the difference is this time Im using that routine in a large number of calls. here is my updated code which I tested and no exceptions:

        Using daLs As New OleDbDataAdapter(sql, connDb)
            Using dtLs As New DataTable
                'fill in the DataTable
                daLs.Fill(dtLs)
                dtLs.TableName = "CoreSys"

                'check for how many rows were returned
                'parse out rows
            End Using
        End Using
answered on Stack Overflow May 20, 2019 by sinDizzy

User contributions licensed under CC BY-SA 3.0