Batch insertion of records in MS Access

[suneesh]

I've got a VB.NET application in which I download records from an oracle table using a select query ( say : select Name,Job,Sal,dept from EMP group by dept). Now I want to create an MS Access database for each dept and insert that dept's records into this access table.
Is it possible to do a batch insert into the access table without having to loop through each record from the select query.

 

[jmcilhinney]

It is but it will take a bit of fiddling. If you're using a DataAdapter to Fill a DataTable from Oracle then I suggest that you set its AcceptChangesDuringFill property to False so that the RowState of each DataRow will remain as Added so that it is ready to be inserted. For each table you want to insert to, you would then create an OleDbdataAdapter with an InsertCommand suitable for inserting just the records for the desired department. You would then call GetChanges on your original DataTable to get a copy that you could pass as an argument to a call to Update on your DataAdapter. This leaves the original DataTable unaffected so it can be used in the same way for each subsequent insertion.

 

[suneesh]

Thanx for the response.
But still I am unable to get it working! First of all I cannot figure out how to write the insert query for mdb having source as an oracle query.
But rather than using any insert query, Can I directly use the changed dataset to update in my mdb?
ie,
1) Create an Oracle Adapter to populate my oracle dataset.
2) Create an mdb Adapter for the new mdb table (which will be empty initially)
3) copy the oracle data set to this mdb dataset
4) Fire the mdb adapter's update property to populate the mdb.

I cant figure out how to implement this!

 

[jmcilhinney]

I will just be repeating what I posted previously, but here goes:

1. Create an OracleDataAdapter with the appropriate SelectCommand and make sure you set its AcceptChangesDuringFill property to False. Call the Fill method to get the data into a DataTable.
2. Create an OleDataAdapter with the appropriate InsertCommand.
3. There is no copying of any DataSet or DataTable. The data is the data and it doesn't care where it comes from or where it goes to. You use a single DataTable for each table of data you want to transfer.
4. Update is a method, not a property. You call Update on your OleDbDataAdapter and pass it the very same DataTable you Filled using the OracleDataAdapter.