Database integrity

  • Thread starter Thread starter Tara
  • Start date Start date
T

Tara

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?
 
Tara said:
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?
Click to expand...

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.
 
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.
 
Tara said:
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.
Click to expand...


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
 
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.
 
One other remote possibility if you are using a linked backend and there are
copies of the backend out there for testing, development, or no known reason.
The user could be linked to a different backend.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Access / Jet randomly loses data.

It's just not a reliable system, not for a single record and a single
user.

It's time to upsize to SQL Server if you don't trust jet databases

-Aaron
MCITP: DBA
 
You know, I had disappearing data once...

I added 'workgroup security' and removed the delete
permission from the data. (I gave the user a short-cut
for log-in).

Within minutes, while I was still sitting there, a user reported
an error, caused by the missing delete permission, and
conclusively indicating where the missing data was going.

In that case, we did not know where the data was going.

I thought our instructions and our user interface were clear,
but I was wrong. The client thought workgroup security
would not help, but they were wrong. The user thought he
was following process, but he was wrong.

Human error is the cause of almost all data corruption.

(david)
 
a a r o n . k e m p f @ g m a i l . c o m said:
Access / Jet randomly loses data.
It's just not a reliable system, not for a single
record and a single user.
Click to expand...

Mr. Kempf's statements are both erroneous. Access / Jet does not "randomly
lose data", and it is quite reliable for everything from single user
databases to properly-implemented multiuser and client-server databases.

Larry Linson
Microsoft Office Access MVP
 
BS - jet doesn't even support security any longer.

Jet isn't reliable or a reasonable option for even single-user
applications
Anything else you claim is just an excuse because you've got a midget-
sized db

-Aaron
 
Jet -cannot- physically do anything resembling client-server kid

requiring developers to Sql Passthrough re-develop wrappers for every
sproc?

what a waste of time!!
 
Tara said:
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?
Click to expand...
 
Back
Top