Problem: Adding a record across multiple tables.

[jimster68]

I have three tables setup in a 1 to 1 relationship using a Autonumber field as their primary keys. I created the seperate tables to group related data together and reduce the tables sizes in general. So think of it as one main table and two tables of extended data.

I need to be able to add a record to table 1 and have it use the same autonumber to reserve that row for data in tables 2 & 3 so that my relationships stay in sync. I want to update the data in tables 2 & 3 later when that information is available.

I have been breaking the database testing some things out like building a form from a select query using all three tables. When I add the record to table 1 nothing is added to the other two. Things get messy on subsequent attempts to add data to any of the tables.

I have avoided this problems in other tables by not using the autonumber datatype, however, I don't have a good unique key that can be manually entered and kept accurate, so autonumber solves that problem in this case.

My VB is very rusty but I'm thinking there should be a way, before update, to capture the autonumber being used for the new record and write it to the other tables. I'm thinking that would be one solution, but I can't seem to get started on that code. Any help or examples would be great.

Thanks,
Jim

 

[The_Doc_Man]

First and foremost, the main table should be updated before the satellite tables. So how are you to do that?

I would make these other tables one::many with the main table (even though you and I both know they will be one:ne). Makes it easier to synchronize things. And despite the apparent insanity of 1/many for what is 1/1 data, there is no requirement that you ever add a second supplemental data record for any of your main records. One::many just gives you the CHANCE, not the REQUIREMENT to have more than one record. You could also have ZERO matching records - and that is what you want this time.

Going this way, the prime key for the (now CHILD) satellite tables is just a LONG integer, not an autonumber. It is also now a Foreign Key and as such can safely be COPIED to the appropriate fields of the satellites. Once the last entry has been made in the main and the update occurs, you can just READ the number you want.

Let me add that sizing is always a valid consideration, but how big is big here? You might be saving only a little space here. Not that I'm saying it is wrong to do what you are doing, but there are limits. Heck, we do the same thing here. But we did it because we had literally hundreds of supplemental data elements per person. (Government sites are like that sometimes...)

The crux of your situation is that one table MUST be the main table and a record MUST exist in it before you could EVER update the others. Referential integrity guarantees that for you. I wonder if RI is the source of your problem in the one:ne case? (Hard to say.)

If you soften the stance just a little bit, you can have the parent entry defined before you go on to create the child portions. The one::many case allows that easily. The alternative would be to do away with referential integrity, and I strongly urge to not do that.

Just a thought, no guarantees, but it should be workable.

 

[jimster68]

Thanks for the feedback! I restrucutred the tables like you suggested and now I'm getting good results. I can now update the main table without the other data, return to the record later and add data for the satallite tables. When it updates it automatically adds the key value to the satallite tables and I now have my related data !

I have roughly 50 or so fields among these tables so I probably could have gotten away with one table but it was just a matter of preferrence in this case.

Thanks!

 

[The_Doc_Man]

I see my smilies confused the issue. Typical.

I'm glad things worked for you. While hindsight is 20-20, it is hard to realize that with 1/1 tables and referential integrity, it is hard for Access to know which table has to have the first record that defines the integrity. Doing it my way answers the question.

As to the wisdom of keeping 50 fields together or separate, that is your call not mine, and I've seen it done before as a performance issue. So I surely will not second-guess that decision. If you are happy then who am I to make snide remarks?

 

[rmaciel]

The Doc Man,

You helped another one today. Thanks!
Ricardo Maciel