none
Copy Data from SQL Server Table to MS Access Table RRS feed

  • Question

  • I run the script below, and get this error:

    ODBC--call failed.

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using ADODB;

    namespace WindowsFormsApplication1
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }


            // Add the reference for
            // Microsoft ActiveX Data Objects 6.0 Library
            // using ADODB;
            // Import data from sql server to access

            private void button1_Click(object sender, EventArgs e)
            {
                    ADODB.Connection cn = new ADODB.Connection();
                    cn.Open("Provider=Microsoft.ACE.OLEDB.12.0;" +
                        "Data Source=C:\\Users\\Excel\\Desktop\\Coding\\Microsoft Access\\Northwind.mdb;"
                        + "Persist Security Info=False;");
                    string sql = "SELECT * INTO "
                        + "[ImportFromSQLServer] "
                        + "FROM "
                        + "[ODBC;Server=Excel-PC\\SQLEXPRESS;Database=Northwind;Trusted_Connection=True;]."
                        + "[dbo.SUBTOTALS]";
                    //Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;
                    object objAffected;
                    cn.Execute(sql, out objAffected, 0);
                    cn = null;
            }
        }
    }

    In the Immediate Window I see this:

    ?sql
    "SELECT * INTO [ImportFromSQLServer] FROM [ODBC;Server=Excel-PC\\SQLEXPRESS;Database=Northwind;Trusted_Connection=True;].[dbo.SUBTOTALS]"

    Can someone please point out the problem???


    Ryan Shuell

    Sunday, November 25, 2012 8:14 PM

Answers

  • Hope this helps you...

    DOWNLOAD THIS AS ENTIRE SOLUTION (File Name: MySQLToMsAccess.zip )

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    
    //.NET Database Libraries
    using System.Data;
    
    //MySQL Database Libraries
    using MySql.Data;
    using MySql.Data.Common;
    using MySql.Data.Types;
    using MySql.Data.MySqlClient;
    
    //MS-Access-OLEDB Database Libraries
    using System.Data.OleDb;
    
    namespace MySQLToMsAccess
    {
        class Program
        {
            static void Main(string[] args)
            {
                //Source Database : MySQL Database
                string strMySqlConnectionString =
                                                    @"SERVER=localhost;" +
                                                    @"DATABASE=dummy;" +
                                                    @"UID=aarsh;" +
                                                    @"PASSWORD=< password >";
                MySqlConnection MSqlConnDummy = new MySqlConnection(strMySqlConnectionString);
                MySqlCommand mSqlCmdSelectCustomers = MSqlConnDummy.CreateCommand();
                mSqlCmdSelectCustomers.CommandText = @"select customerNumber, customerName, country from customers limit 10";
                MySqlDataReader mSqlReader_Customers;
                MSqlConnDummy.Open();
                mSqlReader_Customers = mSqlCmdSelectCustomers.ExecuteReader();
                DataTable dtCustomers = new DataTable();
                dtCustomers.Load(mSqlReader_Customers);
                MSqlConnDummy.Close();
    
                //Target Database : Ms-Access Database
                string strAccessConnectionString =
                                                    @"Provider=Microsoft.Jet.Oldedb.4.0;" +
                                                    @"Data Source=C:\Database\TestDB.mdb;";
                OleDbConnection accessConnection = new OleDbConnection(strAccessConnectionString);
                accessConnection.Open();
                OleDbCommand insertCommand = new OleDbCommand();
                insertCommand.Connection = accessConnection;
                insertCommand.CommandText = @"INSERT INTO Cust (CustomerID, CustomerName, Country) VALUES (@id, @name, @country)";
    
                foreach (DataRow row in dtCustomers.Rows)
                {
                    Console.WriteLine("{0}\t{1}\t\t\b{2}", row["customerNumber"].ToString(), row["customerName"].ToString().PadRight(25), row["country"].ToString());
                    insertCommand.Parameters.Clear();
                    insertCommand.Parameters.AddWithValue("@id", row["customerNumber"].ToString());
                    insertCommand.Parameters.AddWithValue("@name", row["customerName"].ToString());
                    insertCommand.Parameters.AddWithValue("@country", row["country"].ToString());
    
                    insertCommand.ExecuteNonQuery();
                }
                accessConnection.Close();
                Console.ReadKey();
            }
        }
    }

    If you are experiencing : "The 'Microsoft.Jet.Oldedb.4.0' provider is not registered on the local machine." kind of error due your 64 bit machine, HERE IS THE SOLUTION ... !

    • please convert your Target Framework from "Any CPU" to "x86" in project properties and make sure the same by right clicking solution and going to "Configuration Manager" OR
    • Installing 2007 / 2010 Office System Driver: Data Connectivity Components (2007 / 2010 )

    With the 2nd alternative you may want to change  the Provider argument of the ConnectionString property to “Microsoft.ACE.OLEDB.12.0”.  (I had to do this)

    Resulting the Connection String to look like below:
    
    string strAccessConnectionString= @"Provider=Microsoft.ACE.OLEDB.12.0;" +
    @"Data Source=C:\Database\TestDB.mdb;";


    Aarsh Talati


    • Proposed as answer by Aarsh (MCTS) Monday, November 26, 2012 4:08 AM
    • Edited by Aarsh (MCTS) Monday, November 26, 2012 4:13 AM for adding helpful information for "The 'Microsoft.Jet.Oldedb.4.0' provider is not registered on the local machine." error
    • Marked as answer by Jason Dot Wang Tuesday, December 4, 2012 8:51 AM
    Monday, November 26, 2012 3:42 AM
  • Look at this project: https://exportdata.codeplex.com/.  it supports to SQL Server Table to MS Access Table, also export data from database, listview,command to MS Access.
    • Proposed as answer by Jason Dot Wang Wednesday, November 28, 2012 5:16 AM
    • Marked as answer by Jason Dot Wang Tuesday, December 4, 2012 8:51 AM
    Monday, November 26, 2012 4:00 AM

