Duplicating record as a new record in a large number of tables

[kishorkhanal]

Scenario:
I have "Master" table with fields "Job No" and "Revision No". Both together is a primary key, so that combination of both cannot be duplicated. I have 100 other tables to be related with referential integrity(+update&delete) to Master for both fields. Due to 32 limit rule, I had to come up with workaround method to have all 100 tables in the relationship. So, I created 5 other SubMaster1, SubMaster2, ...., SubMaster5 which are related to Master with relationship with referential integrity (+update&delete). Then I assigned 20 tables to each SubMaster so that 20 tables are related to each SubMaster table. Whenever I create new record in Unit, the new record is generated in each SubMaster using update query for each SubMaster table. I have all the forms and necessary query laid out. The only missing part is being able to duplicate a record. I have limited knowledge in VBA, but I should be able to modify it to address to my requirement.

Feature Requested:
I want to copy a given record in Master, SubMasters and 100 tables as a new record. I need this feature so that I can select certain Job No and Revision No and copy that as a new Job No(assigned manually in a form) and 0 as the revision number. Possibly a button which will ask for new job number and copy everything from the active Job No and Revision No to a New Job No and "0" Revision No. The existing record may not be there on all 100 tables for the given Job No and Revision No. If it is there, then copy otherwise ignore for each of the tables.

Note
I have a table "ItemList" which lists all the unique name of the 100 tables. I do not know if this will come handy to build the code.

Looking forward for a solution from the experts. Please let me know if you need any more clarification.

 

[xenou]

Due to 32 limit rule, I had to come up with workaround method to have all 100 tables in the relationship.

I am not familiar with this rule. Can you say more about it? As far as having 100 tables involved in a relationship, the problem sounds like it comes from poor design. This isn't a reasonable why to build an access database. Why so many tables here?

 

[kishorkhanal]

Because each of 100 tables have different fields except Job No and Revision No. I cannot get more than 32 tables in a relationship to the Master table. So the roundabout way of assigning 20 table to each SubMaster tables solves the problem as I mentioned in my first post. The reason behind coming up with this format is that I have a Master form where all the 100 forms are added as subforms, each of the subforms collect different variables. If I put all the 100 tables in one table, I am getting too many fields to work with and was not practicable, and I am not sure how many fields a table would allow.

Is there any other way you can think of?

 

[xenou]

You can have 100 fields in a table (actually up to 255). That sounds preferable to 100 tables if the only point of 100 tables is to avoid 100 fields. Then you could also remove the 100 subforms and everything will be much much better.

 

[kishorkhanal]

You can have 100 fields in a table (actually up to 255). That sounds preferable to 100 tables if the only point of 100 tables is to avoid 100 fields. Then you could also remove the 100 subforms and everything will be much much better.

Each table has in average 6 to 8 fields which makes a total of 600 to 800 fields.

 

[xenou]

Okay. Three tables should do it then, as a point of fact. But you have a large dataset here so again, the design needs to be reviewed before creating elaborate workarounds that make it hard to work with the data. Have you studied the fundamental concepts of database design, such as normalizing your tables?