none
SQL data to Access RRS feed

  • Question

  •  

    I need to get data out of a SQL database and then insert it into an Access database (don't ask why ). I can get the data from the SQL database into a dataset (which has 1 table). The problem I have is then inserting it easily into the empty Access table which has the same data structure as the dataset (field names, field order and data types). Having played about with it for a while, the only way I can see is to loop through each row in the dataset table and execute an appropriate INSERT after setting the command parameters.

    As data transfer/loading is such a common requirement, does anyone know of a quick and easy way to do this, more akin to the Fill() method on the dataAdapter?

     

    Does the DataMapping help enable this?

    Thursday, October 30, 2008 8:49 AM

Answers

  • Code Snippet

    DataSet dataSet = new DataSet();

     

    // query from SQL

    SqlDataAdapter adapter = new SqlDataAdapter(

    "select * from mytable",

    "server=.\\SqlExpress;integrated security=true");

    adapter.AcceptChangesDuringFill = false; // leave DataRow in Added state

    adapter.Fill(dataset);

     

    // insert into access

    OleDbDataAdapter adapter = new OleDbDataAdapter(

    "select * from mytable",

    "provider=Microsoft.Jet.OLEDB.4.0;");

     

    // adapter.OnRowUpdating event keeps the builder alive

    // builder.GetInsertCommand() isn't what you want, you can instead

    // create your own and assign to adapter.InsertCommand

    OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);

     

    // to see what the cmd builder does per OnRowUpdating (per row)

    adapter.OnRowUpdating += delegate(object sender, OleDbRowUpdatingEventArgs args)

    {

       Console.WriteLine(args.Command.CommandText);

       foreach(OleDbParamter p in args.Command.Parameters)

           Console.WriteLine("{0}={1}", p.ParameterName, p.Value);

    };

     

    adapter.Update(dataSet);

     

     

     

     

     

    Thursday, October 30, 2008 4:55 PM
    Moderator

All replies

  • I need to get data out of a SQL database and then insert it into an Access database (don't ask why ). I can get the data from the SQL database into a dataset (which has 1 table). The problem I have is then inserting it easily into the empty Access table which has the same data structure as the dataset (field names, field order and data types). Having played about with it for a while, the only way I can see is to loop through each row in the dataset table and execute an appropriate INSERT after setting the command parameters.

    As data transfer/loading is such a common requirement, does anyone know of a quick and easy way to do this, more akin to the Fill() method on the dataAdapter?

    Tuesday, October 28, 2008 3:30 PM
  • Code Snippet

    DataSet dataSet = new DataSet();

     

    // query from SQL

    SqlDataAdapter adapter = new SqlDataAdapter(

    "select * from mytable",

    "server=.\\SqlExpress;integrated security=true");

    adapter.AcceptChangesDuringFill = false; // leave DataRow in Added state

    adapter.Fill(dataset);

     

    // insert into access

    OleDbDataAdapter adapter = new OleDbDataAdapter(

    "select * from mytable",

    "provider=Microsoft.Jet.OLEDB.4.0;");

     

    // adapter.OnRowUpdating event keeps the builder alive

    // builder.GetInsertCommand() isn't what you want, you can instead

    // create your own and assign to adapter.InsertCommand

    OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);

     

    // to see what the cmd builder does per OnRowUpdating (per row)

    adapter.OnRowUpdating += delegate(object sender, OleDbRowUpdatingEventArgs args)

    {

       Console.WriteLine(args.Command.CommandText);

       foreach(OleDbParamter p in args.Command.Parameters)

           Console.WriteLine("{0}={1}", p.ParameterName, p.Value);

    };

     

    adapter.Update(dataSet);

     

     

     

     

     

    Thursday, October 30, 2008 4:55 PM
    Moderator