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.

 

[Sargad_Strut]

No ideas on how to solve this?

Thanks

 

[Candyman8019]

I wonder if the queries just aren't completing fast enough. Try adding a delay to your loop. For example:

Application.Wait(Now + #0:00:03#)

This would give each query 3 additional seconds before the loop runs the next query.

 

[Sargad_Strut]

I wonder if the queries just aren't completing fast enough. Try adding a delay to your loop. For example:

Application.Wait(Now + #0:00:03#)

This would give each query 3 additional seconds before the loop runs the next query.

Thanks for your reply. It would be quite disturbing if the final solution needed a 3 second delay for each refresh (total number of refreshes are > 100, and the code executes in 6 seconds before adding the delays). However to test your hypothesis I ran the test and the results are the same as without the delay; it crashes on the 64th refresh.

To add a bit more flavour to it. If I run from the top it crashes in the same spot every time (the 64th). If i then close the WB and start from the place it crashed it runs the rest without any issues (which is 43 query updates). If I then start from the top in the same session, it crashes on the 21st refresh, which means it is again the 64th iteration that crashes - and this time in a spot where it runs fine when I'm starting fresh from the top.

 

[sconser]

Any luck figuring this out. I am having the same issue. I had a project that had been working for years and then suddenly last week I am getting the same crash / "Select Data Source" dialog after the 64th refresh. Let me know if you have come across a fix.
Thanks

 

[Sargad_Strut]

No solution yet unfortunately. For now I have split it into two workbooks as a workaround (the first takes care of about 50 refreshes, and the rest is handled in the second WB). I can cope with that for now, but will need to find a proper solution for the longer term, as it might grow to hundreds of refreshes.

Sorry I couldn't help, let's hope someone finds the thread. Please post again if you find a solution elsewhere.

 

[sconser]

Thanks for the reply. Hopefully somebody figures it out. I was able to replicate it by doing the following with Excel:

1. connecting to a MS access table using Microsoft Query
2. Creating a macro that continuously refreshes the data within a for/next loop (code below)
3. When I run the macro, it fails on the 64th refresh

Sub TestUpdateQueryTable()
Sheets("Sheet1").Select
Range("A1").Select
For i = 1 To 100
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Next
End Sub

If I then try to refresh the data manually it still fails The only way to "reset" is to exit and restart Excel.
It's as if the data connections are never being closed. Perhaps it was a recent Windows or Office update that is causing it. This was working fine for me until sometime last week.

 

[sconser]

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

 

[tlundrd]

Hi,

I have followed this post since I have the same problem.

Does others than sconser tried the maintainconnection=false-solution? I can't make it work...

Can anyone tell me if I'm doing it right?

Sub HentStamData()
    
    With ThisWorkbook.Connections("SQL_ImpairmentStamData").ODBCConnection
    
        .Connection = "ODBC;DSN=MS Access Database;DBQ=" & "C:/database/test.accdb" & ";DefaultDir=" & strStiDatabase & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" & "maintainconnection=false;"
        
    End With
   
    ThisWorkbook.Connections("SQL_StamData").Refresh
    
End Sub

 

[Sargad_Strut]

Hi,

I have followed this post since I have the same problem.

Does others than sconser tried the maintainconnection=false-solution? I can't make it work...

Can anyone tell me if I'm doing it right?

Sub HentStamData()
   
    With ThisWorkbook.Connections("SQL_ImpairmentStamData").ODBCConnection
   
        .Connection = "ODBC;DSN=MS Access Database;DBQ=" & "C:/database/test.accdb" & ";DefaultDir=" & strStiDatabase & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" & "maintainconnection=false;"
       
    End With
  
    ThisWorkbook.Connections("SQL_StamData").Refresh
   
End Sub

I have been tearing my hair over this for a long time. Appreciate the input from sconser, but I personally never managed to solve the problem using that method. Tried both via VBA and directly under connection properties. I did however find a stupid thing that seems to work for me. If I go to the data-tab in Excel, and click on "Queries & Connections" (opens in the right pane) before running the macro, it does not crash. Not optimal but you could try that.