Cannot initialize the data source object of OLE DB

问题:

I got an error like this

'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

I have written this code

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

Would you pls solve this error

回答1:

It looks like you are trying to use the wrong connectionstring to connect to the SQL server (I am assuming you are trying to copy information from MS Access to MS SQL Server).

A valid MS SQL Server connection string would look something like this: Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

For more information about connectionstrings please have a look at: http://www.connectionstrings.com

回答2:

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

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

Hope this helps.