I'm currently constructing and maintaining a number of MS Access databases intended to replace paper document systems where I work. As part of this work I'm using simple username/password combinations in place of a signature on the document and would like to keep the username/password combination common between all documents, to this end I created a separate database containing just the user information that's common to all the different documents.
However for each document database I'd like to have a 'permissions' table which has a set of Yes/No fields defining what permissions a user has pertaining to that particular document, obviously I'd like to maintain referential integrity between the permissions table and the users table. All the databases in question are stored on a shared drive.
At first I thought that in WhateverDoc.mdb I could link to the tblUsers table in Users.mdb and then create a one-to-one relationship between the username field of tblUsers and tblPermissions but it seems like I'm not able to enforce referential integrity when I create a relationship with a linked table.
Is there a workaround for this? I'm open to solutions involving VBA but thought I'd ask here rather than StackOverflow as it's not specifically a programming question.