troubleshooting Question

ADODB Connection to Excel fails to read data for second (read-only) user

Avatar of ExcessAxel
ExcessAxelFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft ExcelMicrosoft Development
3 Comments1 Solution1562 ViewsLast Modified:
I have an Excel workbook with a user defined function to retrieve data from closed workbooks using ADODB. The function works for the first user that opens the main workbook, however when a second user opens the file, accepting the message that the workbook can only be opened Read-only, the retrieve function fails. The connection object is established correctly but the SQL to retrieve the recordset fails.

Windows 7
Main workbook native Excel 2010
Source workbook - 97-2003

Connection string as follows:
Provider=Microsoft.ACE.OLEDB.12.0;
User ID=Admin;
Data Source=U:\Clients\********\Dec 2013\ReadOnlyCrashInvestigation8Jan2014\***.xls;
Mode=Share Deny None;
Jet OLEDB:System database="";
Jet OLEDB:Registry Path="";
Jet OLEDB:Database Password="";
Jet OLEDB:Engine Type=35;
Jet OLEDB:Database Locking Mode=0;
Jet OLEDB:Global Partial Bulk Ops=2;
Jet OLEDB:Global Bulk Transactions=1;
Jet OLEDB:New Database Password="";
Jet OLEDB:Create System Database=False;
Jet OLEDB:Encrypt Database=False;
Jet OLEDB:Don't Copy Locale on Compact=False;
Jet OLEDB:Compact Without Replica Repair=False;
Jet OLEDB:SFP=False;
Jet OLEDB:Support Complex Data=False;
Jet OLEDB:Bypass UserInfo Validation=False;

SQL is simple "Select * FROM [SHEETNAME$Range]"

Any pointers would be greatly appreciated.

Attaching file will require a lot of anonymising.
ASKER CERTIFIED SOLUTION
Jerry Paladino

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros