Error when looping through Access queries "Too many client tasks"

[Sargad_Strut]

Hello friendly helpers,

I have run into a problem when looping through Access queries via VBA. I have multiple levels that I am looping through; the first table (query) consists of unique project leaders, I then loop through these to find associated projects, after which i loop through the projects to ultimately find details about items that each project leader should be aware of. That's the quick backdrop. Now to the issue.

The workbook has been working great for years, but recently, after adding more and more people and projects, the macro crashes. First I get a dialogue "Select data source", where MS Access Database is one of the options. Regardless of action (Ok or Cancel), I am presented with the following error message:
Run-time error '-2147217842 (80040e4e)':
[Microsoft][ODBC Microsoft Access Driver] Too many client tasks.
The debugger points to "ActiveWorkbook.Connections("Query from MS Access Database3").Refresh" (see full code below).

I put a counter in the code and it turns out it crashes on the 64th refresh, which does not sound like a coincidence. Here's one of the quieries in the loop:

Sub refreshActiveProjects_projektLedare2(projLeader As Integer)

    folderPath = Application.ThisWorkbook.Path
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
    connString = folderPath & "Master.accdb"

    With ActiveWorkbook.Connections("Query from MS Access Database3"). _
        ODBCConnection
        .BackgroundQuery = False
        .CommandText = Array( _
        "SELECT Projektledare2, ProjectName" & Chr(13) & "" & Chr(10) & "FROM Projects" & Chr(13) & "" & Chr(10) & "WHERE (Active=True) AND (Projektledare2=" & projLeader & ")" & Chr(13) & "" & Chr(10) & "ORDER BY ProjectName" _
        )
        .CommandType = xlCmdSql
        .Connection = Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=" & connString & ";DriverId=25;FIL=MS Access;MaxBufferSize=2" _
        ), Array("048;PageTimeout=5;"))
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("Query from MS Access Database3")
        .Name = "Query from MS Access Database3"
        .Description = ""
    End With
    ActiveWorkbook.Connections("Query from MS Access Database3").Refresh

End Sub

Can I close the connection somehow to avoid the issue - or do you have any other suggestions?

Thanks in advance.

 

[tlundrd]

Hi Sargad_strut,
I tried your "stupid thing", but it didn't work for me unfortunately.

I can't find much about this problem on Google so I guess it is a fairly new problem which for me indicates it could be some kind of update from MS to Windows or Office. Could MS be contacted in any way?

 

[jonceramic]

I have also been having this exact same issue. My coworker using the exact same files and scripts, does not. I also could not get "maintainconnection" to work in my string.

I went ahead and added it to Microsoft's forums and referenced this thread.

Community

Sometimes if you upvote and add additional information to those threads, they do get noticed. Sometimes.

 

[jonceramic]

Now my coworker, after a new update, is having the same issues I am.

 

[jonceramic]

A similar thread started by someone else who responded above with their possible solution. Error when looping through Access queries from Excel - "Too many client tasks" - Microsoft Q&A

 

[mercury97]

I was finally able to figure it out by adding maintainconnection=false to each of my data connections. As I mentioned, this had been working fine for me until about a month ago something must have changed in the default way data connections are opened and closed.

I wrote the macro below to update all of the data connections in my workbook.

Sub correctdataconnections()
Dim ws As Worksheet, qt As QueryTable
For Each ws In ThisWorkbook.Worksheets
For Each qt In ws.QueryTables
qt.Connection = qt.Connection & "maintainconnection=false;"
Next
Next
End Sub

Thanks,

I managed to get this to work with mine - initially I tried just adding the "maintainconnection=false;" to the end of my existing ODBC .Connection string but that didnt work.

Adding the sub above and running did however work as I guess it does need to be set on the querytable rather than the connection...

 

[ShaneGroff]

This was a bug in Microsoft Access, and has been fixed in Version 2302 for Semi-Annual channel (this update was available Oct 10, 2023), and fixed for Version 2305 available at the end of May for Current Channel.