The Microsoft access database engine cannot open or write to the file error

Question

1.00/5 (1 vote)

See more:

I have tried to import an excel sheet to the database. I have tired the below mentioned code and i am getting error "

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.

"

The excel sheet is not opened anywhere while i am executing the code..

Please assist
Thanks in advance.

What I have tried:

private void ExcelConn(string FilePath)
{

    constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", FilePath);
    Econ = new OleDbConnection(constr);

}

private void InsertExcelRecords(string FilePath)
{
    ExcelConn(FilePath);

    Query = string.Format("Select [Account_name],[Key_Risks],[Mitigation_Plan],[Contingency_plan],[Status_in_detail],[Flag],[Rdate],[LableDate],[Pool_name],[Group_name],[Lastupdatedate] FROM [{0}]", "Sheet1$");
    OleDbCommand Ecom = new OleDbCommand(Query, Econ);
    Econ.Open();

    DataSet ds = new DataSet();
    OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);
    Econ.Close();
    oda.Fill(ds);
    DataTable Exceldt = ds.Tables[0];

    SqlConnection con = new SqlConnection(cs);

    //creating object of SqlBulkCopy
    SqlBulkCopy objbulk = new SqlBulkCopy(con);
    //assigning Destination table name
    objbulk.DestinationTableName = "ERP_Mgmt";
    //Mapping Table column
    objbulk.ColumnMappings.Add("Account_name", "Account_name");
    objbulk.ColumnMappings.Add("Key_Risks", "Key_Risks");
    objbulk.ColumnMappings.Add("Mitigation_Plan", "Mitigation_Plan");
    objbulk.ColumnMappings.Add("Contingency_plan", "Contingency_plan");
    objbulk.ColumnMappings.Add("Status_in_detail", "Status_in_detail");
    objbulk.ColumnMappings.Add("Flag", "Flag");
    objbulk.ColumnMappings.Add("Rdate", "Rdate");
    objbulk.ColumnMappings.Add("LableDate", "LableDate");
    objbulk.ColumnMappings.Add("Pool_name", "Pool_name");
    objbulk.ColumnMappings.Add("Group_name", "Group_name");
    objbulk.ColumnMappings.Add("Lastupdatedate", "Lastupdatedate");
    //inserting Datatable Records to DataBase
    con.Open();
    objbulk.WriteToServer(Exceldt);
    con.Close();
}

protected void Button1_Click(object sender, EventArgs e)
{
    string CurrentFilePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);
    InsertExcelRecords(CurrentFilePath);
}

Posted 8-Aug-17 4:21am

Renjith_R

Updated 19-Sep-20 22:35pm

v3

Add a Solution

Comments

Dave Kreskowiak 8-Aug-17 10:35am

Does the user have write permissions to the folder the Excel sheet is in? If under Program Files, the answer to that would be no.

Renjith_R 8-Aug-17 10:42am

Thanks for the quick reply..
i have created a folder in e drive.. still the file path is showing like "C:\Program Files (x86)\Common Files\Microsoft Shared\DevServer\10.0\ImportTest.xlsx"

Where should i keep the excel ?

Dave Kreskowiak 8-Aug-17 10:53am

That's dependent on your business rules and requirements. I can't tell you where to put the Excel file. I CAN tell you that whatever folder the sheet goes in, the user MUST have write permissions to that folder.

As for the path you're coming up with, does the Excel sheet actually exist at that path?

Richard Deeming 9-Aug-17 12:28pm

This appears to be the same question that "paul_vin" posted three days ago:
https://www.codeproject.com/Questions/1200094/Error-while-inserting-excel-sheet-data-into-databa[^]

Are you working on the same project, or have you both been given the same homework assignment? If it's homework, then your teacher obviously hasn't done a very good job of explaining the difference between the client and the server, and how file uploads work in ASP.NET applications.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2017-08-08T04:41:00

Start by checking the permissions on the file and folder as Dave mentions - if the user the software is running under doesn't have read and write access, nor does the application.

If that all looks good, then check the rest of your code: does any part of it create a connection object and forget to close the connection (for best practice, you should use a using block with each creation, so they are automatically closed and disposed when you are finished with them:

using (OleDbCommand Ecom = new OleDbCommand(Query, Econ))
   {
   Econ.Open();
   ...
   }

If any part of your software fails to close the connection, no other part of it will be able to open it again until you app closes (or the GC is kicked into life to dispose the redundant object).

Richard Deeming · Answer 2 · 2017-08-09T06:32:00

(Cross-posting from Error while inserting excel sheet data into database[^], as it seems to be the same question from a different user.)

Your code is running on the server.

The FileName property returns the path - or sometimes just the name - of the file on the client.

Code running on the server has no access to the client's file system.

It might appear to work when you're debugging your code in Visual Studio. But that's just because the server and the client are the same in that specific instance.

You need to save the posted file somewhere on the server, and then use the server path to read the file:

protected void Button1_Click(object sender, EventArgs e)
{
    string filePath = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString("N") + ".xlsx");
    FileUpload1.SaveAs(filePath);
    try
    {
        InsertExcelRecords(filePath);
    }
    finally
    {
        File.Delete(filePath);
    }
}

Member 14943824 · Answer 3 · 2020-09-19T22:35:00

Solution 3

Hello
I installed software for a company
I get this error when I try to update the information
Can you help me with what it is?
Microsoft Access Database Engine could not open or write files
c: \ newfolder. it has already been opened exclusively by another user, or you need permission to view and write its data. "
Is the problem with my software or the client company system?

Posted 19-Sep-20 22:35pm

Member 14943824

Comments

Richard MacCutchan 20-Sep-20 7:48am

"c:\newfolder. it has already been opened exclusively by another user, or you need permission to view and write its data. "
The error message is perfectly clear. Find out which application is using this folder. But you should not be using a folder in the root of the C drive, see this article by the famous OriginalGriff: Where should I store my data?[^].

The Microsoft access database engine cannot open or write to the file error

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0