ado - insert from one DB to another

[jayinthe813]

Hi all, not too sure on how to craft this sql, I want to insert into an access database on a share path, from another access database on a share path, in vb.net using

        Dim cmd As OleDbCommand = New OleDbCommand()
        Dim sConStr As String = "Provider=Microsoft.jet.oledb.4.0;data source=localdb"
        Dim conn As OleDbConnection

        conn = New OleDbConnection(sConStr)

        cmd.Connection = conn
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SQL GOES HERE"               'INSERT INTO localdb SELECT table1 from DB2 ?

        conn.Open()
        cmd.ExecuteNonQuery() 'this should execute the sql to add the table
        conn.Close()

I cant seem to get the syntax right, does anyone have an example of that?

[dunfiddlin]

There is no syntax. If you're addressing two different datatbases then you need two different connections (not that you can do combined operations on Access databases anyway!) Open Database 1, Select (remembering that you need to include the correct connection in the command). Close DB1, open DB2, Insert (including the correct connection in the command).

[jayinthe813]

so what you are saying is i need to populate a datatable from one connection, then insert it into the other connection? Or what is my placeholder for the items that were selected in connection 1 to be sent to connection 2?

[dunfiddlin]

Yes, that is what I am saying!

[jmcilhinney]

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.

[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?