|
Subject:
|
ADO recordset to table
|
|
Posted By:
|
kanoorani
|
Post Date:
|
5/15/2006 12:31:31 AM
|
good morning everybody
...i've a ADO named "Distributor" with cmdText source ... the sql statement is "SELECT * FROM Distributors ORDER BY DistributorID;"
now i've placed button, whenever i press that button, all the records that are in the recordset of Distributor should be copied to a table that is named as "DistributorTwo", but before this, the table "DistributorTwo" should be emptied.
i m sorry my english is poor, but i hope u'll understand my problem.
w8ing 4 a good reply
|
|
Reply By:
|
peko
|
Reply Date:
|
5/15/2006 3:11:13 AM
|
Hi,
Private Sub Command4_Click()
'you use ADO Connection object (named e.g. Conn)
Conn.Execute "DELETE * FROM DistributorTwo"
Conn.Execute "INSERT INTO DistributorTwo SELECT * FROM Distributors ORDER BY DistributorID"
End Sub
|
|
Reply By:
|
kanoorani
|
Reply Date:
|
5/16/2006 12:24:14 AM
|
ooo no no no no
actually i dont want this... i want 2 copy the records from the recordset, not from the table ... coz ... the recordset is sometimge based on some criteria, there4 everytime the recordset is different, but the way u've told will copy all the records from one table 2 the other ... i want 2 copy the records from the recordset to a table
plz try 2 help me
|
|
Reply By:
|
peko
|
Reply Date:
|
5/16/2006 2:59:52 AM
|
Ok,
You may use some cycle e.g.:
Private Sub Command4_Click()
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim i As Long
Set conn = 'set your connection
conn.Execute "DELETE * FROM DistributorTwo"
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
rs.ActiveConnection = conn
rs.Open 'set your SQL command to open
rs2.CursorLocation = adUseClient
rs2.CursorType = adOpenStatic
rs2.LockType = adLockOptimistic
rs2.ActiveConnection = conn
rs2.Open "SELECT * FROM DistributorTwo"
Do While rs.EOF = False
rs2.AddNew
For i = 0 To rs.Fields.Count - 1
rs2.Fields(i).Value = rs.Fields(i).Value
Next i
rs2.Update
rs.MoveNext
Loop
rs2.Close
rs.Close
conn.Close
Set rs2 = Nothing
Set rs = Nothing
Set conn = Nothing
End Sub
Peko
|
