hi, can someone help transfer data from one database to another database

[edgarbenilde]

hi, i'm using using access database...

i having difficulties on how to transfer data from one table in one database to another table of another database...?

can someone give me sample running code... or running application on how to do this?

please help..?

Happy new year to all....

[bigMeUp]

I don't think you're going to find it best to use VB to do this. Why not use the export functionality of Access instead (right click the table, select Export and away you go...)

[jmcilhinney]

If you do want to do this using VB.NET then you need to recognise that "transferring" data from one database to another is not an operation in itself. It's two operations: retrieving the data from one database and then saving it to the other. I'm guessing you already know how to do both of those. In each case you need an OleDbDataAdapter and a DataTable. It just so hapens that you can use the same one in each case.

If there are two databases then you'll need two OleDbConnections. The SelectCommand of the DataAdapter will use one to get data and the InsertCommand will use the other to save data.

The trick is to set the AcceptChangesDuringFill property of the DataAdapter to False. That way all the DataRows will have a RowState of Added and will be ready to insert into the other table.

[edgarbenilde]

BigmeUp...

i can't do what you said it's been done in a program automatically not manual...

[edgarbenilde]

jmcilhinney,

i only tried to do it in access querry and it's only one database itseft..
like this..
INSERT INTO Table2
SELECT *
FROM table1
WHERE Name='Edgar';

but how about two database... and insert it to the table i wanted to..
i know that it's using two connection...
but how could i do it..?

please give me sample..

[jmcilhinney]

Follow the Data Access link in my signature. It shows you how to retrieve data from a database and save data to a database. As I said, you just need to use two different connections. The rest is exactly the same.

[edgarbenilde]

hi, i came-up this code...

but i got errors on the 2nd Dim Statement..

can someone please help me to make this work... please..?

Dim connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\RSODB.mdb.mdb.mdb;User Id=admin;Password=;")

Dim connection1 As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\CopyOfRSODB.mdb.mdb;User Id=admin;Password=;")

