Solved

Comparing Recordsets

Posted on 2004-09-29
9
559 Views
Last Modified: 2013-12-25
I want to compare the last two rows in a table and im trying to do this by creating two recordsets containing the same data. Is there a better way to do this? And is there a way to compare the recordsets as a whole instead of comparing the fields in the recordsets eg if Recordset1 = Recordset2. Thanks
0
Comment
Question by:oagunbiade
9 Comments
 
LVL 7

Accepted Solution

by:
SQL_Stu earned 125 total points
ID: 12178616
You could use Recordset.Clone to mirror your original recordset.

Perhaps you could join all the fields together into 1 string and then compare the whole record against the clone?
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 12179197
What exactly do you need to know ?
Do you want to know if there are more records in one table (or recordset) than in the other ?
Or do you want to compare at field level to see if the data is exactly the same ?
0
 

Author Comment

by:oagunbiade
ID: 12179251
Yes i want to compare 3 fields to see if they are exactly the same as the previous rows 3 fields.
0
 
LVL 3

Expert Comment

by:Arundel_Castle
ID: 12185673
What database are you using. You could write an SQL statement to do this for you then just fit it into a function call in your code. Tell me what database.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 3

Expert Comment

by:sgayatri
ID: 12188442
You can use a single ADO recordset  to do this
kount = 0  '' your row count
str1= "select f1,f2,f3 from yourdb"

rs.open str1,cn,adopenkeyset  'Where cn is your ado connection
if rs.eof = false then
appstring_next= rs(0) & rs(1) & rs(2)
appstring_prev = "" 
end if
while rs.eof = false
  kount = kount + 1
  if appstring_prev = appstring_next then
     msgbox kount  & " and " & kount -1 & " rows are similar " 
 end if
rs.movenext
appstring_prev = appstring_next
appstring_next= rs(0) & rs(1) & rs(2)
wend

Infact this code will find out any two adjacent rows having two fields as similar ....

 

 



0
 
LVL 18

Expert Comment

by:JR2003
ID: 12211320
oagunbiade,
I agree with Arundel_Castle you should do this with an sql statement. something like below. Not that both tables in the from statement are the same table with different aliases.
If the result is 0 (zero) then the last 3 fields don't match.
JR

select count (*)
from Table1 as A, table1 as B
where A.fieldPK = 'XYZ'
   and B.fieldPK = 'XYY'
   and A.field1 = B.field1    
   and A.field2 = B.field2    
   and A.field3 = B.field3    
0
 
LVL 5

Expert Comment

by:Pi7
ID: 12235754
oagunbiade, did you say you want to compare the LAST two rows? If so then you can do it in fewer steps using one recordset
Dim Match as Boolean
Dim LastRow as String
Dim PrevRow As String

With rs   'your recordset
      .MoveLast
      LastRow= Cstr(.Fields(0)) & " " & Cstr(.Fields(1)) & " " & Cstr(.Fields(2))   'concatenates the values in the first 3 fields and assigns it to a string variable
      .MovePrevious
      PrevRow =Cstr(.Fields(0)) & " " & Cstr(.Fields(1)) & " " & Cstr(.Fields(2))   'concatenates the values in the first 3 fields and assigns it to a string variable
End With

Match=( Instr(1,LastRow,PrevRow)>0)
   
0
 
LVL 5

Expert Comment

by:Pi7
ID: 12235763
Of course before the code I submited you should select all the records in the table(either by using the ado control at design time or using the sql select statement,which I prefer)

regards,
Pi7
0
 

Author Comment

by:oagunbiade
ID: 12246952
Thanks everyone for your contribution but the clone method seems to work perfectly.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Saving history changes to sub form 4 35
Using "ScreenUpdating" 6 55
clicking a shape in a frame array vb6 3 39
Child Form in front 4 34
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now