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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 05:59 PM |