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

Question

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.

Answer 1

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.

Answer 2

Hi Scott,

As I googled your query, I came across this forum thread answering your query.

If you're unable to view the marked answer, I'm writing it for you-

Data1.DatabaseName = "...." ' whatever is the path of your .mdb file
Data1.RecordSource = "SELECT * FROM ...... " ' whatever the query is, or let it be a whole table itself
Data1.Refresh
With Data1.Recordset  
 If .EOF Then    
  MsgBox "No macth found! "  
 Else    
  Do While Not .EOF      
  ' process individual attributes as ![AttributeName]      
   .MoveNext    
   End Loop  
  End If
 End With

Hopefully, this will help you out.

Best Regards.

Answer 3

I have the same problem, but using a Word Macro.

I think the problem is in the 64-bit ODBC Text driver.

I have opened a couple of issues with Microsoft, but have not received a helpful answer.

In my case, the 32nd time the macro does a mail merge, using the Microsoft 64-bit ODBC Text driver for MS Access, the error "Too many client tasks" appears. Perhaps each mail merge calls the ODBC driver two times?

My Word 2021 is 64-bit only and uses the 64-bit ODBC Text Driver. It cannot use the 32-bit ODBC Text Driver (or any 32-bit ODBC driver, it seems).

Attempted fixes:

1. Restart the PC. That didn't help.
2. Added code to close the connection from the macro, but that didn't help. The code:

ActiveDocument.MailMerge.DataSource.Close

1. Download MDAC 2.8 and install it. That didn't help.
2. Added a loop to add a one-second delay after every single Mail Merge. This makes the macro run a lot slower, but the same exact error happens at the 32nd Mail Merge every single time.
3. I got a trial of the Progress ODBC Text Driver. This product can do at least 100 mail merges and doesn't give the "too many client tasks" error. However, if you don't define the data type for each column, it can fail if it thinks a column is a datetime, and then fail if the data is not in the format it expects.

On May 3, 2023, I opened an issue with Microsoft Support. Request Number:1053009051

Title:Getting "Too many client tasks" from Access ODBC Text Driver - From Word Macro. I provided information to duplicate the issue. Microsoft mysteriously closed my issue without any comment and did not help me. I have since followed up with them and not received help so far.

Sincerely,

Victor Reinhart

Answer 4

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.