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!

 

[jo15765]

Update:

I also tried using this code for the Workbook:

Public Sub RefreshOnOpen()
ActiveWorkbook.RefreshAll
End Sub


But that also does not refresh all sheets?!

 

[Fazza]

as refreshing each query individually works, try some code for that. such as, for Excel 2003 & earlier

dim wks as worksheet
dim qt as querytable
 
for each wks in worksheets
for each qt in wks.querytables
qt.refresh backgroundquery:=false
next qt
next wks
 
set qt=nothing
set wks=nothing

in later versions the query table is slightly different & unfamiliar to me. so the code will need to be different for Excel 2007 / 2010 - it might be listobjects.querytables

 

[Fazza]

PS repeated querying will increase workbook size. suggest you have a backup of the file. then if the file size starts to increase or you have any instability the back up will 'save' you. you might also consider replacing the query tables with ADO queries and create a new result file via VBA. regards

 

[jo15765]

Luckily I am using Excel 2000 so that code worked like a charm!!! What is an ADO query, I am unfamiliar with that/those??

 

[Norie]

ADO is a way to connect to a database and run queries on it.

So you could use it to get the data you are getting with the querytables.

It gives you more control over what's happening and when it happens.

If you do use ADO then the data won't automatically update and you would need to run the code again.

 

[Fazza]

thank you, Norie. perfect

 

[jo15765]

But my data is not automatically updating as is, that is why I was needing some code that would do it. I am confused now...What is the difference between using ADO and a query in access like what I have done?

So in my googling it looks like there is ADO and DAO and maybe what I have been using is DAO? Does that sound correct?

 

[Norie]

You use ADO to connect to the Access database.

Then you can run SQL statements against it.

If you have existing queries it's straightforward and you don't need to know too mucj SQL.
Here's a very simple example.

It gets the data from MyQuery and puts it on Sheet1.

Option Explicit

Sub ADOExample()

Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
 
Dim strPath As String
Dim strQueryName As String
Dim strDB As String
Dim strSQL As String
 
strDB = "Database2.accdb"    ' change to your database name
 
strPath = "C:\"    ' change to the path where the database is
 
strQueryName = "MyQuery"    ' change to query you want data from
 
    Set conn = New ADODB.Connection

    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strPath & strDB & ";Persist Security Info=False"
 
    conn.Open
 
    strSQL = "SELECT * FROM [" & strQueryName & "]"
 
    Set rst = New ADODB.Recordset
 
    rst.Open strSQL, conn, adOpenStatic
 
    Worksheets(1).Range("A1").CopyFromRecordset rst
 
    rst.Close
 
    Set rst = Nothing
 
    conn.Close
 
    Set conn = Nothing
 
End Sub

 

[jo15765]

So instead of actually going to (something like these steps, i don't have access2000 on this computer) data, get external data and pulling in the query that way, you can use ADO to pull in the query that way? Am I somewhat understanding?