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

Bill

Thanks for the clarification about the #deleted message. This database is not owned by me, I can only advise on good practice. There has been a culture of fixing things by changing the data in the tables rather than working out what functions are needed and building forms to support them. Maybe this problem has high-lighted an issue and things might change - hope so.

The database has now been recovered to a usable state and I will monitor the situation for a few days via a remote link. I think we can draw this to a close - thanks for being there and helping with this.

Best regards
Ray

--- 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]
>