ACCESS FEED: Re: [MS_AccessPros] #deleted in a field value

Russ

That is a misconception. If you have referential integrity turned on, Access will prevent you from deleting parent records if a child exists. Turning on the cascading delete will wipe out everything related to the parent when the parent is deleted. Only a DBA should be allowed to do that...unless you are making hourly db backups so you can restore what some bozo user has erased. And using an Access back end, hourly backups are probably not in place.

Conversely, if you do not have referential integrity turned on then you can end up with orphans. I always turn RI on and never allow deletion of any record. That gives me a better audit trail. In the work I do (finance, healthcare, HR), historical records are as important as current ones.

Bill

--- In MS_Access_Professionals@yahoogroups.com, "ghsclass65" <dyspoz2@...> wrote:
>
> Bill,
>
> I do not understand your statement that relationships should not have cascading updates/deletes turned on. I build all my db that way and if I do not turn that feature on, then when someone deletes a record, it's related records in the connected tables are not deleted resulting in records that are in limbo with no corresponding records in other tables.
>
> Russ
>
>
>
> --- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@> wrote:
> >
> > Ray
> >
> >
> >
> > The user who did not delete a record would be the one who sees the #deleted. It
> > wouldn't cause chaos. User1 would just have to refresh the form.
> >
> >
> >
> > Tampering with data directly in a table can be dangerous. Users should have no
> > way of opening tables. And relationships should not have cascading
> > updates/deletes turned on. Cascading stuff should only be done by the DB
> > administrator should it ever be necessary.
> >
> >
> >
> > When I mentioned the problem with network connectivity I was saying the record
> > itself could be corrected. By backing out of the form, compacting the db and
> > then opening the form again to re-enter the data usually cleans up the
> > corruption.
> >
> >
> >
> > Regards,
> >
> > Bill
> >
> >
> >
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Ray
> > Sent: Saturday, April 27, 2013 9:54 AM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: Re: [MS_AccessPros] #deleted in a field value
> >
> >
> >
> >
> >
> > Hi Bill
> >
> > Thanks for that. The part of the database that is having these errors has the
> > structure tblContacts has many tblContactNotes. If User1 was using the form
> > correctly (form for Contact, subform for Notes) whilst User2 went directly to
> > the tblContactNotes and deleted a record - would that cause chaos? Perhaps even
> > #deleted?
> >
> > If I can come up with a plausible reason as to why some records are being
> > corrupted (data lost and replaced with #deleted) I'm sure that I can change this
> > culture of tampering with the tables and lock the database down.
> >
> > Finally, you said that network connectivity could be easily remedied - is this
> > an Access thing or a network server thing? Where should I start to look?
> >
> > Thanks for your help with this.
> > Ray
> >
> > --- In MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com> , "Bill Mosca" <wrmosca@>
> > wrote:
> > >
> > > Ray
> > >
> > > The first person to open the database is not creating a lock file. It is a
> > locking file. This file is used by the JET/ACE engine to lock records that are
> > being edited, nothing more as long as the form in use has it's record locking
> > set to Edited Record. That keeps more than one person at a time from changing a
> > record.
> > >
> > > This is not the same as opening the database exclusively. If the first person
> > opens it exclusively or opens an object in design mode everyone else is locked
> > out. Exclusive mode actually locks out all others from the entire db file.
> > Splitting the database and making sure each user is using his own copy of the
> > front end will still protect the records, but will not lock others out of the
> > back end. That is because the back end is never opened directly by a user.
> > >
> > > Your multi-user db would benefit from splitting. It's very rare that a back
> > end table gets corrupt. Sometimes a record will corrupt due to loss of network
> > connectivity, but that sort of thing is easily remedied.
> > >
> > > Bill
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com> , "Ray" <rayfrew@> wrote:
> > > >
> > > > Hi Bill
> > > >
> > > > The sad situation is that the database is not split, it's just one file on a
> > server. Two people use it during the day by using Access to open the mdb file.
> > The first person to open the database will create the lock file - I'm not sure
> > what happens when the next person opens the database (whilst the first person is
> > still using it). Another aspect is that one person in India connects to the
> > server overnight and does some data input.
> > > >
> > > > I've suggested that the database be split but so far have been met with
> > "Why? Everything's working and not causing a problem" - well now, perhaps it is.
> > Especially as one of the errors says that an update cannot be done because
> > someone else is updating - yet there is only one person using the database.
> > > >
> > > > If I split the database for them (data on server, forms etc on clients) will
> > the first person to open the database still create a lock file and be the
> > "owner" or does it work differently? I know that splitting a database is good
> > practice but can you give me any pointers to the types of problems splitting
> > solves?
> > > >
> > > > Many thanks
> > > > Ray
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com> , "Bill Mosca" <wrmosca@>
> > wrote:
> > > > >
> > > > > Ray
> > > > > >I don't think that an index was removed in this case because the
> > > > > >users wouldn't
> > > > > >know how to. However, it is more likely that there was a network
> > > > > >problem -
> > > > > >apparently that happens occasionally.
> > > > >
> > > > > I agree. Do you have the database in a split configuration with the tables
> > on a server and the forms, reports, queries etc on the users' local drive? That
> > would immensely reduce future corruption...at least as far as the forms and
> > queries are concerned .
> > > > >
> > > > > Bill
> > > > >
> > > > >
> > > > > --- In MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com> , "Ray" <rayfrew@> wrote:
> > > > > >
> > > > > > Hi Roberto
> > > > > >
> > > > > > I don't think that an index was removed in this case because the users
> > wouldn't know how to. However, it is more likely that there was a network
> > problem - apparently that happens occasionally.
> > > > > >
> > > > > > Things have moved forward now, because the database has been recovered
> > from a backup, data is being rekeyed and another backup is about to be taken.
> > Probably best to take more than one eh?
> > > > > >
> > > > > > Thanks for your help.
> > > > > > Ray
> > > > > >
> > > > > > --- In MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com> , "RobertoARV" <robertoarv@>
> > wrote:
> > > > > > >
> > > > > > > Sometimes when it happens , an index is removed, then you need to
> > delete
> > > > > > > records showing deleted# mark , run a compact database , recreate the
> > index
> > > > > > > and then create a blank database and import all objects from fixed
> > > > > > > database.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > From: MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > [mailto:MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of RobertoARV
> > > > > > > Sent: Thursday, April 25, 2013 11:28 AM
> > > > > > > To: MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > Subject: RE: [MS_AccessPros] #deleted in a field value
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > I have seen this for years with this database , often happens when you
> > > > > > > migrate same database to another upper version, you need to install
> > all the
> > > > > > > Microsoft jet service patches. it also occurs when you edit same
> > record in 2
> > > > > > > computers , it shows deleted in other computer but it get fix when you
> > run a
> > > > > > > requery in form or simply close form and open it again. it happens too
> > when
> > > > > > > a network disconnect occurs when you are editing a record.
> > > > > > >
> > > > > > > From: MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > [mailto:MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Bill
> > Mosca
> > > > > > > Sent: Thursday, April 25, 2013 9:39 AM
> > > > > > > To: MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > Subject: RE: [MS_AccessPros] #deleted in a field value
> > > > > > >
> > > > > > > Ray
> > > > > > >
> > > > > > > The most likely cause is a corrupted index. It might have been saved
> > if you
> > > > > > > imported it into a blank database (which sometimes fixes bad indices).
> > I'm
> > > > > > > glad
> > > > > > > there was a backup, though. That was good thinking on your friend's
> > side.
> > > > > > >
> > > > > > > Regards,
> > > > > > > Bill Mosca,
> > > > > > > Founder, MS_Access_Professionals
> > > > > > > That'll do IT <http://thatlldoit.com/> http://thatlldoit.com
> > > > > > > MS Access MVP
> > > > > > >
> > > > > > >
> > <https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-642707308
> > > > > > > 81E>
> > > > > > >
> > https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-6427073088
> > > > > > > 1E
> > > > > > >
> > > > > > > My Nothing-to-do-with Access blog
> > > > > > >
> > > > > > > <http://wrmosca.wordpress.com> http://wrmosca.wordpress.com
> > > > > > >
> > > > > > > From: MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > [mailto:MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Ray
> > > > > > > Sent: Thursday, April 25, 2013 5:39 AM
> > > > > > > To: MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > Subject: [MS_AccessPros] #deleted in a field value
> > > > > > >
> > > > > > > Can you shed some light on this mystery? A friend has an Access 2003
> > db and
> > > > > > > one
> > > > > > > table has some rows with #deleted in each field rather than the data
> > that
> > > > > > > used
> > > > > > > to be there. I suspect that they have used the "delete record" button
> > from
> > > > > > > the
> > > > > > > toolbar to delete a few rows (I know, not a good idea) and that
> > something
> > > > > > > went
> > > > > > > wrong. When a query runs on this table it returns an error saying that
> > a
> > > > > > > record
> > > > > > > has been deleted and then stops dead.
> > > > > > >
> > > > > > > We recovered the database from a backup and life has moved on.
> > However, I've
> > > > > > > never seen this before, have you?
> > > > > > >
> > > > > > > Many thanks
> > > > > > > Ray
> > > > > > >
> > > > > > > [Non-text portions of this message have been removed]
> > > > > > >
> > > > > > > [Non-text portions of this message have been removed]
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > [Non-text portions of this message have been removed]
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>