question about copying a record

Is there a way to copy a record from one table to another with ADO code if the two tables have identical fields? Also, is there an easy way to delete ALL records in a table using ADO code?

Thank you,
Thai

1. To copy one record from one table to another, assuming that I know the ID of the record I want to copy. Lets call them Table1 and Table2:

Code:

---

Dim cn As New ADODB.Connection

'Open connection
'Substitute it with appropriate one.
cn.Open "DSN=MyDSN","UserName", "password"
cn.Execute "Insert Table2 Select * From Table1 Where ID = 1"

---

2. Deleting all records in the table:

Code:

---

Dim cn As New ADODB.Connection

'Open connection
'Substitute it with appropriate one.
cn.Open "DSN=MyDSN","UserName", "password"
cn.Execute "Delete Table1"

---

dim cn as new adodb.connection
cn.execute "Select * From Table1 Where Number = 10"

now.. how do you manipulate those records it just retrieved??

Thanks for the help,
Thai

You have to return the results of your query to a recordset object:

Code:

---


dim cn as adodb.connection
dim rs as adodb.recordset

set cn = new adodb.connection
set rs = new adodb.recordset

cn.open <connectionstring>

'open recordset, you may then work with it
rs.open "Select * from MyTable", cn, adopenstatic, adlockoptimistic, adcmdtext

'do your stuff here
msgbox rs.fields(0).value

---