By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,700 Members | 1,349 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,700 IT Pros & Developers. It's quick & easy.

Function to compare 2 multirow DAO recordsets

P: 2
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.
Mar 9 '15 #1
Share this Question
Share on Google+
3 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,486
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:

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. For intX = 0 to 3 'Fields are numbered from 0 to (n-1)
  2.     If rst2.Fields(intX) = rst3.Fields(intX) then ... etc.
  3. 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.
Mar 9 '15 #2

jforbes
Expert 100+
P: 1,107
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.
Mar 9 '15 #3

P: 2
Thanks twinnyfo, that actually worked better than I expected. I have it running as I needed it to. My sincere thanks!
Mar 13 '15 #4

Post your reply

Sign in to post your reply or Sign up for a free account.