what happens when i delete a record from access?

S

steven.ipek

I need to delete a record from a form I created, it is linked to a subform.
When I delete it I need know if it removes the number of that record, or does
it renumber the rest to take its place? And I also need to know if I have to
delete its equivalent record in the subform.
 
S

Steve Schapel

Steven,

When you ask about the number being removed, I guess you have an ID
field of some sort in the table that the form is based on, and this is
an Autonumber data type. The answer is that the number is removed if
you delete the record, and other records are not renumbered. Related
records as shown in the subform should probably be deleted, although
there are some circumstances where this is not what you want, for
example if you need to retain financial transactions even if the
customer is deleted. To delete the related subform records, you can use
code on the same event that you use to delete the main form record, in
order to run a delete query on the related records. However, if you
establish a Relationship between the tables, with Referential Integrity
enforced, and Cascade Deletes enabled, the related subform records will
automatically be deleted if you delete a main form record.
 
S

Sheila D

Hi Steven

When you delete a record no re-numbering will take place (I assume you have
an Autonumber field) that number is just 'lost'. Whether the record in the
related table is deleted as well depends on the rules set up in the
relationship window.

Go to relationships and see whether referentail integrity is checked. If it
is and Cascade delete related records is also checked then the related record
will be deleted automatically.

If referentail integrity is checked.and Cascade delete related records is
not checked then you will not be allowed to delete the main record. I don;t
recommend you change these options without a full understanding of the
implications.

Hope this helps

Sheila
 
S

Sprinks

Hi, Steven.

If the table behind your form has an AutoNumber primary key, when you delete
the record, nothing happens to the primary key of other records, but the
deleted number may be reused. The AutoNumber feature exists only to provide
a unique identifier for each record, not to maintain any order. If your
application needs to do this, you will have to provide code to do it.

If there are child records associated with the main form record, then the
behavior depends on whether or not you've defined a relationship through the
Relationships tool (Tools, Relationships), and selected Referential Integrity
enforcement with Cascade Delete.

With Cascade Delete set, on attempting to delete the main form record, you
will be prompted that child records exist, and asked if you wish to delete
tham also. You can not delete the main form record without deleting the
child records as well.

If Cascade Delete is not set, nothing happens to the child records, but they
become "orphans" since they have no "parent".

Hope that helps.
Sprinks
 
S

Steve Schapel

Sprinks said:
... If Cascade Delete is not set, nothing happens to the child records, but they
become "orphans" since they have no "parent".
Click to expand...

Sorry, but just to avoid confusion, this is not correct. Access will
not allow the "parent" to be deleted, so the "children" will not be
orphaned.
 
S

Sharkbyte

Steven:

This is entirely dependent on the structure of the database. There may or
may not be cascade delete, so that when you delete a record all child records
are also removed.

By default, any autonumber type, id fields, are not renumbered. You are
left with a missing number in your sequence. So if you are working with some
sort of Billing system, with Invoice #s, you will not want to use any type of
autonumbering, and will need to add code to renumber whatever you need to,
if/when a record is deleted.

Sharkbyte
 
S

Sprinks

Steve,

I'm not sure if you misread my post or if this feature has changed in later
releases, but in my version of Access 2002 (10.6501.6714 SP3), if no
relationship is defined between a parent and child, Access permits the
deletion of a parent record while leaving behind the orphans.

Sprinks
 
B

Brendan Reynolds

I don't get it - if no relationship is defined, then in what sense do parent
and child records exist?
 
S

Steve Schapel

Maybe I misread your post, Sprinks. I interpreted you to be referring
to a relationship with Referential Integrity enforced, and then
discussing the difference between whether Cascade Deletes is enabled or not.
 
S

Sprinks

Only in the sense of the Link Master Fields and Link Child Fields properties
on the form. Although I'm sure most professional developers define all of
the pertinent relationships between tables, I've seen a lot of apps that
don't.

Sprinks
 
B

Brendan Reynolds

OK, but that would be a 'relationship' that would not be visible to the
database engine. It is the database engine that manages referential
integrity, and in the eyes of the database engine, no relationship would
exist in that situation.

Other than that though, yes, I see what you mean now.

--
Brendan Reynolds (MVP)


Sprinks said:
Only in the sense of the Link Master Fields and Link Child Fields
properties
on the form. Although I'm sure most professional developers define all of
the pertinent relationships between tables, I've seen a lot of apps that
don't.

Sprinks
Click to expand...
 
N

Nicole

Sprinks:

Sprinks said:
Steve,

I'm not sure if you misread my post or if this feature has changed in later
releases, but in my version of Access 2002 (10.6501.6714 SP3), if no
relationship is defined between a parent and child, Access permits the
deletion of a parent record while leaving behind the orphans.
Click to expand...

This has happened to me, and now I have 8 orphans. How can I get them
adopted (restore these deleted records) to new parents (main form records) 1
through 8?

Nicole
 
L

Larry Daugherty

Find unmatched? You want to find all of the records in the child
table whose ForeignKey value is not found in the parent table's
PrimaryKey column.

HTH
 
Top