All replies

  • Why not just do this inside MS Access? It has a fully functional VBA programming environment, and then you avoid a lot of hassles such as this.

    "Premature optimization is the root of all evil." - Knuth

    If I provoked thought, please click the green arrow

    If I provoked Aha! please click Propose as Answer

    Sunday, November 25, 2012 8:52 PM
  • Yeah, I know, I work as an Access developer.  What you described is pretty easy. 

    I'm just trying to learn a new skill.  I'm just trying to learn more about C# Automation.


    Ryan Shuell

    Sunday, November 25, 2012 9:40 PM
  • Hammers for hammering, saws for sawing

    "Premature optimization is the root of all evil." - Knuth

    If I provoked thought, please click the green arrow

    If I provoked Aha! please click Propose as Answer

    Sunday, November 25, 2012 9:43 PM
  • Hope this helps you...

    DOWNLOAD THIS AS ENTIRE SOLUTION (File Name: MySQLToMsAccess.zip )

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    
    //.NET Database Libraries
    using System.Data;
    
    //MySQL Database Libraries
    using MySql.Data;
    using MySql.Data.Common;
    using MySql.Data.Types;
    using MySql.Data.MySqlClient;
    
    //MS-Access-OLEDB Database Libraries
    using System.Data.OleDb;
    
    namespace MySQLToMsAccess
    {
        class Program
        {
            static void Main(string[] args)
            {
                //Source Database : MySQL Database
                string strMySqlConnectionString =
                                                    @"SERVER=localhost;" +
                                                    @"DATABASE=dummy;" +
                                                    @"UID=aarsh;" +
                                                    @"PASSWORD=< password >";
                MySqlConnection MSqlConnDummy = new MySqlConnection(strMySqlConnectionString);
                MySqlCommand mSqlCmdSelectCustomers = MSqlConnDummy.CreateCommand();
                mSqlCmdSelectCustomers.CommandText = @"select customerNumber, customerName, country from customers limit 10";
                MySqlDataReader mSqlReader_Customers;
                MSqlConnDummy.Open();
                mSqlReader_Customers = mSqlCmdSelectCustomers.ExecuteReader();
                DataTable dtCustomers = new DataTable();
                dtCustomers.Load(mSqlReader_Customers);
                MSqlConnDummy.Close();
    
                //Target Database : Ms-Access Database
                string strAccessConnectionString =
                                                    @"Provider=Microsoft.Jet.Oldedb.4.0;" +
                                                    @"Data Source=C:\Database\TestDB.mdb;";
                OleDbConnection accessConnection = new OleDbConnection(strAccessConnectionString);
                accessConnection.Open();
                OleDbCommand insertCommand = new OleDbCommand();
                insertCommand.Connection = accessConnection;
                insertCommand.CommandText = @"INSERT INTO Cust (CustomerID, CustomerName, Country) VALUES (@id, @name, @country)";
    
                foreach (DataRow row in dtCustomers.Rows)
                {
                    Console.WriteLine("{0}\t{1}\t\t\b{2}", row["customerNumber"].ToString(), row["customerName"].ToString().PadRight(25), row["country"].ToString());
                    insertCommand.Parameters.Clear();
                    insertCommand.Parameters.AddWithValue("@id", row["customerNumber"].ToString());
                    insertCommand.Parameters.AddWithValue("@name", row["customerName"].ToString());
                    insertCommand.Parameters.AddWithValue("@country", row["country"].ToString());
    
                    insertCommand.ExecuteNonQuery();
                }
                accessConnection.Close();
                Console.ReadKey();
            }
        }
    }

    If you are experiencing : "The 'Microsoft.Jet.Oldedb.4.0' provider is not registered on the local machine." kind of error due your 64 bit machine, HERE IS THE SOLUTION ... !

    • please convert your Target Framework from "Any CPU" to "x86" in project properties and make sure the same by right clicking solution and going to "Configuration Manager" OR
    • Installing 2007 / 2010 Office System Driver: Data Connectivity Components (2007 / 2010 )

    With the 2nd alternative you may want to change  the Provider argument of the ConnectionString property to “Microsoft.ACE.OLEDB.12.0”.  (I had to do this)

    Resulting the Connection String to look like below:
    
    string strAccessConnectionString= @"Provider=Microsoft.ACE.OLEDB.12.0;" +
    @"Data Source=C:\Database\TestDB.mdb;";


    Aarsh Talati


    • Proposed as answer by Aarsh (MCTS) Monday, November 26, 2012 4:08 AM
    • Edited by Aarsh (MCTS) Monday, November 26, 2012 4:13 AM for adding helpful information for "The 'Microsoft.Jet.Oldedb.4.0' provider is not registered on the local machine." error
    • Marked as answer by Jason Dot Wang Tuesday, December 4, 2012 8:51 AM
    Monday, November 26, 2012 3:42 AM
  • Look at this project: https://exportdata.codeplex.com/.  it supports to SQL Server Table to MS Access Table, also export data from database, listview,command to MS Access.
    • Proposed as answer by Jason Dot Wang Wednesday, November 28, 2012 5:16 AM
    • Marked as answer by Jason Dot Wang Tuesday, December 4, 2012 8:51 AM
    Monday, November 26, 2012 4:00 AM