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

Russ

You could also write a routine that would mark the customer as inactive and set up your forms and queries to ignore all inactive customers. That's how I do it. My users never delete parent records.

Bill

--- In MS_Access_Professionals@yahoogroups.com, "ghsclass65" <dyspoz2@...> wrote:
>
> John and Bill,
>
> The databases I've built do not have thousands of records for each "customer". In addition, when a delete function occurs a warning is always given in the form. I simply cannot be there to babysit the upkeep of a db and every deletion, but build in enough precautions to avoid mistakes that might be made. If cascading updates and deletions are not allowed, I would have to look at these things weekly or possibly daily, and I just want to build one that can be manageable by the user and not require my frequent attention.
>
> Russ
>
>
>
> --- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@> wrote:
> >
> > 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
> > >
> >
>