How to resolve "too many client tasks" error when using MS Excel Driver to read worksheet into a data table

Community,

I acquired some VB code from this thread in order to read worksheet contents into data tables.

Public Shared Function ReadExcelIntoDataTable(ByVal FileName As String, ByVal SheetName As String) As DataTable
    Dim RetVal As New DataTable
    Dim strConnString As String
    strConnString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & FileName & ";"

    Dim strSQL As String 
    strSQL = "SELECT * FROM [" & SheetName & "$]"

    Dim y As New Odbc.OdbcDataAdapter(strSQL, strConnString)
    y.Fill(RetVal)

    Return RetVal
End Function

The code works the first 3-4 uses, but then this error occurs:

ERROR [08004] [Microsoft][ODBC Excel Driver] Too many client tasks.

<< IMAGE HERE >> enter image description here

I must exit/restart the application to continue development. Again after about four runs, the error occurs again and I must restart the app.

I conclude the connection is not closing and therefore results in "too many client tasks". A search on the error message also suggests issues with a closed connection.

Any advice on how to change the code to prevent the error would be appreciated

Researching the error message did not help to identify any specific methods to close the connection.

Solution

It looks like this is a new manifestation of a common issue. OdbcDataAdapter implements IDisposable (indirectly, in a base class a couple of levels up). Whenever you are using something that implements IDisposable, this is a signal that you must call Dispose when you are done with it. Usually it's because of some kind of resource allocation that needs to be released.

In this case, the right thing to do is to use a Using block which will take care of calling Dispose for you when exiting the block (and also take care of ensuring that it happens even in the event of an exception):

Using y = New Odbc.OdbcDataAdapter(strSQL, strConnString)
    y.Fill(RetVal)
End Using