0

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.

Aiken
  • 111

2 Answers2

0

Create a backend database with all your tables and establish the relationships on that database. Then link all the tables on the backend to your frontend database that has all your forms, queries and code.

leach613
  • 21
0

Solution I'm currently using:

In the table design for tblPermissions, select the User field and then in the Lookup tab for the field options set the following options.

Display Control: Combo Box
Row Source Type: Table/Query
Row Source: SELECT Username FROM tblUsers;
Limit To List: Yes

This prevents the User field in tblPermissions from containing a value that is not present in the Username field of tblUsers due to the "Limit To List" option being set to yes for the appropriate row source.

Aiken
  • 111