none
Microsoft.ACE.OLEDB.12.0 Extended property READONLY=TRUE not working RRS feed

  • Question

  • I searched several times over the web and I don't see any concrete solution to my current situation.

    Is Microsoft.ACE.OLEDB.12.0 really reading (able to read) the excel file when it's already opened by some other user?

    I tried several ways by modifying it's extended property READONLY but no use... same warning always!

    System.Data.OleDb.OleDbException was unhandled by user code
      HResult=-2147467259
      Message=The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.
      Source=Microsoft Access Database Engine
      ErrorCode=-2147467259

    Any help?

    Wednesday, October 24, 2018 4:56 AM

Answers

  • Hi KIRANKUMAR SADHANALA,

    If possible, you can open the document for read-only access using the Open XML SDK.

    The following code example calls the Open Method:

    // Open a SpreadsheetDocument for read-only access based on a filepath.
        using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filepath, false))

    For more information, please refer the following link:

    How to: Open a spreadsheet document for read-only access (Open XML SDK)

    Hopefully it helps you.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Thursday, October 25, 2018 9:09 AM
  • Hi Lina,

    This morning I installed and tried.... but end up with IO Exception error "File is already being referenced or used by other resource...."

    I searched further and I got a suggestion --> need to create a file stream with read only mode and pass it to Open method instead of passing file directly.

    using (var fileStream = new FileStream(this.Variables.paramInputExcelFile, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
    {
         using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileStream, false))
         { 
              //your own logic
            }
      }
    

    I followed it and the final answer is --> Working!!!

    Thanks a lot Lina for your time, effort and valuable suggestions.

    Friday, October 26, 2018 8:17 AM

All replies

  • Hi KIRANKUMAR SADHANALA,

    >>It is already opened exclusively by another user

    This does sound as if even read-only access is indeed forbidden. Can you test how excel open the files?

    As Brett said, it must be handled by user rights controlled by the database administrator or by different cursor types of the record set.

    For more information, please see the following links:

    Open an Excel file in read-only mode using OleDb?

    How do I prevent Excel from locking files by default?

    Hopefully it helps you.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.



    • Edited by Lina-MSFT Wednesday, October 24, 2018 6:28 AM
    Wednesday, October 24, 2018 6:21 AM
  • Hello Lina,

    Thank you very much for the response. I have gone through it

    The first suggestion "As Brett Said..." --> it's more about access database related issue. I think, it's not applicable for my case.

    The second suggestion (the two links you provided) --> after modifying registry, it will make file open always read-only. At one point of time user will open in write mode to update it. During that instance if my application tries to read it then the existing issue likely to be repeated!

    At this moment I see the only option --> install office, add interop excel dll refernces and can open the excel worksheet in read-only mode!

    Any other suggestions appreciable!

    Wednesday, October 24, 2018 8:16 AM
  • Hi KIRANKUMAR SADHANALA,

    Please refer the following code:

    GlobalData.stringConExcelMat = "Provider=Microsoft.Jet.OLEDB.4.0;

    Data Source=" + GlobalData.ExcelMatPath + ";

    Extended Properties= \"Excel 8.0;HDR=NO;IMEX=1;READONLY=TRUE\"";


    For more information, please see the link as below:

    C# Excel File - I want to Read Only

    If it still does not work, I'm afraid there is no better solution to achieve it.

    Hopefully it helps you. Please feel free to ask any questions.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Wednesday, October 24, 2018 9:05 AM
  • Once again Thanks Lina.

    Yes I am aware of this Microsoft.Jet.OLEDB.4.0.... but I think it supports for older version of excel file.

    To read .xlsx I think we should go for Microsoft.ACE.OLEDB.12.0 provider.

    FYI, I already used several ways(modified extended properties) of using this ACE.OLEDB provider but no use!

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<<excel file path>>;Extended Properties="EXCEL 12.0 XML;HDR=YES;MODE=READ;READONLY=TRUE;";

    Note: I would like to mention here that the above connection string is syntactically correct (just copied from SSIS package and pasted here) and removed excel file path!



    Wednesday, October 24, 2018 10:06 AM
  • Hi KIRANKUMAR SADHANALA,

    Have you tried this solution?-->install office, add interop excel dll refernces and can open the excel worksheet in read-only mode.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.




    • Edited by Lina-MSFT Thursday, October 25, 2018 6:54 AM
    Thursday, October 25, 2018 5:53 AM
  • Hi Lina,

    Not yet, that will be my last option because It demands to install office wherever we deploy our product.

    Right now looking for any simple method/solution!

    Thursday, October 25, 2018 7:59 AM
  • Hi KIRANKUMAR SADHANALA,

    If possible, you can open the document for read-only access using the Open XML SDK.

    The following code example calls the Open Method:

    // Open a SpreadsheetDocument for read-only access based on a filepath.
        using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filepath, false))

    For more information, please refer the following link:

    How to: Open a spreadsheet document for read-only access (Open XML SDK)

    Hopefully it helps you.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Thursday, October 25, 2018 9:09 AM
  • Hi Lina,

    This morning I installed and tried.... but end up with IO Exception error "File is already being referenced or used by other resource...."

    I searched further and I got a suggestion --> need to create a file stream with read only mode and pass it to Open method instead of passing file directly.

    using (var fileStream = new FileStream(this.Variables.paramInputExcelFile, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
    {
         using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileStream, false))
         { 
              //your own logic
            }
      }
    

    I followed it and the final answer is --> Working!!!

    Thanks a lot Lina for your time, effort and valuable suggestions.

    Friday, October 26, 2018 8:17 AM
  • Hi KIRANKUMAR SADHANALA,

     

    Thanks for your asking. Please remember to mark the replies as answers if they helped and please help us close the thread.

     

    Thank you for understanding. If you have any question, or update, please feel free to let us know.

     

    I wish you a happy life!

     

    Best Regards,

     

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Friday, October 26, 2018 8:20 AM