Database integrity
in [General]
|
Prev: mail merge currency
Next: Email multiple reports
From: Tara on 24 Feb 2010 10:51 I have someone who says they entered data into a database, but the data isn't there. Others have entered data into the same database and that data is there. All users have used the same front-end. Save is automatic when the form is closed. There are no signs of corruption. Is this possible?
From: Dirk Goldgar on 24 Feb 2010 12:01 "Tara" <Tara(a)discussions.microsoft.com> wrote in message news:83693CD9-76E5-4225-9864-CEB70132EA59(a)microsoft.com... >I have someone who says they entered data into a database, but the data >isn't > there. Others have entered data into the same database and that data is > there. All users have used the same front-end. Save is automatic when > the > form is closed. There are no signs of corruption. > > Is this possible? No -- or rather, in the absence of corruption or user action, data doesn't just disappear. There are a few possibilities to consider: 1. The user is mistaken in thinking the data was entered. 2. The data was manually deleted, either by this user or by someone else. 3. The data was deleted programmatically or by a delete query that was too broad in scope. 4. The data was deleted by the action of cascading deletes -- that is, a "parent" record in another table was deleted, and relationships have been defined in such a way that deleting the parent record causes all related records to be deleted. 5. The data is still there, but doesn't appear on the form, report, or query you're looking at because either (a) there are filtering criteria in effect that exclude it, or (b) the form/report's recordsource is a query (or you're looking directly at a query) that joins multiple tables in a way that prevents the records from the subject table from appearing when they don't have matching records in some other table -- and the records you're looking for don't. The first thing I would investigate is #5, above. Make sure you look directly in the table where these records are supposed to have been stored, and see if they are there. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup)
From: Tara on 24 Feb 2010 12:56 Thanks Dirk. I have looked in the tables directly...no data. There is only one delete query - it's very specific and limited in scope and doesn't include the table in question, so I know that's not the issue. I do have cascading deletes set, but there is no way to delete the parent record via forms. Someone would have had to enter the table directly, which is highly unlikely. Which basically leaves me with #1. Thank you. I was a tad upset that the integrity of my design was being questioned. I knew I personally had never seen data just randomly disappear, but I needed to confirm from someone more knowledgeable than myself that it simply doesn't happen that way. "Dirk Goldgar" wrote: > "Tara" <Tara(a)discussions.microsoft.com> wrote in message > news:83693CD9-76E5-4225-9864-CEB70132EA59(a)microsoft.com... > >I have someone who says they entered data into a database, but the data > >isn't > > there. Others have entered data into the same database and that data is > > there. All users have used the same front-end. Save is automatic when > > the > > form is closed. There are no signs of corruption. > > > > Is this possible? > > No -- or rather, in the absence of corruption or user action, data doesn't > just disappear. There are a few possibilities to consider: > > 1. The user is mistaken in thinking the data was entered. > > 2. The data was manually deleted, either by this user or by someone else. > > 3. The data was deleted programmatically or by a delete query that was too > broad in scope. > > 4. The data was deleted by the action of cascading deletes -- that is, a > "parent" record in another table was deleted, and relationships have been > defined in such a way that deleting the parent record causes all related > records to be deleted. > > 5. The data is still there, but doesn't appear on the form, report, or query > you're looking at because either (a) there are filtering criteria in effect > that exclude it, or (b) the form/report's recordsource is a query (or you're > looking directly at a query) that joins multiple tables in a way that > prevents the records from the subject table from appearing when they don't > have matching records in some other table -- and the records you're looking > for don't. > > The first thing I would investigate is #5, above. Make sure you look > directly in the table where these records are supposed to have been stored, > and see if they are there. > > -- > Dirk Goldgar, MS Access MVP > Access tips: www.datagnostics.com/tips.html > > (please reply to the newsgroup) >
From: Dirk Goldgar on 24 Feb 2010 13:10 "Tara" <Tara(a)discussions.microsoft.com> wrote in message news:DC8BD76B-AFFC-49F9-B8A7-82AD2378522F(a)microsoft.com... > Thanks Dirk. > > I have looked in the tables directly...no data. There is only one delete > query - it's very specific and limited in scope and doesn't include the > table > in question, so I know that's not the issue. I do have cascading deletes > set, but there is no way to delete the parent record via forms. Someone > would have had to enter the table directly, which is highly unlikely. > Which > basically leaves me with #1. > > Thank you. I was a tad upset that the integrity of my design was being > questioned. I knew I personally had never seen data just randomly > disappear, > but I needed to confirm from someone more knowledgeable than myself that > it > simply doesn't happen that way. It does not. One additional possibility occurs to me. If the data entered was invalid in such a way that it could not be saved -- missing required fields, failing a validation rule, no matching record in a related table, etc. -- then *if* you close the form in code by calling DoCmd.Close, then the invalid record will be silently discarded and the form will close anyway. In this case, the user would not realize that the record had not been saved. For this reason, it is important that if you have a "Close" button on your form, you make sure that the code for that button explicitly saves the record before closing the form, like this: If Me.Dirty Then Me.Dirty = False DoCmd.Close acForm, Me.Name, acSaveNo -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup)
From: Tara on 24 Feb 2010 14:36
I never thought about invalid data causing an issue. For whatever reason - maybe just the way I learned to do it - I always explicitly save. Which is why I probably never thought about it being an issue...I've never run into it. It obviously makes sense though! Learn something new everyday! Thanks again. "Dirk Goldgar" wrote: > "Tara" <Tara(a)discussions.microsoft.com> wrote in message > news:DC8BD76B-AFFC-49F9-B8A7-82AD2378522F(a)microsoft.com... > > Thanks Dirk. > > > > I have looked in the tables directly...no data. There is only one delete > > query - it's very specific and limited in scope and doesn't include the > > table > > in question, so I know that's not the issue. I do have cascading deletes > > set, but there is no way to delete the parent record via forms. Someone > > would have had to enter the table directly, which is highly unlikely. > > Which > > basically leaves me with #1. > > > > Thank you. I was a tad upset that the integrity of my design was being > > questioned. I knew I personally had never seen data just randomly > > disappear, > > but I needed to confirm from someone more knowledgeable than myself that > > it > > simply doesn't happen that way. > > > It does not. > > One additional possibility occurs to me. If the data entered was invalid in > such a way that it could not be saved -- missing required fields, failing a > validation rule, no matching record in a related table, etc. -- then *if* > you close the form in code by calling DoCmd.Close, then the invalid record > will be silently discarded and the form will close anyway. In this case, > the user would not realize that the record had not been saved. > > For this reason, it is important that if you have a "Close" button on your > form, you make sure that the code for that button explicitly saves the > record before closing the form, like this: > > If Me.Dirty Then Me.Dirty = False > DoCmd.Close acForm, Me.Name, acSaveNo > > > -- > Dirk Goldgar, MS Access MVP > Access tips: www.datagnostics.com/tips.html > > (please reply to the newsgroup) > |