OleDB Connection string for reading an Excel file exclusively opened by other user

Question

Can anyone tell me OleDB Connection string to be used for just reading an Excel file exclusively opened by other user?

I tried following connection string which did not work for me:-

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Mode=Read;Extended Properties=\"Excel 8.0;HDR=YES;ReadOnly=true;\"";

This connection string throws following exception when is it opened by other user:-

The Microsoft Jet database engine cannot open the file ''.  It is already opened exclusively by another user, or you need permission to view its data.

NOTEs:

1. Excel file is kept on share so that multiple users can open it.
2. Provider used:- Microsoft.Jet.OLEDB.4.0
3. Excel file type:- MS Excel 97-2003 Worksheet
4. Copying a file is NOT allowed

Chessboard

If you only want to read from the file, you can create a copy of the Excel file first, and then remove after working with it. – Biannulate

Since multiple users are editing excel it would create issues if we copy the file. – Chessboard

Yes I tried it. 1st user loads copied file containing 100 records. Before he saves it another user adds one more records to the file and saves that file with 101 records. Now 1st user updates 100th records and saves copied file on the top of main file. So finally main file will not have 101 records. – Chessboard

Well, I said: If you only want to read from the file.... Of course it won't work if you are writing data into the Excel file too. – Biannulate

So is there any workaround to handle this scenario? – Chessboard

Answer 1

I would suggest that you use a background thread to retry opening a connection on an interval for some time, and when you get a connection to the excel datasource, read all your data into memory and immediately close the connection, so that other processes can access the file.

Enki