none
ACE.OLEDB - multiple spreadsheet readers RRS feed

  • Question

  • Is it possible for several applications to read the same Excel spreadsheet via ACE.OLEDB provider simultaneously?
    I thought that 'Mode=Share Deny Write' should make it possible.
    Nevertheless, when trying to execute two instances of the code below, the second instance shows "It is already opened exclusively" error.
     string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Mode=Share Deny Write;Data Source=\\\\MyComputer\\MyShare\\MySpreadsheet.xlsx;Extended Properties=\"Excel 12.0;IMEX=1;HDR=Yes\"";
    
     OleDbConnection connection = new OleDbConnection(connectionString);
    
     connection.Open(); 
    
    
     Thread.Sleep(100000);
    
    
    System.Data.OleDb.OleDbException (0x80004005): 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.
    
     at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
    
     at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
    
     at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
    
     at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
    
     at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
    
     at System.Data.OleDb.OleDbConnection.Open()




    Tuesday, May 3, 2011 1:26 PM

Answers

All replies

  • I think what you have to do is open the Workbook in Excel and select the Share Workbook option on the Review tab.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, May 3, 2011 3:13 PM
  • Nope, didn't help.
    Tuesday, May 3, 2011 3:33 PM
  • What are the other applications that have the Workbook open? Is Excel one of them?

    Also, keep in mind that Excel is somewhat limited in functionality with respect to data access technologies such as Jet and ACE.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, May 3, 2011 6:14 PM
  •  

    No other applications. Just the above code run twice. Say, compile the above code as MyTestApp.exe and run it twice. The first process will successfully open the connection when the second one will crash with the above error.

    Tuesday, May 3, 2011 7:13 PM
  •  

    >Also, keep in mind that Excel is somewhat limited in functionality

    Unfortunately I couldn''t find any documentation describing ACE provider.

    But a lot of samples found in Internet contain Mode=Share Deny Write in connection string.

    My question is if it is a mistake and the ACE provider doesn't support concurrent reading of excel files.

    If it doesn't I'll workaround it by creating a copy of the file before opening it.

    But first I'd like to make sure that there is no better  solution.

     


     


    Tuesday, May 3, 2011 7:18 PM
  • Apparently the data access methods are not supported for concurrent multi-user access to an Excel file. You would be better off using an actual database instead.

    http://support.microsoft.com/kb/195951

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, May 3, 2011 7:41 PM