Microsoft Access数据库引擎无法打开或写入文件错误 [英] The Microsoft access database engine cannot open or write to the file error

问题描述

我尝试将Excel工作表导入数据库。我已经厌倦了下面提到的代码，我收到错误

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.

当我执行代码时，excel表格没有在任何地方打开..



请协助

提前致谢。



我尝试了什么：

"

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);
}

推荐答案

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

//创建SqlBulkCopy对象
SqlBulkCopy objbulk = new SqlBulkCopy（con）;
//分配目标表名
objbulk.DestinationTableName =ERP_Mgmt;
//映射表列
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）;
//将数据表记录插入数据库
con.Open（）;
objbulk.WriteToServer（Exceldt）;
con.Close（）;
}

protected void Button1_Click（object sender，EventArgs e）
{
string CurrentFilePath = Path.GetFullPath（FileUpload1.PostedFile.FileName）;
InsertExcelRecords（CurrentFilePath）;
}

"); 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); }

首先检查文件和文件夹的权限，如Dave所提到的 - 如果用户软件在isn下运行没有读写访问权限，也没有应用程序。



如果一切看起来都不错，那么检查一下代码的其余部分：它的任何部分都会创建一个连接对象并忘记关闭连接（为了最佳实践，您应该在每次创建时使用使用块，因此它们会在您完成后自动关闭并处理：

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();
   ...
   }

如果您的软件的任何部分未能关闭连接，那么在您关闭应用程序之前，它的任何其他部分都无法再次打开它（或者GC开始生效以处置冗余对象）。

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

（从将excel表数据插入数据库时​​出错 [ ^ ]，因为它似乎是来自不同用户的相同问题。）



您的代码在服务器上运行。



FileName 属性返回客户端上文件的路径 - 有时只是名称 - 。



服务器上运行的代码无法访问到客户端的文件系统。



可能出现在Visual Studio中调试代码时工作。但这只是因为服务器和客户端在该特定实例中是相同的。



您需要将发布的文件保存在服务器上的某个位置，然后使用服务器读取文件的路径：

(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);
    }
} 

这篇关于Microsoft Access数据库引擎无法打开或写入文件错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！