recordset modified to insert in another table

[Palmero]

Hi all,

In a VB6 procedure, I have a recordset from table t1 and i want to modify some fields and insert the new value of this recordset in another table t2. t1 and t2 have the same structure.

Here is my code :

SQLquery = "select * from t1"
Set rs1 = db.OpenRecordset(SQLquery, , 3, 3)
Do Until rs1.EOF
rs1.Edit
rs1.Fields("code&quot = "code1" 'code is modified
rs1.Fields("libelle&quot="libelle1" 'libelle is modified
'here, I want to insert rs1 in table t2, which has the same structure as t1
rs1.MoveNext
Loop
rs1.Close

Thanks in advance for your help.

 

[sunaj]

Hi,

You are using a syntax that indicates that you are using DAO(?). If you are usinging ADO consider changing to
rs1.open SQLquery,YourConnection[String]

Anyway, you basically have 3 options
1) Use the recordset to update the database. Make sure that the recordset is updateble (rs1.Open "t1", YourConn, adOpenKeyset, adLockOptimistic, adCmdTable) and use the .addnew and .update methods to insert new values.
2) use the connection directly. YourConn.execute "INSERT INTO t1(code,libelle) VALUES ('" & NewCode & "','" & NewLibelle & "')"
3) Use a stored procedure to do your update. If you are using a 'mature' database consider doing it all in SQL. Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[Palmero]

In fact, rs1 has many many fields.
Can I insert using rs1, not referencing the fields' names?

Thanks for your help.

 

[sunaj]

If you choose solution 1) I would probably use 2 recordsets; one for the looping t1 and one for updating t2.
When using .addnew you need to refer to the fields. the short synax is:
-----------------------------------------------------
rs2.addnew array("MyField1","MyField2&quot, array("'" & MyString & "'",MyNumeric)
rs2.update
-----------------------------------------------------

So to sum up things, you code could look something like:
-----------------------------------------------------------
Dim Conn as connection, Rs1 as recordset, Rs2 as recordset
Set Conn = new Connection
Conn.Open YourConnectionString
Set Rs2 = new recordset
Rs2.open "t2", YourConn, adOpenKeyset, adLockOptimistic, adCmdTable
Set Rs1 = new recordset
Rs1.open "select code,libelle from t1"
If not R1.eof and not Rs1.bof then
Rs1.movefirst
while not rs1.EOF
Rs2.addnew array("code","libelle&quot,array("'" & NewCode & "'","'" & NewLibelle & "'&quot
rs1.MoveNext
wend
Rs2.update
else
msgbox "No entries to process"
end if
rs1.Close
set Rs1 = nothing
Rs2.close
Set Rs2= nothing
----------------------------------------------------------
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[Palmero]

Thank you very much sunaj for your help, but what would be the syntax if I want to user
set rs1=db.Openrecordset(SQLquery,,3,3)?
idem for rs2.
Thanks in advance.

PS : I'm using an Access 97 database.

 

[sunaj]

That depends on your references. Are you using DAO or ADO?
If use ADO you should use the syntax I have showed above.

Its a long time ago that I used DAO but I guess that the synax is similar e.i. something like
set rs2 = db.openrecodset "select * from t2", adOpenKeyset, adLockOptimistic
Where you would have to replace the constants.... Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'