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

John-
Exactly what I was getting at. It's a rare case when I want to allow deletion of a parent when child records exist. I'd rather force parent and child records to be marked as inactive.

Bill

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Russ-
>
> I think Bill is suggesting that code in forms should to the checking and
> deleting after prompting the user. Consider the relationship Customer ->
> Order -> OrderDetails. If a user deletes a customer in a database with
> Cascade Delete turned on, he or she may not realize that perhaps thousands
> of rows will also disappear. I personally use Cascade Delete often, but
> then I also write code to check and inform the user in the Delete event of
> every form. Something like: "This action will permanently delete customer
> XXXX along with 540 Orders and 1,659 Order Detail records. Are you SURE you
> want to proceed?"
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of ghsclass65
> Sent: Monday, April 29, 2013 4:43 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] #deleted in a field value
>
> 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-642
> > 707308
> > > > > > > 81E>
> > > > > > >
> > https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-6427
> > 073088
> > > > > > > 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]
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>