Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Victoria @ WB
 
Posts: n/a
Default "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.
  #2   Report Post  
Bill Manville
 
Posts: n/a
Default

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

  #3   Report Post  
Victoria @ WB
 
Posts: n/a
Default

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.
  #4   Report Post  
Bill Manville
 
Posts: n/a
Default

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
incorporating live data from external source to work book Jess Excel Discussion (Misc queries) 0 February 10th 05 06:41 PM
change directory for refresh data TxRaistlin Excel Discussion (Misc queries) 0 February 7th 05 10:09 PM
Pulling data from 1 sheet to another Dave1155 Excel Worksheet Functions 1 January 12th 05 06:55 PM
populating data from excel into access Chris Links and Linking in Excel 1 December 15th 04 08:50 AM
Data population between excel and access - Errors on missed fields Chris Excel Discussion (Misc queries) 0 December 13th 04 12:55 PM


All times are GMT +1. The time now is 02:05 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"