ado - insert from one DB to another
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
Code:
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?
Re: ado - insert from one DB to another
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).
Re: ado - insert from one DB to another
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?
Re: ado - insert from one DB to another
Yes, that is what I am saying!
Re: ado - insert from one DB to another
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.
Code:
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.
Re: ado - insert from one DB to another
Quote:
Originally Posted by
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.
Code:
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:
Code:
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?
