SQL statement INSERT INTO db2 file from an Access Table

[Guest.Visitor]

If the db2 file is defined as a linked table within the Access DB you would be able to do this. INSERT INTO MYLIB_MYFILE (SELECT * FROM myAccess table...)

[TonyT]

REVERSE VERSION: IS IT POSSIBLE TO INSERT FIELDS TO AN ACCESS FROM DB2 LIKE: INSERT INTO ACCESS ( SELECT * FROM DB2 ). ANY CODE EXAMPLE IS VERY MUCH APPRECIATED. THANKS

[Diane Mueller]

Mike, Your "linked table" response, actually, that is what I am doing now to get the Access data into the 400 is through the linked table. Since my link is already defined, I should have the ability to do this--great. I intended to delete this link when I had the vb.net program working, but from what you had just told me, it looks like I will need to keep this. Just curious, if the link was not defined within Access, would there be any other way within .NET & recordsets that I could accomplish this?? Thanks for your response! Diane

[M.Savino]

Diane, I no loner work with ACCESS and VB on a regular basis.... But if there are no restricitions on having connections open to two separate databases, this might work. Open a connection to ACCESS Open an ODBC connection to the AS400 Create the ACCESS recordset Iterate through all rows For each row Retrieve the data you need from the ACCESS columns Build an SQL INSERT statement in a string variable, similar to this: strsql= "INSERT INTO AS400Table (FLD1) VALUES(" + "'" COL1 +"'" + ")" Pass the String Variable to the Execute Method of the AS400 connction Object. Next Row

[Diane Mueller]

Mike, I wondered about that process, read a record, write a record, wasn't sure if I should do it that way. Really was hoping to insert my selection - which I do have that working now with the help of Mike Faust, (insert to the 400 file (select from the access table) using the "linking" table as he suggested. Just for testing and learning, I will try the logic that you have described above to see how I can work with two open connections and inserting each record, one at a time. Thanks for your post! Diane

[Diane Mueller]

I'm testing a .NET application that contains a recordset from a sql statement, (select * from myfile where etc....). Now that I have the records, I would like to INSERT INTO mylib.mydb2file, using the data from the above select statement. In theory----> INSERT INTO mylib.myfile (select * from myAccessTable where etc.....) Is it possible to combine an SQL statement using a db2 database with the INSERT statement and an Access table for the select statement? Diane

[Diane Mueller]

Mike, Thanks for the post! All is working. Diane