topic RE: OLEDB Connection String - ReadOnly File in Product Forum

OLEDB Connection String - ReadOnly File

Nandhakumar — Thu, 08 Feb 2024 06:25:00 GMT

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

RE: OLEDB Connection String - ReadOnly File

Mukeshh_k — Thu, 08 Feb 2024 07:21:00 GMT

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

RE: OLEDB Connection String - ReadOnly File

sastharpa — Sat, 10 Feb 2024 10:21:00 GMT

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

RE: OLEDB Connection String - ReadOnly File

Nandhakumar — Tue, 13 Feb 2024 02:17:00 GMT

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

RE: OLEDB Connection String - ReadOnly File

Mukeshh_k — Tue, 13 Feb 2024 11:29:00 GMT

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