none
Copy one table to another

    Question

  • Hi, I have 2 password protected database. They have the totally same structure (same tables, same columns). First consist all data, second - is empty. So, I need to copy this filtered data from firs database to second. How it can be done?

    I have C:\ABC.mdb (source) with ABC password, and C:\XYZ.mdb (destination) with XYZ password. All files are exist, all tables are exist (with data in ABC.mdb and empty in XYZ.mdb). Im trying to copy filtered data from table ATable in ABC.mdb to ATable in XYZ.mdb.

    So, I'm reading this data to DB_ABC dataset:

    cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ABC.mdb;Jet OLEDB:Database Password=ABC")
    cmd = New OleDb.OleDbCommand With {.Connection = cn}
    cn.Open()
    cmd.CommandText = "SELECT * FROM ATable WHERE AColumn=1"
    DA = New OleDbDataAdapter(cmd)
    DA.Fill(DB_ABC)
    DA.Dispose()
    cn.Close()

    And trying to put this dataset to another database:

    cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\XYZ.mdb;Jet OLEDB:Database Password=XYZ")
    cmd = New OleDb.OleDbCommand With {.Connection = cn}
    cn.Open()
    cmd.CommandText = "SELECT * FROM ATable WHERE AColumn=1"
    DA = New OleDbDataAdapter(cmd)
    MyBuilder = New OleDbCommandBuilder(DA)
    DA.Update(DB_ABC)
    DA.Dispose()
    cn.Close()
    As result I have filled dataset with all needed data and totally empty second database. Any ideas how to do this?

    Aleksey

    Sunday, June 23, 2013 7:42 PM

Answers

  • Hi Alkesey,

    you need (and should use) at minimum a DbDataAdapter as the method is implemented there. As the inheritance hierarchy is

    OleDbDataAdapter inherits DbDataAdapter
    DbDataAdapter inherits DataAdapter
    DataAdapter inherits Component

    Regards, Elmar

    • Marked as answer by Newfriend Monday, June 24, 2013 7:38 AM
    Monday, June 24, 2013 6:16 AM

