locked
Ace Oledb provider issue RRS feed

  • Question

  • User-640323567 posted

    Hi,

    I am trying to get an excel file data stored on the server into a dataset using ACE oledb provider.

      var filenames = from fullFilename in Directory.EnumerateFiles(@"\\xxxxx\c$\temp\uploads", "*.*", SearchOption.AllDirectories)
                                    select Path.GetFileName(fullFilename);


                    foreach (var file in filenames)
                    {
    DataTable encounters = GetDataTableForCSV(@"\\itspscrmsb15\c$\temp\uploads", file);
    }


    private static DataTable GetDataTableForCSV(string csvDirectory, string csvFileName)
            {
    OleDbConnection dbConn = new OleDbConnection("Provider=Microsoft.ACE.OleDb.12.0; Data Source = " + csvDirectory + "; Extended Properties = \"Excel 12.0;HDR=YES;IMEX=1\""); dbConn.Open(); string dbName = "[" + csvFileName + "]"; string sql = @"select * from " + dbName; //OdbcDataAdapter dbCmd = new OdbcDataAdapter(sql, dbConn); OleDbDataAdapter dbCmd = new OleDbDataAdapter(sql, dbConn); DataSet dataSet = new DataSet(dbName); dbCmd.Fill(dataSet, dbName); dbConn.Close(); return dataSet.Tables[0];
    }

    But, it throws this error:

    Additional information: The Microsoft Access database engine cannot open or write to the file '\\xxxx\c$\temp\uploads'. It is already opened exclusively by another user, or you need permission to view and write its data.

    uploads is a folder on the server for storing excel files.

    Another thing is, if I store a csv file in the same folder and use Jet Oledb provider, I get no errors and it works perfectly. So, it doesn't seem to be a folder permission issue.

    OleDbConnection dbConn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + csvDirectory + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\"");

    What am I doing wrong? Why is it working for a csv file not for an excel?

    Please guide me through this. Thanks for any help.

    Wednesday, March 9, 2016 9:24 PM

All replies

  • User-986267747 posted

    Hi ASPbun

    Additional information: The Microsoft Access database engine cannot open or write to the file '\\xxxx\c$\temp\uploads'. It is already opened exclusively by another user, or you need permission to view and write its data.

    Because of security concerns, the ASP.NET worker process runs under the default ASPNET account. If you do not enable impersonation for an application, all of the threads that run the requests for that application run under the process account.

    I assume that this problem occurs because the ASPNET account does not have sufficient permissions to connect to or write to an Access database.

    Please try the following solution and check if it works for you. 

    1. Configure the ASP.NET worker process to run under the SYSTEM account in the <processModel> section of the Machine.config file.

    2. For security reasons, Microsoft recommends that you enable impersonation on your ASP.NET application. This method works if the impersonated user has necessary permissions to the computer and the database that you are accessing.

    3. Grant read and write permissions for the "Everyone" group on the database and the database folder. This method is not safe; therefore, Microsoft does not recommend this method.

    For more information, you could refer to:

    https://support.microsoft.com/en-us/kb/316675

    http://forums.asp.net/t/1929989.aspx?The+Microsoft+Office+Access+database+engine+cannot+open+or+write+to+the+file+

    In addition, if the solution above doesn’t work for you, I suggest that you could try to create an excel file on your local machine, and try to use ACE provider to access the excel and check if it is a ACE provider issue.

    Regards,

    Klein Zhang

    Thursday, March 10, 2016 7:27 AM