ADO connection to excel from Access wont be read only

[WilliamMathias]

Hi,

I'm using the Jet oledb provider 4.0 (I've also tried with
oledb provider for ODBC) to open a read only ADO connection to excel from access, and read a data range from a worksheet.

Everything works fine, but if another user has the workbook open, I get an error saying the file has been opened exclusively, and that the connection cannot be opened.

I've set the conn.mode property to readonly

Everything works fine if the workbook is opened with sharing.

If I try to open the workbook as a normal excel user and another user has it open exclusive, then I'm given the option of viewing a read only version, so why not from ADO?

Any help will be much appreciated,

Cheers,

 

[comaboy]

It's to do with the Database locking techniques. If you open an Access database, you'll notice an option to Open Exclusive. This basically cut others off from opening the database while you're there. It you open the database non-exclusively, you can then specify the locking techniques for the objects in the database. There are various kinds of lock from No Locks to locking the entire object.

What is happening here is the ADO is opening the sheet Exclusively. Try playing with Optimistic Locking in your connection string. That should solve your problem.

 

[WilliamMathias]

Thanks for your reply.

Can you/anyone give me an example of a connection string to excel that specifies a locking technique of ant kind?

thanks