Error when looping through Access queries from Excel - "Too many client tasks"

Scott Conser 5 Reputation points
2023-03-13T17:02:17.0566667+00:00

I have a workbook that has many MS Query connections to an Access database. The workbook has been working great for years, but recently it stopped working after the 64th refresh. 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.

I am 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. As I mentioned, this was working fine for me without any limitations (I was able to update the queries 100s of times without errors) until about a month ago.

Any help or insight would be appreciated.

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
304 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
2,204 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Dan Crist 0 Reputation points
    2023-03-14T16:23:21.44+00:00

    I am having the same issue with random spreadsheets that are connected via MS Query to independent spreadsheets. I tested the refresh table and it fails after 64 attempts. I have performed multiple tests on simple MS Queries and was able to refresh without fault using a loop macro from 1 to 100. Interestingly, if I have the "faulty" spreadsheet open while I run this separate MS Query spreadsheet it fails after 64 attempts. Also, even if I open the faulty spreadsheet and close it before running the simple refresh macro, it again fails.

    I was only able to resolve this by rebuilding the faulty spreadsheet over again and have had no issues. The problem is I just ran into another faulty spreadsheet and believe I will be running into others.