Record will not Delete using Subtable

  • Thread starter Thread starter Steve Stad
  • Start date Start date
S

Steve Stad

I have a datasheet (e.g., table1) with a sub table inserted. I could delete
records from table1 OK for awhile. But now I can not delete records from
table1 with the subtable inserted. It works OK if I remove the sub table.
i.e., I can delete records in table1 fine without the sub table. I have
tried to compact and repair the db but so far to no avail -- any help you
can provide is appreiciated.
Steve
 
I have a datasheet (e.g., table1) with a sub table inserted. I could delete
records from table1 OK for awhile. But now I can not delete records from
table1 with the subtable inserted. It works OK if I remove the sub table.
i.e., I can delete records in table1 fine without the sub table. I have
tried to compact and repair the db but so far to no avail -- any help you
can provide is appreiciated.
Steve
Click to expand...

My guess is that Access is doing precisely what you instructed it to do.

Creating a Subtable is one (obscure and not the best) way to define a
relationship between tables. A Relationship, with referential integrity
enforced, will (quite properly) prevent you from deleting a parent record if
that would leave any child records "orphaned".
 
John,

What is the best way to insert a subtable (i.e., tbl-2) which will allow me
to delete a record in the Master table (i.e., tbl-1). i.e., can I not
enforce referential integrity and link the tables using a parent or child
field?
 
John,

What is the best way to insert a subtable (i.e., tbl-2) which will allow me
to delete a record in the Master table (i.e., tbl-1). i.e., can I not
enforce referential integrity and link the tables using a parent or child
field?
Click to expand...

I don't understand the question. It sounds like you want to enforce
referential integrity - i.e. protect parent records from deletion if deleting
would break RI - but you also want to be able to delete any record.

Please explain what these two tables are, how they are related, and what you
want to delete.

Just FWIW, I realize that A2007 really pushes table datasheet view as a
routine user interface but I dislike it. It's VERY limited and limiting. I'd
suggest going to the additional (slight) effort of creating a Form with one or
more subforms to model your one to many relationships.
 
John,

I am not trying to protect records from being deleted. My process involves
updating records in a Temporary table (Temp Table). When a record is
complete I move (copy/paste) the record from Temp tbl to a Permanent table
(e.g., Perm Table) and delete it from the Temp table. The purpose of the sub
table is to store extra and historical data for corresponding records in the
temp table. So I can refer to the sub table for additional data (or history)
for the related record in the temp or Perm table. So I DO WANT to be able to
delete a record from the temp table and keep a corresponding record in the
sub table. The records are (or can be) related or linked by one (or more)
common id codes in each record. Let me know if this helps explain or if I
can clarify further.
 
John,

I am not trying to protect records from being deleted. My process involves
updating records in a Temporary table (Temp Table). When a record is
complete I move (copy/paste) the record from Temp tbl to a Permanent table
(e.g., Perm Table) and delete it from the Temp table. The purpose of the sub
table is to store extra and historical data for corresponding records in the
temp table. So I can refer to the sub table for additional data (or history)
for the related record in the temp or Perm table. So I DO WANT to be able to
delete a record from the temp table and keep a corresponding record in the
sub table. The records are (or can be) related or linked by one (or more)
common id codes in each record. Let me know if this helps explain or if I
can clarify further.
Click to expand...

If you want to be able to delete a parent record (in a main table or temp
table, Access doesn't make a distinction) and leave records in a dependent
table, then you simply cannot use referential integrity. That's the whole
PURPOSE of referential integrity - to prevent the formation of orphans.

If you want to create "orphans" (as far as the temp table is concerned),
then... you simply cannot specify referential integrity to the temp table.

Note that copy and paste is probably the worst possible way to migrate data!
Any chance you could use an Append query instead?
 
John W. Vinson said:
If you want to be able to delete a parent record (in a main table or temp
table, Access doesn't make a distinction) and leave records in a dependent
table, then you simply cannot use referential integrity. That's the whole
PURPOSE of referential integrity - to prevent the formation of orphans.

If you want to create "orphans" (as far as the temp table is concerned),
then... you simply cannot specify referential integrity to the temp table.

Note that copy and paste is probably the worst possible way to migrate data!
Any chance you could use an Append query instead?
Click to expand...
John,
Correct - I want to be able to delete a parent record (in a main table or temp
table and leave records in a dependent (sub) table. Can you recommend the
best way to insert a sub table to the temp table which will allow deletions in
the temp table, i.e., w/o Referential integrity.
I am not concerned about creating orphan records. Because they are not really
orphans - just records w/o a corresponding record in the temp table.
 
Correct - I want to be able to delete a parent record (in a main table or temp
table and leave records in a dependent (sub) table. Can you recommend the
best way to insert a sub table to the temp table which will allow deletions in
the temp table, i.e., w/o Referential integrity.
Click to expand...

AFIAK you cannot. What you can do is use a Form (for the temp table) with a
Subform (for the subtable), using the linking field as the master/child field,
but not setting RI on the relationship.

If you insist on using table datasheet view (which, IMO, is not really
designed for data interaction), you may be out of luck. Table datasheets are
very limited, and this is one of their limitations.
 
Back
Top