All replies

  • Hi Aleksey,

    if you fill a DataTable using an adapter the rows have DataRowState.Unchanged as a default, and the queried rows are considered as new records. To changed this set AcceptChangesDuringFill to false.

    In addition a CommandBuilder needs a primary key - that requires the MissingSchemaAction.AddWithKey.

    Combined (untested):

            Dim table As New DataTable("ATable")
            Using connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ABC.mdb;Jet OLEDB:Database Password=ABC")
                Using adapter As New OleDbDataAdapter(
                     "SELECT * FROM ATable WHERE AColumn=1",
                     connection)
                    adapter.MissingMappingAction = MissingMappingAction.Passthrough
                    ' required for Primary Key
                    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
                    ' Don't call AcceptChanges and treat rows as Added
                    adapter.AcceptChangesDuringFill = False

    adapter.Fill(table) End Using End Using Using connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\XYZ.mdb;Jet OLEDB:Database Password=XYZ") Using adapter As New OleDbDataAdapter( "SELECT * FROM ATable WHERE AColumn=1", connection) adapter.MissingMappingAction = MissingMappingAction.Ignone Using builder As New OleDbCommandBuilder(adapter) adapter.Update(table) End Using End Using End Using

    Regards, Elmar

    Sunday, June 23, 2013 8:28 PM
  • When I try to fill table instead of dataset, I have an error:

    Value of type 'System.Data.DataTable' cannot be converted to 'System.Data.DataSet'.

    I mean the part:

    adapter.Fill(table)


    Aleksey


    • Edited by Newfriend Sunday, June 23, 2013 8:56 PM edited
    Sunday, June 23, 2013 8:55 PM
  • Hi Aleksey,

    The OleDbDataAdapter.Fill method accepts also a single DataTable.
    If you have a DataSet you must supply also the source tablename.

    Regards, Elmar

    Sunday, June 23, 2013 9:17 PM
  • Here is another idea. In the example below Database2.mdb has one table, same table as in Database1.mdb. Database1.mdb has data but not Database2.mdb.

    An important note, both have a auto-incrementing primary key which is why the select statement is used with column names rather than SELECT * along with, at least here why include country column if you know they are all one country but then again you might want that column.

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Using cn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Database2.mdb")
                Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            INSERT INTO [Customers] 
                            SELECT 
                                CompanyName,
                                ContactName,
                                ContactTitle
                            FROM 
                                [MS Access;DATABASE=Database1.mdb;].[Customers] 
                            WHERE Country = 'USA'
                        </SQL>.Value
                    cn.Open()
                    cmd.ExecuteNonQuery()
                    Console.WriteLine(Now)
                End Using
            End Using
        End Sub
    End Class


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Sunday, June 23, 2013 11:11 PM
    Moderator
  • By the way, Primary key is set in both tables like autoincrement column. Could be any problems while filtered data from the first table with numbers (1,5,3,2...) trying to fill new empty table where they should be like 1,2,3,4,5?

    Aleksey

    Monday, June 24, 2013 6:10 AM
  • Hi Alkesey,

    you need (and should use) at minimum a DbDataAdapter as the method is implemented there. As the inheritance hierarchy is

    OleDbDataAdapter inherits DbDataAdapter
    DbDataAdapter inherits DataAdapter
    DataAdapter inherits Component

    Regards, Elmar

    • Marked as answer by Newfriend Monday, June 24, 2013 7:38 AM
    Monday, June 24, 2013 6:16 AM
  • I would like to use SQL but I can't, because it imperfect. Some times I need filtering couldn't be done in SQL, so I did it in code.

    Aleksey

    Monday, June 24, 2013 6:17 AM
  • Hi Aleksey

    the CommandBuilders ignore a DataColumn with AutoIncrement set. And the new rows will have new Identities that are assigned by the Access engine.

    You should try the code as posted first, that can avoid some discussions...

    Regards, Elmar

    Monday, June 24, 2013 6:51 AM
  • Elmar, thanks I got it and pass this problem. But stuck with next one. 

    When I do SELECT * for the ATable in ABC file, my Table filled up with data. Fine! But when I do use the same SELECT * for the ATable in XYZ file, it return nothing, which is ok as long as it empty. BUT! My DA.Update(Table) threw the exception that my Primary field cant be NULL. When I check me Table I see the records with the Primary keys. And I assume that problem is with second SELECT.

    I try to avoid this column (Col-0) and did SELECT Col-1, Col-2 for the first and second file. But got still the same exception. Why?


    Aleksey

    Monday, June 24, 2013 6:51 AM
  • My code for now

    Dim Table As New DataTable("ATable")
    
    cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ABC.mdb;Jet OLEDB:Database Password=ABC")
    cmd = New OleDb.OleDbCommand With {.Connection = cn}
    cn.Open()
    
    cmd.CommandText = "SELECT Col-1, Col-2 FROM ATable"
    DA = New OleDbDataAdapter(cmd)
    DA.MissingMappingAction = MissingMappingAction.Passthrough
    DA.MissingSchemaAction = MissingSchemaAction.AddWithKey
    DA.AcceptChangesDuringFill = False
    DA.Fill(Table)
    DA.Dispose()
    cn.Close()
    
    
    cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\XYZ.mdb;Jet OLEDB:Database Password=XYZ"
    cmd = New OleDb.OleDbCommand With {.Connection = cn}
    cn.Open()
    
    cmd.CommandText = "SELECT Col-1, Col-2 FROM ATable"
    DA = New OleDbDataAdapter(cmd)
    DA.MissingMappingAction = MissingMappingAction.Ignore
    MyBuilder = New OleDbCommandBuilder(DA)
    DA.Update(Table)
    DA.Dispose()
    cn.Close()


    Aleksey

    Monday, June 24, 2013 6:59 AM
  • It's kind of magic, but as soon as I remove 

    DA.MissingMappingAction = MissingMappingAction.Passthrough
    DA.MissingSchemaAction = MissingSchemaAction.AddWithKey
    DA.AcceptChangesDuringFill = False

    it's works...


    Aleksey

    Monday, June 24, 2013 7:38 AM
  • Hi Aleksey,

    it' not easy to follow all those snippets... Some points:
    If the column names contains a "-" or other special characters you will have to use square brackets:

    SELECT [Col-1], [Col-2] FROM ATable

    as otherwise Access would apply arithmetic rules if "Col" exists.
    As suggestion, use underscores for names, here COL_1 , COL_2 , as it avoids such problems.

    About the identity primary key:
    Check if the source tables datacolumn has the AutoIncrement property set - for more see FillSchema.
    The destination table should have the identity attribute set.

    Regards, Elmar
    Monday, June 24, 2013 8:04 AM
  • Following worked with Jet 4.0 (Access):

                    using(var connection = new OleDbConnection(connectionString))
                    using(var origConnection = new OleDbConnection(origConnectionString))
                    {
                        connection.Open();
                        origConnection.Open();

                        var table = new DataTable("some_table");

                        using (var cmd = new OleDbCommand("SELECT * FROM some_table"
                            , origConnection))
                        using (var DA = new OleDbDataAdapter(cmd))
                        {
                            DA.MissingMappingAction = MissingMappingAction.Passthrough;
                            DA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                            DA.AcceptChangesDuringFill = false;
                            DA.Fill(table);
                        }

                        using(var cmd = new OleDbCommand("SELECT * FROM some_table"
                            ,connection))
                        using(var DA = new OleDbDataAdapter(cmd))
                        {
                            DA.MissingMappingAction = MissingMappingAction.Passthrough;
                            var MyBuilder = new OleDbCommandBuilder(DA);
                            DA.Update(table);
                        }
                    }

    Friday, March 07, 2014 12:24 PM