MS Access how to over come the max amount of relationships

Well how many indexes do you have on tbl_user?

Actually, Anders, I think the bigger question is how many other tables are there?

I don't generally create relationships between the ModifiedBy or CreatedBy columns of my tables and an employees table, which I believe is what Dustin is describing.

Dale

Well even that SHOULD only create 2 indexes. Sure it could create many foreign key constraints, but only 2 indexes. So I wonder if something else is at play. Could you screenshot the index description?

Thank you. I am currently out of my office but tomorrow I will try to get a screen shot. There are many tables involved. Just guessing 30 or so.

Ok well I have 51 tables in my BE so that is more than I estimated.  Basically I want in most of my tables a field for EmpIDadd and EmpIDEdit so I can track you added what and who edited what.

So if I can't check the Employees table to make sure the values are correct then what do I do???? As bad as it sounds do I just not do anything and add up the maximum number of relationships I can to what I think is the most important tables?????

Pat what you said kinda threw me for a loop.  I don't understand exactly.

So, what I do is to disallow deletes from the employee table except under special security.  In that case I check 6 of the major tables to be sure that there are no references to the EmpID that the admin is requesting to delete.  

Thanks for the help.

Dustin,

What I believe Pat was stating is that she prevents users from perform a deletion from the Employees table.  Instead, she uses an additional column (InActive) as a flag (probably a YesNo field) to indicate that that employee is no longer employed.  Then, all forms or queries which use the Employees table would be filtered to only show the records where [InActive] = 0 (or maybe it was [Active] = -1).

Her "special security" comment probably has to do with an Admin user being allowed to delete employees, but only after confirming that the EmpID is not used in any of the EmpID fields within the 6 most important tables.  If it is, I'm guessing that she prohibits that record (EmpID) from being deleted, even by the Admin.

Thanks Dale. That's what I got from the comment. I just wanted to make sure I was on the same page. My main concern for now is tracking who changes and adds what to my other tables. If I can't check the Employees table from the current table I am in to verify it is a correct value then what do I do? There can't be a relationship on every table back to the Employees table.

I hide my login form rather than closing it so once the userID and password are validated, it is available throughout the app.  If the user is not active, I do not allow him to log in.  I don't like not being able to enforce RI on all tables.  You can still do it for the most important ones and then do some logic checks as I do for the rest.  It's a calculated risk forced by a limitation of Jet/ACE but an acceptable one.  Nothing really bad would happen if the employee record got deleted after he had only updated a couple of records in some obscure infrequently used table.

Pat,

I have not tried this, because I haven't had a need to implement data macros in most of my applications, but do you think you might be able to enforce RI via a data macro?

Dale

As of right now I set a control value on
 A hidden form as soon as the employee logs in successfully. This closes the login screen but the value is saved on the main hidden form. I don't really like the thought either of not being able to enforce relationships as this is the main feature of a relationship database.

Dale,
I don't know.  They might work.

I don't really like the thought either of not being able to enforce relationships as this is the main feature of a relationship database.

If you use SQL Server as the BE, there won't be a problem.  This is only a limitation of Jet/ACE.  Try Dale's suggestion to use Data Macros.  You will need two or three for each set of tables in the relationship since you have to validate add/change/delete actions.  Although, you might be able to consolidate if you can use TempVars for the variable end of the relationship.    I don't know how DM's are implemented behind the scenes but as long as they don't attempt to create an index (which is the real limitation), they could work.

Please let us know how this works out if you decide to try it.

Thanks for the help. When I get time to try the macro I will report back.