Dim insert As New OleDbCommand("INSERT INTO FinalCustItemRSO", connection (Dim adapter As New OleDbDataAdapter("SELECT * FROM FinalCustItemRSO",connection1))

[edgarbenilde]

Hello,please Help..!!! I Really Need It..

[edgarbenilde]

Help please how could i Finalized this statemen below..?
Dim insert As New OleDbCommand("INSERT INTO FinalCustItemRSO", connection (Dim adapter As New OleDbDataAdapter("SELECT * FROM FinalCustItemRSO",connection1))

[jmcilhinney]

I'm afraid you simply made that code up without actually reading any documentation. There's absolutely nowhere in my example or the documentation for the OleDbConnection, OleDbDataAdapter or OleDbCommand that would suggest that that is valid. You create two connections, one adapter and then two commands:

Dim selectConnection As New OleDbConnection("connection string here")
Dim insertConnection As New OleDbConnection("connection string here")
 
Dim adapter As New OleDbDataAdapter
 
Dim selectCommand As New OleDbCommand("SELECT statement here", selectConnection)
Dim insertCommand As New OleDbCommand("INSERT statement here", insertConnection)
 
'Add parameters here.
 
adapter.SelectCommand = selectCommand
adapter.InsertCommand = insertCommand
adapter.AcceptChangesDuringFill = False

There are numerous variations on that theme that would also work.

[edgarbenilde]

Yes, agree to that... but as i read to the code... i must inserting with the assign values...

because i need form like this parrameters here and this is originally from you in link below....
http://www.vbforums.com/showthread.php?t=469872

Dim connection As New SqlConnection("connection string here")

Dim adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", connection)

Dim delete As New SqlCommand("DELETE FROM StockItem WHERE ID = @ID", connection)

Dim insert As New SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", connection)

Dim update As New SqlCommand("UPDATE StockItem SET Name = @Name, Quantity = @Quantity, Unit = @Unit WHERE ID = @ID", connection)


delete.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")

insert.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")

insert.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")

insert.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")

update.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")

update.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")

update.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")

update.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")

adapter.DeleteCommand = delete

adapter.InsertCommand = insert

adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey



Dim table As New DataTable

'Retrieve the data.
adapter.Fill(table)

'The table can be used here to display and edit the data.

'That will most likely involve data-binding but that is not a data access issue.
'Save the changes.
adapter.Update(table)

i don't want to select it one by one.. because what i have thousand of record to be transfer and how much time i spend to it... to transfer.. it for that it must be better to just link my access database to excel if i use this method...

i wanted to create method that work not that slow....

because look at this querry

INSERT INTO Table2
SELECT *
FROM table1
WHERE Age='21';

in just one click or run of program it transfer all the data that has the Value of Age=21 from one table to another table.. in just that simple code... why i need to do parrameters... why not to code it simply..?

that is what i wanted to do....
because if i had 50000 record to be transfer it take a lot of time before it will finished...

the only problem here is to simplify the lacking codes...

please help me in this method below to work..

Dim insert As New OleDbCommand("INSERT INTO FinalCustItemRSO", connection (Dim adapter As New OleDbDataAdapter("SELECT * FROM FinalCustItemRSO",connection1))

[jmcilhinney]

1. Please wrap your code snippets in the appropriate tags.
2. Please read post #10 again.

After the code in post #10 you'd just do this:

Dim table As New DataTable
 
adapter.Fill(table)
adapter.Update(table)

[RonR]

hi, i'm using using access database...

i having difficulties on how to transfer data from one table in one database to another table of another database...?

can someone give me sample running code... or running application on how to do this?

please help..?

Happy new year to all....

approx. how many columns do you have in your DB table??

[edgarbenilde]

i had 25 column is my DB table...

[edgarbenilde]

jmcilhinney,

i use your given sample but i doesn't work..
here my codes...

i got syntax error on INSERT INTO Statement..

here is my codes.

Try

Dim insertConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\RSODB.mdb;User Id=admin;Password=;")
Dim selectConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\ph-suman\rso\CopyOfRSODB.mdb;User Id=admin;Password=;")

Dim adapter As New OleDbDataAdapter

Dim selectCommand As New OleDbCommand("SELECT * From FinalCustItemRSO", selectConnection)
Dim insertCommand As New OleDbCommand("INSERT into MyRSOTransfer", insertConnection)

'Add parameters here.
adapter.SelectCommand = selectCommand
adapter.InsertCommand = insertCommand
adapter.AcceptChangesDuringFill = False


Dim table As New DataTable

adapter.Fill(table)
adapter.Update(table)


Catch ex As Exception
MsgBox(ex.ToString)
End Try


can you please give me what is locking in my codes..?

[jmcilhinney]

1. I asked you several posts ago to wrap your code in tags. If you're not prepared to make an effort for us...

2. That is not a legal INSERT statement. What is it supposed to be inserting? The system doesn't just magically know what to insert where. Read an SQL reference or just go back to my code example and read it again.

[edgarbenilde]

what do you mean by wrap your code in tags.?

[tsungik]

Hi edgarbenilde,

If you are transferring the table from one database to another database, the easiest way to do this without coding is to open two (2) instance of the MS Access application. Open the Source Access Database on the first instance of Access, then open the Target Access Database on the second instance of Access. Select the Table and Drag and Drop to the Target Database then you have it done.

hi, i'm using using access database...

i having difficulties on how to transfer data from one table in one database to another table of another database...?

can someone give me sample running code... or running application on how to do this?

please help..?

Happy new year to all....

[edgarbenilde]

tsungik, how could it be look like..?

can you give ilustration or steps on how to do it..?

[tsungik]

See the attached file on how to Drag and Drop the Table from Source to Target.

[RonR]

If you want to transfer the data from one table to another using VB and not Access let me know and I can help.

[joewmaki]

jmcilhinney,

i only tried to do it in access querry and it's only one database itseft..
like this..
INSERT INTO Table2
SELECT *
FROM table1
WHERE Name='Edgar';

but how about two database... and insert it to the table i wanted to..
i know that it's using two connection...
but how could i do it..?

please give me sample..

There are lots of ways to do this. Are you creating a new table in the second database or just appending records to an existing one? If appending records you can link the remote table to your source database (File/Get External Data/Link Tables) and then run a simple insert query. If your moving the data to a new table you might want to look at the TransferDatabase method in Access. Are you trying to use VB.Net because your doing this from an external application?

[Hack]

what do you mean by wrap your code in tags.?

He means please use the highlight tags.

Example: [highlight=vb.net]your code goes in here[/highlight]

@joewmaki: Welcome to VBF Joe!

[Ggalla1779]

Egdar vb.net can be ascary place after access but after you get your head round the concepts its alot easier and more dynamic than Access.

Suggest you use Access to create a insert query to match what you need in for your post no 15...as it looks like your almost there!!

jmc I loved your idea about AcceptChangesDuringFill = False...that was well handy to know

cheers george