ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Links and Linking in Excel (https://www.excelbanter.com/links-linking-excel/)
-   -   "Refresh All" won't work with external Access data located on serv (https://www.excelbanter.com/links-linking-excel/17957-%22refresh-all%22-wont-work-external-access-data-located-serv.html)

Victoria @ WB

"Refresh All" won't work with external Access data located on serv
 
Hello:

I have a large report that has 104 imported external data links. These
links refer to 104 differnt Access queries for a file located on a shared
file server.

If I click on each link individually and select "Refresh Data" from the
data menu, no problem. But if I click on the "Refresh All" button on the
External Data tool bar I run into some problems.

After a second or two of "Connecting to data source" appears in my lower
left window, I receive an "Unspecified Error" error. When I click OK I get
the following window: Data file: "xyz" not found. Connect to "xyz" instead?
(yes, the exact same file name.)

When I click on "Yes", I'm taken to the following window: Data Links
Property. When I click on "Test Connection" it fails.

Any suggestions? Is there something about using a file on a server I need
to be careful about?

Thanks.

Bill Manville

I don't know, but it may be that Refresh All attempts to do all the
refreshes simultaneously and runs into trouble because there aren't
enough connections to the database.

The following macro should do each one in turn.
Does it work?

Sub RefreshEach()
Dim WS As Worksheet
Dim QT As QueryTable
For Each WS In ThisWorkbook.Worksheets
For Each QT In WS.QueryTables
Debug.Print "Doing " & WS.Name & "!" & QT.ResultRange.Address
QT.Refresh BackgroundQuery:=False
Next
Next
Debug.Print "All done"
End Sub

If it were to fail on one you could see which one in the immediate
window of the VB editor.
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup


Victoria @ WB

Bill:

It starts to run well and then it stops and I get the same error. The debug
returns at line:

QT.Refresh BackgroundQuery:=False

I suspect your suspicion about not enough connections to the database might
be correct. When I was refreshing each query seperately the other day, half
way through the error popped up. I shut down my Excel file and opened the
Access database. I activated the query in question and shut down Access.
When I returned to Excel and tried to refresh the same query, it worked again.

Do you know how to increase the connections to the database? Is it
something I need to discuss with my networking department?

Thanks for the help.

Bill Manville

Victoria @ WB wrote:
It starts to run well and then it stops and I get the same error. The debug
returns at line:

QT.Refresh BackgroundQuery:=False


Since that is the line that actually does the query, that will be where any
problems show up.

I suspect your suspicion about not enough connections to the database might
be correct.

Since the problem is still happening when we have forced the refreshes to be
sequential I have gone off that idea.

Is it always the same query that fails?
Is it a query that takes a long time and therefore might hit a timeout?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup



All times are GMT +1. The time now is 01:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com