VB6, Access 2003 Compare and update tables

Posted on 2011-04-29
Last Modified: 2012-05-11
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
Question by:KimD2
    LVL 1

    Accepted 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.


    Author Closing Comment

    Thanks so much!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    760 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

    8 Experts available now in Live!

    Get 1:1 Help Now