Microsoft.ACE.OLEDB.12.0 works on and Excel file stored on a network drive, not if the Excel file is run from local C drive.

I am stumped by this one. I have some VBA code in an Excel file that uses Microsoft.ACE.OLEDB.12.0 to retrieve a row from a SharePoint 2010 list. If I open the Excel file from a mapped drive and run the code, it works flawlessly. If I copy the working Excel file to my local hard drive and run the code, i get an exception "Invalid Argument" when the connection is opened.
LVL 1
J_MrazekAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Number of things it could be, but right off permissions pop into mind.  May sure you have access to the directory (and make sure you check the user that is actually running the process).

Second would be the connect string.....pathing is correct?

Jim.
J_MrazekAuthor Commented:
permissions are all set I think. we run it from the mydocs folder. connection string is also good, pointing to our in-house sharepoint server.

just for clarity's sake I want to point out that both the working and non-working instances are run from the same user, on the same machine & login. basically if I copy the non-working excel file to a mapped drive and then open from it the mapped drive, it works. no change in the excel file at all, just the location of the file. very weird.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I think I totally mis-read your question.

Let me make sure I go this straight:

1. You open Excel on the local station.

2. If you open the spreadsheet from a network drive, it works.

3. If you copy that same spreadsheet to the local drive, then open it, it fails?

First thing I'd do is zip the spreadsheet file, then copy the .zip to the local station and unzip.  This will give you a CRC check on the file and your assured it's 100% accurate.   While it's been quite some time, I've seen bad NIC drivers mess up a file.

Along the same lines; anti-virus.   A network file might not be getting scanned on open, but a local file might.   Try disabling anti-virus for the above test.

If that gets you now where, post the connection string your using and I'll think about it some more in the mean time.

Jim.
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

J_MrazekAuthor Commented:
Windows 8.1 64bit O/S. I disabled 3rd-party A/V (webroot), native windows defender already off.   Zipped, copied, extracted XLSM (macro-enabled) Excel with no luck. It's not an issue with macro-enabling, as all of the code runs inside VBA. Its been this way for years.

attached is a screen shot of the code, and error message. did a lot of research online. cant find an explanation or resolution of why the same file would work from a network drive. Error occurs when the connection is opened. tried both http & https, so i don't think it is a certificate issue. Not sure why the error is "invalid argument", as the arguments in the connection string do not change depending on the source of the file. my guess is that this is the closest error in the library. I believe that the network location is considered "trusted" (excel will at times prompt to trust a network file).

doesn't matter what server i place the file on, i can run the VBA from any mapped drive. We have a few Windows servers, and a Ubuntu Server, and they all work. Curiously, if i run from a working mapped drive, but instead use UNC naming convention to access the file ("\\<server>\<shared directory>"), then the VBA fails at the connection.

I am starting to wonder if it is perhaps an issue with the sharepoint server, and that SP is refusing the connection, though i cant imagine why it matters the location of the file, especially the C: drive.
VBA-Error.png
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<attached is a screen shot of the code, and error message. did a lot of research online. >>

 Certainly a puzzler....

<<Curiously, if i run from a working mapped drive, but instead use UNC naming convention to access the file ("\\<server>\<shared directory>"), then the VBA fails at the connection.
>>

hum, IE security settings come to mind.   Their more intertwined with Windows then most think.  Thing of it is, typically you more restricted with non-local files that local ones.

That's a good clue though...have to think about that.

<<I am starting to wonder if it is perhaps an issue with the sharepoint server, and that SP is refusing the connection, though i cant imagine why it matters the location of the file, especially the C: drive.>>

 SharePoint would see the connection request coming from your machine in either case.   Excel is still executing on your station either way.

Jim.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
J_MrazekAuthor Commented:
i checked IE, and the site is in the Local Intranet Zone (this comes from a GPO on the server), and I also looked at IE's security settings for that zone, and don't see anything that would disrupt the  connection as described.

I then also looked at Excel's Trust Center settings, and even toggled a few, to no avail. i also added the source folder of the local drive as a Trusted Location. Still no luck.

But, i ran the file locally from another PC, one that has Excel 2013 installed, and it is working flawlessly. I of course neglected to mention that some of the PC's, including mine, use Excel 2016. Barring some other difference on the working pc, my guess is that Excel 2016 has something to do with the problem. It appears that the Office 2016 installation drops a new version of ACEOLEDB.DLL, which is the Microsoft.ACE.OLEDB library file. There are a few versions in C:\Program Files (x86)\Common Files\Microsoft Shared, under the various Office XX folders, and my guess is that the PC picks the latest ACEOLEDB.DLL file if you open it locally, and an earlier version if you open the file from the network. Or some variation of that. This is probably due to how the various versions of ACEOLEDB.DLL installed are registered in registry. This will probably be addressed in an Office 2016 update.

so in the end, i guess the 90's called, and they are looking for our old friend, "DLL Hell".
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Sounds like, great detective work!

<<and my guess is that the PC picks the latest ACEOLEDB.DLL file if you open it locally, and an earlier version if you open the file from the network. >>

Seems odd that it would do that, but I know references do auto update to the latest and it may not be doing that in a specific case.

You can check that by opening the files as you are doing now, then opening the VBA editor and check the reference version that's listed.

Jim.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
BTW, I know Office 2016 has a number of issues, so I think your onto something in that regard.

Jim.
J_MrazekAuthor Commented:
I don't recall setting a point value,  but your IE comment is what led to the solution, so thanks for staying with me on this one!
J_MrazekAuthor Commented:
I ran into this exact issue again, when we migrated to a Sharepoint 2016 server, and when debugging the updated connection references (on a local machine), and getting "invalid argument". I spent days trying to track down my issue. Imagine my surprise when finding this article 4 1/2 years later as the most exact description of my problem, only to realize that this was MY post!.

so, same issue: if i run the file locally, no dice, opening the connection complains of "invalid argument". If i copy it to a network drive, then it works. There is no error when the connection opens.

On the machine(s) i was now testing, there is only one copy of ACEOLEDB.DLL, so my earlier supposition is now proven false. And, apparently not enough people have had this issue, because the file (version 16.0.4266.1001) hasn't been updated since 7/31/2015.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.