Link to home
Start Free TrialLog in
Avatar of gerry_ocallaghan
gerry_ocallaghan

asked on

Excel 2007 - ADO - Provider=Microsoft.Jet.OLEDB.4.0 not working for Shared workbook

In a shared workbook that on startup contains only empty workbooks. At runtime [Sheet1$] is populated on the fly. However after this I try and establish an ADO connection using the Jet engine and peform a SELECT * FROM [Sheet1$].  My select statement cannot find any records on [Sheet1$] even though I can see them on the sheet.

This code will work fine on non-shared copy of the workbook.

Help would be very welcome.  Thanks.
ASKER CERTIFIED SOLUTION
Avatar of Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs)
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gerry_ocallaghan
gerry_ocallaghan

ASKER

Hi Webtubbs.
I found that executing the Save method will resolve this issue. However I dont want to perform this action as it's a shared workbook (a report) and I don't want subsequent users opening it and seeing results.
When I remove the shared workbook flag it will work as expected even without the Save method.
However when I mark as shared under Reveiw>>Share Workbook it will not work (unless I execute the Save method).

I read somewhere else that there is a known issue using Jet Provider for ADO on shared workbooks. However the article did not mention a workaround.

Thanks.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for comments experts. Your solutions are accurate but do not completely solve my issue.  I appreciate the advise though.