cancel
Showing results for 
Search instead for 
Did you mean: 

OLEDB Connection String - ReadOnly File

Nandhakumar
Level 6

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
------------------------------
4 REPLIES 4

Mukeshh_k
MVP

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.

19818.png

let me know if you face any difficulties doing this.



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

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

Regards,

Mukesh Kumar

sastharpa
MVP

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
------------------------------
VL Ganesh
Tech Arch RPA

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.

19819.png

19820.png



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

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 -

19822.png

19823.png



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

Mukesh Kumar
------------------------------
Regards,

Mukesh Kumar