none
Export to access RRS feed

  • Question

  • Ok,,, here is the requirement: Export data from SQL Server to access (basically just dump it).
    So I write an SP which is returning the data and I add it into a DataTable object.

    Now I try to export to access. So I create a new .mdb file and here is the insert code:

    private void InsertTableInAccess(OleDbConnection accessConnection, DataTable inTable, string tableName)
            {
                // create a temporary command object, just to get the insertion script
                OleDbCommand selectCommand = new OleDbCommand("Select * From " + tableName);
                selectCommand.Connection = accessConnection;
                OleDbDataAdapter adapter = new OleDbDataAdapter(selectCommand);
                OleDbCommandBuilder insertCommandBuilder = new OleDbCommandBuilder(adapter);
                
                // Create the actual insertion command.
                OleDbCommand insertCommand = insertCommandBuilder.GetInsertCommand();
                insertCommand.Connection = accessConnection;
                string insertQuery = insertCommand.CommandText;

                // insert into Access.
                adapter.InsertCommand = insertCommand;
                DataTable changed = inTable.GetChanges(); // This returns null. I added it only for debugging.
                int insertedRows = adapter.Update(inTable); // 0 rows inserted
            }

    There is no error - but no data is inserted either !!
    From the docs , it appears that only the changed rows are updated back into Access. And the fact that I get "null" for changed Rows, backs that theory.
    Now my question is how do I mark all the rows in a DataTable as "Changed" so that the Update method works...??

    Or is this all bull, and I am totally missing something else?

    Sunil
    Tuesday, June 30, 2009 3:23 PM

Answers

  • The tasks I am completing are actually a simple programmatic dumping of data into an access DB. This is like a report that gets exported to Access and the user then downloads the  mdb file.
    We use SQL 2005 as the DB.... But I found the solution. Its pretty straight forward .... I need to make every row dirty by calling SetAdded() on it.
    Then the Insert command generated from CommandBuilder works perfectly...

    This issue can be marked as closed...
    • Marked as answer by r_a_ven Tuesday, June 30, 2009 9:41 PM
    Tuesday, June 30, 2009 9:41 PM

All replies

  • Ok... some progress made..... there is an API called : Row.SetModified() or something like that... you can loop over the rows in the table and mark each
    row as modified.... looks like thats the only way to mark a DataTable as dirty ....

    But now I get this error:
    Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

    I will look more into this ....  but I guess progress is progress ..!
    Tuesday, June 30, 2009 3:57 PM
  • This guy comes closest to the solution .....



    Tuesday, June 30, 2009 6:16 PM
  • Why do you even need to write code for this? If you are using Sql 2008 there are tasks, called Export. You can simply export your database to access directly. It would likely be easier this way.

    Now if you still want to use data set, why not just create the database, ensure you have correct insert statement, does it really work?
    Also review the following post
    http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/dcde641d-d1ef-47a9-ac94-614c8a90b362

    You can set the state to modified and then call update on the access adapter to push the data in.

    Thanks
    Chris Robinson
    Program Manager - DataSet


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, June 30, 2009 8:13 PM
  • The tasks I am completing are actually a simple programmatic dumping of data into an access DB. This is like a report that gets exported to Access and the user then downloads the  mdb file.
    We use SQL 2005 as the DB.... But I found the solution. Its pretty straight forward .... I need to make every row dirty by calling SetAdded() on it.
    Then the Insert command generated from CommandBuilder works perfectly...

    This issue can be marked as closed...
    • Marked as answer by r_a_ven Tuesday, June 30, 2009 9:41 PM
    Tuesday, June 30, 2009 9:41 PM