Solved

Excel connections to Access refresh fail

Posted on 2013-11-27
5
522 Views
Last Modified: 2014-02-04
Hi Experts,

I have a Large Excel file with multiple connections (around 60) to the same Access Database.

If i refresh them all one of them is failing with a "Unspecifid error". If i then test the connection that fails i get this message:
"Test connection failed because of an error in initializing provider"

If i then close down the Excel file and open it again, i'm able to refresh that connection without any problems.

And if I then again try to refresh all, I get the same error but now with another connection. It seams that after refreshing x-number of connection, the files fails to refresh any more.

Please help!
0
Comment
Question by:DCRAPACCESS
  • 3
  • 2
5 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 39682594
What 'kind' of connections?
How are you refreshing the links?
Why are you refreshing the links?
0
 

Author Comment

by:DCRAPACCESS
ID: 39682759
The connections are created by using get data from Access, function in Excel. Then I change the connection to "Read" mode.

Refresh are done by looping all connections i vba, and then refresh them one at the time.

I'm refreshing them each night so the users dont have to wait for 5 min each time they open the document. So Backgroundqurey are disabled.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39683401
I think you would be better off with 'static' data in your workbook.

Remove the links and let the nightly sync code do all the heavy lifting.

You will get good results if you use an ADO recordset object and the CopyFromRecord method.
I describe other methods  you can use in my Fast Data Push to Excel article:
http:A_2253.html
0
 

Accepted Solution

by:
DCRAPACCESS earned 0 total points
ID: 39689241
Hi aikimark,

I found another way around. I have split up all connections in 3 groups and then i open the report and run til first group then i close it. Then i open it again and then run the next group and so on...

That fixed the problem.

But frustrating that it is not able to refresh around 70 connection, because it keeps the connection open to the Access Database :-(
0
 

Author Closing Comment

by:DCRAPACCESS
ID: 39831964
Worked but not a great solution
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now