A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Links and Linking
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old March 17th, 2005, 01:17 AM
Victoria @ WB
external usenet poster
 
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  
Old March 17th, 2005, 02:20 PM
Bill Manville
external usenet poster
 
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  
Old March 30th, 2005, 11:59 PM
Victoria @ WB
external usenet poster
 
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  
Old April 1st, 2005, 08:03 AM
Bill Manville
external usenet poster
 
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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Accessing a databse thru a webpage (newbie question) Chang General Discussion 1 August 23rd, 2004 09:10 AM
Data from Excel to Access Database Design 2 August 20th, 2004 12:53 PM
Need help with Access decision aualias General Discussion 23 June 21st, 2004 02:04 AM
External Data refresh query BeSmart Worksheet Functions 0 April 30th, 2004 10:26 PM
Excel - Import External Data from Access VLeonard Setting up and Configuration 1 November 7th, 2003 06:59 PM


All times are GMT +1. The time now is 10:08 AM.


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