Web Development

Ask something
Discussion Locked

VB6 and ADO Recordset

0 helpful
7 replies

by setivi

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have two identical tables A and B, the same fields etc. but table B is the smaller of the two. I want to write or transfer every record from table A to table B whose value in the field "Payment" is true.

I am trying to avoid field by field assignments. There are two hundred fields. Writing an entire record at one swoop in the do or while loop is
my wish.

7 replies

by rmrsalisbury

The simplest way to accomplish what you want is to let the database do the work, using the ADO command object

Dim adoCommand As New ADODB.Command

With adoCommand
.ActiveConnection = conn
.CommandText = "INSERT INTO tableB SELECT* FROM tableA WHERE payment = True"
.Execute
End With

by vinnyd

The easiest way I know is to use an SQL Query.

INSERT INTO tableb
SELECT *
FROM tablea
WHERE field="Payment";

by guan

You can loop in the recordset that contain all selected rows which have value in the field "Payment" is true and using following procedure to assign all fields in the recordset.

Private Sub CopyRS(ByVal rsSource As ADODB.Recordset, ByVal rsDest As ADODB.Recordset)
Dim F As ADODB.Field
If rsSource.State = adStateClosed Then Exit Sub
If rsSource.EOF And rsSource.BOF Then Exit Sub
If rsSource.CursorType <> adOpenForwardOnly Then
If Not rsSource.EOF And Not rsSource.BOF Then
rsSource.MoveFirst
End If
End If

Do While Not rsSource.EOF
rsDest.AddNew
For Each F In rsSource.Fields
If F.Type <> adChapter Then rsDest(F.Name).Value = F.Value
Next F
rsDest.Update
rsSource.MoveNext
Loop

End Sub

This discussion is closed to new replies.