ado - insert from one DB to another

[jayinthe813]

Unless you have the databases linked through Access itself, you have to use two separate connections - or at least two separate connection strings - for the two separate databases, as dunfiddlin says. You can do it with a single data adapter though, because the connections relate to the commands, not the adapter, e.g.

Dim sourceConnectionString = "..."
Dim destinationConnectionString = "..."
Dim selectStatement = "SELECT * FROM SomeTable"
Dim insertStatement = "INSERT INTO SomeTable (SomeColumn) VALUES (@SomeColumn)"

Using destinationConnection As New OleDbConnection(destinationConnectionString),
      insertCommand As New OleDbCommand(insertStatement, destinationConnection),
      adapter As New OleDbDataAdapter(selectStatement, sourceConnectionString)
    'Add parameters to INSERT statement.
    insertCommand.Parameters.Add("@SomeColumn", OleDbType.VarChar, 50, "SomeColumn")

    'Keep all rows in Added state so that they are ready to insert.
    adapter.AcceptChangesDuringFill = False

    adapter.InsertCommand = insertCommand

    Dim table As New DataTable

    'Retrieve data from the source database.
    adapter.Fill(table)

    'Save data to the destination database.
    adapter.Update(table)
End Using

When the adapter adds the new rows to the DataTable, as with all new DataRows, their RowState is Added by default. Normally, at the end of a call to Fill, the adapter calls AcceptChanges on the DataTable and all RowStates are set to Unchanged. In this case, you don't want that. If the rows are to be inserted into the destination database then their RowStates must be Added, which is why the code above sets AcceptChangesDuringFill to False.

Essentially what I am trying to achieve is similar to the "import" feature in access, where you bring an entire table over. Thanks for the code. If I am dealing with say 100 fields, do I need to add 100 parameters as values?

:EDIT:

        Dim cmd As OleDbCommand = New OleDbCommand()
        Dim sConStr As String = "Provider=Microsoft.jet.oledb.4.0;data source=C:\test.mdb"
        Dim conn As OleDbConnection

        conn = New OleDbConnection(sConStr)

        cmd.Connection = conn
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT * INTO DEST_TBL FROM [C:\mymdb.mdb].srcTABLE"

        conn.Open()
        cmd.ExecuteNonQuery()
        conn.Close()

This code answers my initial question, but is there a reason why doing something like this would not be a good idea?