Solved

Excel connections to Access refresh fail

Posted on 2013-11-27
5
531 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

867 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

17 Experts available now in Live!

Get 1:1 Help Now