Function to compare 2 multirow DAO recordsets

[pmagee1024]

Looking for some help from you experts to compare 2 multirow DAO recordsets. I have code that loads the recordsets correctly. Just trying to compare them and am having problems getting started. For some details:

My data source is a Windows permission scan. It has a path and 3 other fields related to permissions granted on that path including accountname and permission. The fields take the form

strPath bInherited(0/-1) strAccountname strPermission

There can be several rows describing the permissions granted per path. A recordset contains however many permissions there are for a given path. Usually 4-8 per path/recordset.

rs1 contains a path (select distinct) and is used as a guide to all paths for a given database.
rs2 contains the same path as rs1 (child path) and 3 other fields as described above.
rs3 contains the parent path of rs2 (parent path) and 3 other fields as described above.

Looking to compare rs2 and rs3 to see if they match exactly (all 4 fields). If yes go to next path on rs1. If not, then kick out a list into a new table in the same DB.

Would anyone be willing to assist me? I've done VB6 coding but no VBA and seriously nothing like this using DAO and recordsets.

[twinnyfo]

pmagee1024,

The concept is really simple, and I think you have already done the majority of the work yourself.

Using rst2 and rst3, just compare the values of the corresponding fields:

If rst2.Fields(0) = rst3.Fields(0) then ... etc.

Since you know how many fields are contained in each recordset, you could even use a variable with a For...Next loop:

For intX = 0 to 3 'Fields are numbered from 0 to (n-1)
    If rst2.Fields(intX) = rst3.Fields(intX) then ... etc.
Next intX

There are many ways to slice and dice this problem, and this is just one simple option. Having a Boolean variable indicating whether there is a discrepancy would be helpful, also.

Hope this gets you on the right track.

[jforbes]

Yes, you can use the RecordSet structure you have defined and approach it the way Twinnyfo describes. It's probably the most straight forward way with what you have already, but I would like to offer a couple of options.

The first option would be instead of using two RecordSets, rs2 and rs3, I would only use one and order it by the Path column. Then as you are walking through the RecordSet, right before .MoveNext save the column values off to Variables. Then when you are on the next record, you can compare the current RecordSet's values against the values set in the Variables from the previous record and look for discrepancies.

The Second Option, and the one I would recommend, is to create a Query to give you the Exceptions. If you have a Table with all of these values in it, you could create a query that links the table to itself on the Parent/Child relationship and have a Where Clause for records with different permissions. Then you can create a report based on the Query or open it as a RecordSet in code and do whatever action you need to accomplish.

[pmagee1024]

Thanks twinnyfo, that actually worked better than I expected. I have it running as I needed it to. My sincere thanks!