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

John,

That's a messy bit alright. Quite a bit more work and probably not worth that much effort. Thanks for the thoughts.

Russ

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Russ-
>
> You could do that, too, but would require a bit more code. The messy bit is
> you'll also need to move any related child records. You could do that with
> a series of Append queries.
>
> 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: Wednesday, May 01, 2013 3:21 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] #deleted in a field value
>
> John,
>
> That's simple enough to mark one as inactive. I always figured (maybe
> incorrectly) that an archive record would be moved to a different table,
> maybe called archive.
>
> Russ
>
>
> --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@>
> wrote:
> >
> > Russ-
> >
> > The basic technique is to add a column called "Deleted" or "Obsolete"
> > to every table for which you want to do this. When a user requests to
> > delete a record, your code simply sets that field to True and saves
> > the record. For all your active forms, use a Record Source that
> > includes criteria WHERE Deleted = False so that you display only
> > active records. Oh, and when a user requests a deletion, you Requery
> > the form after setting the flag so the user no longer sees the record.
> >
> > 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: Wednesday, May 01, 2013 2:08 AM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: Re: [MS_AccessPros] #deleted in a field value
> >
> > Bill
> >
> > Actually a pretty good idea. I would really like to mark or move a
> > "customer" to an Archive file, but as of today am not quite sure how
> > to do it.
> >
> > Russ
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca"
> > <wrmosca@>
> > wrote:
> > >
> > > 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-429
> > > > > > > 1-
> > > > > > > B816-642
> > > > > > > 707308
> > > > > > > > > > > > 81E>
> > > > > > > > > > > >
> > > > > > > https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291
> > > > > > > -B
> > > > > > > 816-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
> > > > > >
> > > > >
> > > >
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>