We help IT Professionals succeed at work.

Excel connections to Access refresh fail

628 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!
Comment
Watch Question

aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
What 'kind' of connections?
How are you refreshing the links?
Why are you refreshing the links?

Author

Commented:
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.
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
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
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Worked but not a great solution
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.