Add records to two tables in 1-1

[HalcyonDaze]

I have a database where two tables are linked in a 1-1 relationship linked by the auto-number key in each. Table A is the main list, with Table B containing unique (and private) information related to each of the corresponding records from the Table A.

What I need to do is ensure that changes to Table A also occur to Table B, namely the addition or removal of records so that there are always the same number (with proper 1-1 relation maintained) as things are added or removed.

And I need this to happen automatically, without the need of a refresh action on the part of the user. I made an Append Query to create the initial Table B (Table A exists from a currently used DB) so that there were corresponding records, but to run that every time would require the user to click through several windows of caution, confirmation, and expected errors.

Any Ideas how to keep this all straight?

 

[Galaxiom]

Relating tables via autonumber key in both is wrong. There is no way to synchronise the autonumbering of two tables and they could get out of step at any moment.

Table A can have an autonumber but the key field in Table B should be an integer datatype. Table B should be shown in a subform with the link fields set to the respective key fields.

In the BackEnd database set the Referential Integrity from Table A to Table B with Cascaded Deletes enabled. Deleting a record in A will delete the corresponding record in B.

 

[gemma-the-husky]

as Galxiom says, table B should just have a number ID (rather than autonumber) which is then linked/set to the autonumber value of table A

 

[The_Doc_Man]

OK, let's get down to the main issue. Why do you use a 1/1 relationship at all?

I'll list the only reasons I know for this to be done meaningfully, and comment on each as regards to your problem.

1. Tables are split because of different security rights on one of the parts.

The table that has the more stringent security rights should be treated like a child table. Its PK should be LONG and should contain a copy of the PK of the corresponding record from the public portion. Use a JOIN query when updating the pair of tables. A 1/1 JOIN should be an updatable query.

2. Tables are split into more than two pieces-parts because the amount of data in a single JOINed record would overflow the 2048-byte record size limit.

One fragment must be identified as the main piece. Then you will need two (or more) queries to update the other parts. Which means that a careful app design will be crucial here. To turn off the warnings, look at DoCmd.SetWarnings

3. Tables exist on multiple servers and TWO database tables must be open at the same time because two files on two different hosts are involved.

This can be done as a JOIN also. See comments for #1 regarding layout and choice of PK, but be prepared for performance issues out the wazoo. This is also generally not recommended because of the potential for descynchronization of the two tables if there is a network glitch during an update.

4. Though the relationship is 1/1 in a 2-table setup, it is possible for NONE of the fields in one of the tables to be populated, perhaps because it is all OPTIONAL supplemental data. Further, when the supplemental data fields are populated, they tend to be rather long. This might also apply for a table that holds optional MEMO field types.

The table that is always populated holds the autonumber PK, the other table simply uses a LONG as its PK. For those program parts that need the supplemental data, involve an OUTER JOIN so the optional data comes back as nulls. But be prepared to handle a LOT of nulls. If the number of potentially long fields is not that large, consider collapsing that into a single table anyway.

5. None of the above.

Don't split the table. Merge it. There is no valid reason for the data to be split between two tables.

 

[Galaxiom]

OK, let's get down to the main issue. Why do you use a 1/1 relationship at all?

I'll list the only reasons I know for this to be done meaningfully, and comment on each as regards to your problem.

1. Tables are split because of different security rights on one of the parts.
[Four more reasons]

There is no valid reason for the data to be split between two tables.

Doc. You have contradicted yourself by giving five reasons then saying there was no valid reason.

I believe the OP stated a reason fitting your first exception in their opening post.

Table A is the main list, with Table B containing unique (and private) information

 

[gemma-the-husky]

i took it that doc man was saying, that if none of the above - only then is there no reason to split.

it seemed consistent to me

 

[Galaxiom]

Ah. I have misinterpreted the structure of the post.
The last line belonged to 5. None of the Above.

 

[HalcyonDaze]

Mostly its a case of Doc's #4 scenario.

Table A is part of an existing database in use that tracks control instrument locations, pricing, etc. being use in current project. I am trying to add the instrument Datasheets, which contain specifics of model and implementation for each instrument, to the database. This supplemental information is populated at a much later stage (and then will not be needed for all instruments) and will potentially contain quite a bit of information (Text not Memo size, but 90 fields).


Edit:
I tried changing the PK on the Datasheet table to a Long and set cascading delete. Still not getting what I need. My problem right now is that if something changes and a new instrument is added, I cannot input data into the datasheet table (via its split form ) because there is no matching record in the datasheet table for the new instrument added to the main index. I made a basic append query to work around it, but I dont want to have to manually run it and click through the various notifications that come with it every time I add an instrument.

If its a more stable structure I am will to merge the two tables in to one massive table, though Im not sure the cleanest way to accomplish that. I was trying to avoid the huge stack of null values, but I think they will be there to some degree regardless.

 

[The_Doc_Man]

Galaxiom, you are correct. "There is no reason..." was part of #5. Sometimes I put line breaks for readability but disrupt the flow anyway. My bad.


HalcyonDaze - "And I need this to happen automatically, without the need of a refresh action on the part of the user. I made an Append Query to create the initial Table B (Table A exists from a currently used DB) so that there were corresponding records, but to run that every time would require the user to click through several windows of caution, confirmation, and expected errors."

OK, let's address that. You said it was mostly like my case #4, lots of sparse data even to the point of not having some records in the B-side table.

If your issue is that you are going to delete entries from the "main table" and want to delete them from the subsidiary table, you need to do this in a form context so that you can put some VBA code behind the scenes. You can turn off warning messages and turn them on again using the DoCmd.SetWarnings {true/false}, so that won't bother your users much. From a form context where VBA can run easily and events can fire based on what a person is about to do, look at the idea of having the form include some code in a Before_Delete event. If you are about to delete a record in table A, use some generated code to test for and, where applicable, delete the selected record.

You can do that with either generated SQL based on building the string:

"DELETE * FROM TABLEB WHERE TABLEB.PK = " & CStr( [TABLEA]!{PK] )

then do a DoCmd.SQL on that string...

or you can do a recordset operation where you look up that record and .DELETE it through the recordset.

How you go about inserting records? Well, that depends on what you would insert in table B initially when creating a new record in Table A. So I can't really address that without a little more feedback.

 

[Galaxiom]

I tried changing the PK on the Datasheet table to a Long and set cascading delete. Still not getting what I need.

Cascading only works in one direction, from the first table to the second in the order you dragged the relationship line. You had better check this or there is a risk that you will delete a datasheet and the main record will unexpectedly delete too.

My problem right now is that if something changes and a new instrument is added, I cannot input data into the datasheet table (via its split form ) because there is no matching record in the datasheet table for the new instrument added to the main index.

Using a subform with the Master and Child link fields set for the key fields should automatically create the record in the subform recordsource when a new record is created in the datasheet table.

If its a more stable structure I am will to merge the two tables in to one massive table, though Im not sure the cleanest way to accomplish that. I was trying to avoid the huge stack of null values, but I think they will be there to some degree regardless.

With 90 fields I guess that only some will apply to any one device. If so an alternative structure to consider is to use multiple records which include fields for the specification type and specification value rather than having a field for each specification type.