Error Updating Multiple Queries

[jo15765]

I have about 10 worksheets in a workbook, each of which contain about 6 access queries (so 60 total queries). When I try to use the refresh all option I get the following errors:

[Microsoft][ODBC Microsoft Access Driver] Too many client tasks and when I click okay I get this
[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed


Is there a way for me to update all of the queries automatically? The way that I have been using as a workaround has been to just on each individual page click refresh for each individual query. VERY time consuming!

 

[Norie]

Yes.

It would involve a bit more work but not too much and it does allow you more control.

eg you could put the data anywhere in the workbook, you could use values from the worksheet in the queries.

Perhaps if you told us a bit more about the queries we could give some pointers.

 

[jo15765]

It's a workbook that contains 4 queries on the front page, and 2 queries on the back page. It's work stuff, and unfortunately I don't have a blank "template" here with me at home to show for reference. Basically in let's say cell B2,C2, and D2 all the way down to B4 and C4 and D4 it pulls one query. Then again let's say in B44, C44 and D44 it pulls another query and so on and so forth. I just pulled in the actual query because I didn't know about the ADO until it was mentioned.

I do have a question about the code that was provided...Why when I try to use the refresh all button within Excel I get numerous errors, but if I use the VB code to refresh all it works flawlessly? Why does one work and not the other?

 

[Norie]

Eh, that could be because nothing is being refreshed.

Using code like this is just getting the data, there's no link back to the database/query.

If you wanted to 'refresh' the data you would need to run the code again.

 

[jo15765]

I may not have detailed the steps I used for the code. What I did was I went into the VB editor, and went to the "This Workbook" tab. From there, I inserted a new procedure, debugged and saved. When I open the workbook, I go to Tools ----> Macro ---> Run Macro and select the name of my procedure, and it refreshes all sheets with no issues.

The question I was asking is why does this "macro" run with no errors of any sorts, but if I just try to use the refresh all command I got all those errors at the beginning of my post?