Re: compare 2 recordsets

Yes, 2 databases, so I can't do it in SQL.
Actually they are different recordsets as one is an ADO recordset and the other one
is a dhSQLite recordset. dhSQLite is a VB wrapper for VB.
It is the ADO recordset that I need to alter, that is throw out records where the ID
doesn't appear in the dhSQLite recordset.

What I currently do is move the ADO recordset to SQLite and then do the job in SQL,
that is delete the rows in the table made from the ADO recordset where the ID doesn't
appear in the other SQLite table. This works fine, but it is a bit slow as moving the
records from the ADO rs to SQLite takes a bit of time.
I thought I could make it a lot faster by first manipulating the ADO recordset and then
moving the data to SQLite.

As a matter of fact to make things more confusing, all the data originally comes from
an Interbase database and I could do all in SQL, but that puts too much strain on the system
(on the clinical frontend) and that is not an option.

I thought it could be something along these lines:

Sub FilterRecordsetByParentNode(strDB As String, _
rsNode As ADODB.Recordset, _
oParentNode As Node, _
oNode As Node, _
lFieldNode As Long, _
lFieldParentNode As Long, _
Optional bFirst As Boolean, _
Optional bLast As Boolean, _
Optional bNOT As Boolean)

Dim rsParentNode As cRecordset
Dim strSQL As String
Dim strPID As String
Dim strCriterium As String

strPID = GetTablePIDType(strDB, oParentNode)
strSQL = "SELECT " & strPID & _
" FROM A" & GetNodeName(oParentNode, True) & "_J " & _
"ORDER BY " & strPID & " ASC"

'get the cRS from the main table of the parent node
'will need to check if an array may be faster
'--------------------------------------------------
Set rsParentNode = GetcRSFromDB(strDB, strSQL, True)

If bNOT Then 'doing opposite

Else
rsNode.MoveFirst
Do While rsNode.EOF = False
strCriterium = strPID & " = " & rsNode.Fields(lFieldNode)
If rsParentNode.FindNext(strCriterium) = False Then
rsNode.Delete adAffectCurrent
End If
rsNode.MoveNext
Loop
End If

End Sub


Problem for now is that my ADO recordset is not updatable, so will need to do something
about that.
Sorry, if it is all not clear, but your assistance is appreciated.


RBS



"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message news:eaTFtPjAIHA.4584@xxxxxxxxxxxxxxxxxxxxxxx
RB Smissaert wrote:
What would be the best (fastest) way to do this:
I have 2 ADO recordsets say A and B.
They both have a common field, say ID
Now I need to delete all the records in recordset A where
the value in the field ID is not found in the field in recordset B.
These are values of the datatype Long.
The ID field will be sorted ascending in both recordsets.

Now, I could do this with a double loop, but that could be a bit slow
and I wonder if this could be done better with a filter or find.

Why are you using recordsets for this? Are the recordsets from different databases?
If not, this is a job for SQL.
If so, there's no alternative to looping through one of them, but you can use Filter to get the corresponding records in the other recordset.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.

Relevant Pages