Referential Integrity

[llkhoutx]

I have a two tables, each with 73 related tables utilizing the same key.

I've programmitically generated cascading update, cascading delete relationships between them the parent and the 73 children, but get an error on the 26th such relationship. Too many related tables.

Is there any way around this? I can manually track children, but Access does it so handily.

The 73 tables uniquely describe characteristics of different data. Consolidating and keying those tables at this juncture would be a nightmare this late in the project with hundreds of forms and reports.

I have just come to need the cascasing delete.

I'm going to try dynamically adding a relationdship when needed and deleting it prior to creating another. Anybody done that?

Suggestions? Poor table design I know.

 

[Pat Hartman]

I have to agree about the table design. Are you sure you don't want to try to change it?

If you have MSDE as an option, it can handle more relationships. I made some modifications to a client developed db with exactly the same problem. I ended up converting it to SQL server.

 

[llkhoutx]

Next release, I'll redesign the tables. MSDE and SQLServer are not viable options for this application. I'm not as sophisticated as I thought and my third party customers technophobes in very small offices.

My original thinking was that the 73 tables would reduce network traffic and record conflicts, that is, passing small tables over the network rather than one big table.

I've still got to try the creation/deletion of the one-to-many relation on an as needed basis.

Thank you for the sage advice.