Solved

Excel connections to Access refresh fail

Posted on 2013-11-27
5
547 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

759 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