OLEDB Connection String - ReadOnly File

[Nandhakumar]

Has anyone used OLEDB query to an excel file in a readonly mode? Win10 platform

I have tried few possibilities but didn't work, please let me know if you have done so.

Connection string:

Extended properties: Mode=Read; ReadOnly=True; ReadOnly=1;

I have tried the aboved mentioned properties but did not work.

------------------------------
Nandhakumar C
Technology Consultant
SimplifyNext Pte Ltd
Singapore
------------------------------

[Mukeshh_k]

Hi @Nandhakumar - Set the connection string  like this - "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=""" & [File path] & """;Extended Properties=""Excel 12.0 Xml;HDR=No;ReadOnly=True;IMEX=1"""  and use this connection string to set the OLEDB connection.

let me know if you face any difficulties doing this.

------------------------------
Regards,

Mukesh Kumar
------------------------------

[sastharpa]

Hi @Nandhakumar, 

Yes able to work on a protected workbook on Windows 10. What is the error you are getting and what is your AccessDatabaseEngine version, mine is 2010 32bit?

Below connction strings, I had tried,

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""File fullpath.xlsx"";Extended Properties=""Excel 12.0 XML;HDR=YES"";"

Also, Extended Properties=""Excel 12.0 XML;HDR=YES;Readonly=True; 

------------------------------
L Ganesh Velayudham
------------------------------

[Nandhakumar]

Hi Mukesh,

I have tried the suggested changes, but no luck.

Here is the connection string and error message i get.

I'm using MS access db 2010.

I have the same query working for reading the file if it is not opened by users. just included the extended properties to have it read only but not working.

------------------------------
Nandhakumar C
Technology Consultant
SimplifyNext Pte Ltd
Singapore
------------------------------

[Mukeshh_k]

Hi @Nandhakumar - I have just tested this myself again - I kept the file open this time and then tried doing an OLDEB connection, open, execute and reading the file and it didn't throw me any such error -

try creating a test excel file with few columns and rows and repeating the steps and see if this happens to your local file as well -

------------------------------
Regards,

Mukesh Kumar
------------------------------