How to store a query result in an external db-table?
Hi.
Do you know how to store a query result in an external db-table?
For example: I have to make a query using ODBC that point to AS/400 tables, but the result I have to store it in Access.
'iSeries IBM Driver for AS/400
Dim conMacPac As OdbcConnection = New OdbcConnection(conStrMacPac)
Dim cmdMacPac As OdbcCommand = New OdbcCommand(strSQL, conMacPac)
Dim custDAMacPac As OdbcDataAdapter = New OdbcDataAdapter(cmdMacPac)
Dim custDSMacPac As DataSet = New DataSet()custDAMacPac.MissingSchemaAction = MissingSchemaAction.AddWithKey
custDAMacPac.Fill(custDSMacPac, "qryIBMReferenceResult")
How store this dataset in a new table in MSAccess?
'MSAccess
Dim conMdbResults As OdbcConnection = New OdbcConnection(conStrMdbResults)
Dim cmdMdbResults As OdbcCommand = New OdbcCommand(strSQLResult, conMdbResults)
Dim custDAMdbResults As OdbcDataAdapter = New OdbcDataAdapter(cmdMdbResults)
Dim custDSMdbResults As DataSet = New DataSet()custDAMdbResults.MissingSchemaAction = MissingSchemaAction.AddWithKey
Then?
NOW, HOW CAN I STORE THE RESULT FILLED IN 'custDSMacPac' TO A NEW OR EXISTING TABLE OF ACCESS
PLEASE HELP ME OUT!Thanx in advance.
Hi,
If, the access database is existing (and the table structure must be the same) then it would be much easier. In your sample, after creating your dataset:
(
this is a pseudocode)
custDAMdbResults.FillSchema(custDSMdbResults);for each (DataRow dr In custDSMacPac.Tables[0].Rows) {
DataRow nr = custDSMdbResults.Tables[0].NewRow();
// copy fields
nr["col1"] = dr["col1"];
...
custDSMdbResults.Tables[0].Rows.Add(nr);
}
//Send updates to the adapter
custDAMdbResults.Update(custDSMdbResults);
If the table or database doesn't exist then you'll have to add codes to create a new database and table for access...
cheers,
Paul June A. Domag
thank you very much buddy!
Is there any way to do it in just one line? This is because just figure out to read about 300,000 records! It could be very slowly, couldn't it?
Cheers!!!!
Hi,
Well, there's no "one-line" solution for this. But here's a shorter path...
Open your external database via the command object, and execute a reader:
OleDbCommand cmd = new OleDbCommand("<myQuery>", <myConnection>);
OleDbDataReader reader = cmd.ExecuteReader();
After that, open your mdb (destination database) by using a dataadapter and a datatable. Use the DataTable's Load method to populate the values...
MDBDataTable.Load(reader);
cheers,
Paul June A. Domag
