• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

VB6, Access 2003 Compare and update tables

Hello Experts,

I am working with VB6 and Access 2003

The first table has a list of reports

The second table handles the report security.  There should be one line for each report for each employee.  

I am trying to loop through the second table (recordset) checking to see that each employee has a line for each report.  
If there is not a report line then it would be added to the recordset.  

This should be so simple but I am stuck.  Could someone help me out with basic code?  Thanks so much for your time!  Kim
1 Solution
You don't necessarily need to use VB for that.  I've achieved what I think you're trying to do with a few queries and a psuedo-key to be used in Access' own Find Unmatched Query type, converted to either a Make-Table or an Append query type.

We have two basic tables - one each for employees and reports then a third (rather than second as in your question) which joins the two together with an extra field for the permissions (which I default to text "ReadOnly" for now.

qryAllCombinations finds all the possible combinations with the two tables and builds a psuedo-key from the respective table IDs.

qryInitialPopulatePermissions will (re)create the table from the above
Alternatively qryUnmatchedCombinations will see which of the possible combinations are not already in the main permissions table and from that, qryAppendMissingPermissions will add the missing ones in.

In fact, as long as the report permissions table already exists, the append will do the same job as the initial populate query - it just has to append all of them to a blank table.

Hope it helps.

KimD2Author Commented:
Thanks so much!

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now