MS Access how to over come the max amount of relationships

In my DB I have a table called Employees and the key is EmpID. EmpID is a foreign key in all my other tables.

In all the other tables I have EmpIDadd and EmpIDedit

Wouldn't I want to enforce a relationship between both of these and my employees table?

I keep getting error "The operation failed. There are too many indexes on table 'Employees'. Delete some of the indexes on the table and try the operation again."

Thanks for the help.
LVL 1
Dustin StanleyEntrepreneurAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
Well how many indexes do you have on tbl_user?
0
Dale FyeCommented:
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
0
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dustin StanleyEntrepreneurAuthor Commented:
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.
0
PatHartmanCommented:
Access limits the relationships to 32 for a single table so it is usually not possible to create relationships to the ChangedBy field in each table.  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.   The employee record always has an ActiveFlag and if the flag is set to No, that person cannot log in.
0
Dustin StanleyEntrepreneurAuthor Commented:
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.
0
Dale FyeCommented:
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.
0
Dustin StanleyEntrepreneurAuthor Commented:
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.
0
Dale FyeCommented:
Well, as long as you are setting either a global variable, a tempvar, or a control on a form with a value of the EmpID when that Employee logs into your application, then you are pretty much assured that when you write a value to those EmpIDAdd or EmpIDChanged fields, that the value you enter will exist in the Employees table.

And as long as you don't give users the ability to delete records from the Employees table, then once a value is entered in theEmpIDAdd or the EmpIDChanged fields, then you should not need the relationship between those fields.

You can always create that relationship in a query if you want to know who the person was that added or changed the record.

Dale
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
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.
0
Dale FyeCommented:
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
0
Dustin StanleyEntrepreneurAuthor Commented:
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.
0
PatHartmanCommented:
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.
0
Dustin StanleyEntrepreneurAuthor Commented:
Thanks for the help. When I get time to try the macro I will report back.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.