A few weeks ago, out of nowhere, I started receiving the following error when I started Excel after a restart or resuming from a locked computer.
Run-time error ‘-2147467259 (80004005)
[Microsoft][ODBC Microsoft Access Driver] ‘(unknown)’ is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
I have a ton of add-ins that load at startup and more than a few of them connect to an Access database via ActiveX Data Objects (ADO). The offending code was simply establishing the ADO connection.
|
1 2 3 4 5 6 7 8 |
Sub ConnectToMRP() If gcnMRP Is Nothing Then Set gcnMRP = New ADODB.Connection gcnMRP.Open gsMRPConn End If End Sub |
The global constant, gsMRPConn, pointed to the file correctly. I was able to access the network share via Windows Explorer. But when I executed the code
|
1 |
?Len(Dir(gsMRPConn)) |
I got 0, meaning VBA couldn’t resolve the path. The fix for a while was to close Excel and restart. Sometimes one restart would fix it and other times it took up to five restarts. I was at a loss for why VBA couldn’t see the network share.
After much searching, I read something about offline files. I had no idea what offline files were or why I would want them. Apparently Windows makes a cache of network files locally so that I can access them when I’m not on the network. I typed “offline files” in the Win7 start menu and found the Offline Files dialog.
I disabled offline files and have not had the problem since. I still don’t know how offline files work. I was connected to the network, so there was no need for Windows to use files offline. And if it had a cache, I’m not sure why it didn’t use it. I guess Windows was working to sync offline files in the background, which is why it worked after some number or restarts. I’m just glad the nightmare is over.