Selasa, 30 April 2013

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

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (22)
Recent Activity:
MARKETPLACE


.

__,_._,___

[belajar-access] Access DVD Collections

 

Assalamu’alaikum Wr Wb,

Dear Sahabat Access,

Barangkali diantara sahabat ada yang memerlukan kumpulan tutorial, tools, artikel, template, kumpulan problem solving, dan hal-hal lainnya seputar Microsoft Access, bisa pesan ke saya “Access DVD Collections” yang berisi kumpulan dan referensi lengkap untuk belajar Microsoft Access dan Pemrogramannya.

Isi DVD:

·         AccessWeb Indonesia PLUS (edisi revisi), yang memuat pelajaran Access untuk tingkat dasar, pemrograman Access, teori Microsoft SQL Server, OPI Software berikut dengan source codenya, tips-tips tentang Access (termasuk tips spesial mengenai tata cara mengemas program dan system security-nya), FAQ mengenai Access, contoh-contoh file Access berasal dari para programmer Access dan juga dari saya sendiri, berbagai Access tools dan add-ins yang menarik dan bermanfaat, beberapa Access template dengan berbagai fungsi, object dan file-file pendukung, serta informasi-informasi menarik lainnya mengenai Access. Untuk melihat screenshot, silahkan kunjungi: http://trendmuslim.com/awip/

·         Access Ebook, berisi kumpulan puluhan ebook Access dari mulai tingkat dasar hingga advance, termasuk pemrograman VBA.

·         Access Excel Collaboration, berisi tutorial dan penjelasan mengenai penggunaan Access yang dikolaborasikan dengan Excel, diantaranya:

Bab I : Export Data Access ke Excel

•     Export Data Access ke Excel Menggunakan Metode Copy Paste

•     Export Data Access ke Excel Menggunakan Metode Click and Drag

•     Export Data Access ke Excel Menggunakan Fasilitas Export Wizard di Access

•     Export Data Access ke Excel Menggunakan VBA

Bab II : Import Data Access ke Excel

•     Import Data Access ke Excel Menggunakan Fasilitas Import Wizard di Excel

•     Import Data Access ke Excel Menggunakan Macro

•     Memanfaatkan Microsoft Query dalam Import Data Access ke Excel

Bab III : Menambahkan Sejumlah Record Tertentu

•     Menambahkan Sejumlah Record Tertentu dari Access ke Suatu Table di Excel

•     Menambahkan Sejumlah Record Tertentu dari Excel ke Suatu Table di Access

Bab IV : Remote Access di Excel dan Sebaliknya

•     Menjalankan Macro Excel di Access

•     Menjalankan Action Query Access di Excel

•     Menjalankan Parameter Query Access di Excel

•     Menjalankan VBA Access di Excel

•     Membuka Object Access (Table, Query, Form dan Report) Menggunakan Macro Excel

•     Export Object Access (Table, Query, Form dan Report) Menggunakan Macro Excel

•     Compact and Repair Access Database Menggunakan Macro Excel

·         Access Menu Permission, yang merupakan file template bagaimana membuat sendiri menu dan permission didalam Access. Sebagaimana kita ketahui, pada Access 2007 dan berikutnya, tidak disertakan Switchboard dan Menu Permission. Maka dengan menggunakan file template ini, kita bisa membuat sendiri Menu Utama berikut dengan permissionnya.

·         Access Online, berisi informasi dan tutorial bagaimana membuat Access untuk aplikasi online, terutama sekali dalam hal penggunaannya dengan Web Remote Desktop Connection.

·         Access Video Tutorial, berisi file video tutorial tentang Access, lengkap dengan file untuk latihannya.

Harga Access DVD Collections ini Rp.150.000,- (belum termasuk ongkos kirim). Untuk pemesanan Access DVD Collections, sahabat bisa pesan via email ke email saya: sofyanefendi@gmail.com , dengan menyertakan nama lengkap, alamat lengkap dan No HP yang bisa dihubungi.

 

Bagi sahabat yang hendak kursus atau problem solving Access, bisa mengunjungi:

http://imopi.wordpress.com/access-course-by-request/

Terima kasih.

Wassalamu'alaikum Wr Wb,

Sofyan Efendi

NB: Mohon untuk tidak reply email ini ke milis, silahkan kirim langsung ke email saya.

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)
Recent Activity:
SPAM IS PROHIBITED
MARKETPLACE


.

__,_._,___

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

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (21)
Recent Activity:
MARKETPLACE


.

__,_._,___