Results 1 to 3 of 3
  1. #1
    Hendro623 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Dec 2016
    Posts
    14

    Using and having link between 2 tables with the same ID

    I've created an employee database but was asked to split off the security information into a separate table. The idea was to have other front ends that aren't connected to those specific tables to strengthen up confidential information. I'm pretty sure i went about this the wrong way and not sure how to get this setup correctly.... looking for some quick suggestions.

    The attachment shows the 3 tables i'm dealing with now. I'm pretty sure I need a "SecurityID" in the employee table to reference the security table, but I have that being linked to the keyholder table already.

    I had imported all the information into the security table which allowed it work, but with no link to the actual employee list, records will never be updated when new employees are added.

    I have the 2 tables (security and keyholder) split up because the records in the security table are all unique to each employee, there will never be a duplicate. As for the keyholder table, an employee might have key access to multiple buildings, so I have that setup as a one to many relationship.

    I'm hoping this is enough detail for someone to see what I have going on. I'm pretty sure its something simple and i'm just over-thinking it, could really use an outsiders opinion.



    If more information is needed, let me know.

    Thanks for the help!
    Attached Thumbnails Attached Thumbnails Relationship.JPG  

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Seems that you are concerned with relationships. If some fe's don't have links to the security info, relationships that you define in a master fe that you distribute become invalid which might raise error messages. There is nothing that says you must create relationships, so that can be one answer if you're willing to ensure all queries/code ensures data integrity. Another possibility is that you have an fe version for those who can interact with this data, and one for those who cannot, although that can introduce more work in that a report/query/form that gets added may have to be worked into both versions.

    Probably other/better suggestions are forthcoming...
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Hmm, I'm a little confused. Sure it's easy enough to disable the Shift Key, hide the Navigation Pane and turn it into an .ACCDE but splitting the table does not prevent someone from going to the Backend and just opening the Table. So splitting the Tables really doesn't offer any real security... at least from what I can see. I mean the lock is to keep the honest guy honest but the thief will find a way.

    I'm thinking the only semi-sure way is to do what I said to the Frontend and put the Backend on an SQL Server where you can then control who has access to it and for extra security run a script when the database opens to connect to said Table and disconnects on closure. However, being connected to an SQL Server, even if they walk away with the Frontend they have nothing as there will be no Backend they can get to.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 06-08-2017, 07:32 AM
  2. How to link 2 tables
    By smba in forum Access
    Replies: 2
    Last Post: 02-29-2016, 08:32 AM
  3. link tables using vba
    By dumbledown in forum Import/Export Data
    Replies: 3
    Last Post: 03-09-2012, 05:27 AM
  4. Link tables
    By compooper in forum Import/Export Data
    Replies: 2
    Last Post: 06-23-2011, 07:08 PM
  5. Best way to link tables together?
    By marleyuk in forum Access
    Replies: 1
    Last Post: 06-07-2009, 08:44 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums