无法初始化OLE DB提供程序"Microsoft.Jet.OLEDB.4.0"的数据源对象.用于链接服务器“(null)" [英] Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"

问题描述

我遇到这样的错误

'无法为链接服务器(null)"初始化OLE DB提供程序"Microsoft.Jet.OLEDB.4.0"的数据源对象. 链接服务器(空)"的OLE DB访问接口"Microsoft.Jet.OLEDB.4.0"返回消息找不到可安装的ISAM.".将数据库从msaccess传输到SQL时

'Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)". OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".' while transferring database from msaccess to SQL

我已经编写了这段代码

try
{
    DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
    DataTable userTables = null;
    using (connection)
    {
       string mappath = dataGridView1.CurrentRow.Cells["Path"].Value.ToString();
       string[] filePaths = Directory.GetFiles(@"" + mappath + "", "*.mdb", SearchOption.TopDirectoryOnly);
       // c:\test\test.mdb
       foreach (string tr in filePaths)
       {
          connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + tr + "";
          string[] restrictions = new string[4];
          restrictions[3] = "Table";
          connection.Open();
          userTables = connection.GetSchema("Tables", restrictions);
          List<string> tableNames = new List<string>();
          for (int i = 0; i < userTables.Rows.Count; i++)
             tableNames.Add(userTables.Rows[i][2].ToString());
          try
          {
             foreach (string tableName in tableNames)
             {
                cn1 = new SqlConnection(con);
                if (cn1.State != ConnectionState.Open) { cn1.Open(); }
                SqlCommand cmd = new SqlCommand("select * into [" + tableName + "] from OPENROWSET('Microsoft.Jet.OLEDB.4.0','" + tr + "',[" + tableName + "])");
                cmd.Connection = cn1;
                cmd.ExecuteNonQuery();---Got error Here
             }
          }
          catch (Exception Ex) { connection.Close(); }
          connection.Close();
       }
    }
 }
 catch (Exception Ex) { }

请您解决此错误

推荐答案

我已经在本地环境上进行了简单的测试，并且以下各项工作都很吸引人(这来自C#控制台应用程序):

I have made a simple test on my local environment and the following works like a charm (this is from a C# console application):

static void Main(string[] args)
{
    string accessConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Temp\\StackOverflowDemo\\MyAccessDb.mdb;User Id=admin;Password =; ";

    using (DbConnection accessConnection = new OleDbConnection(accessConnectionString))
    {
        accessConnection.Open();

        using (DbCommand accessCommand = new OleDbCommand())
        {
            string accessQuery =
                "SELECT * INTO [MySqlTable] IN '' [ODBC;Driver={SQL Server};Server=(local);Database=MySqlDb;Uid=username;Pwd=password;] FROM [MyAccessTable]";

            accessCommand.CommandText = accessQuery;
            accessCommand.Connection = accessConnection;

            accessCommand.ExecuteNonQuery();
        }
    }
}

我已经使用MS Access 2002数据库作为源，并以SQL Server 2014数据库作为目标进行了测试.

I have tested this with a MS Access 2002 database as my source and a SQL Server 2014 database as my target.

希望这会有所帮助.

这篇关于无法初始化OLE DB提供程序"Microsoft.Jet.OLEDB.4.0"的数据源对象.用于链接服务器“(null)"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！