Table rows disappearing from database

[mscertified]

I'm supporting a database that at some point appears to have lost over 200
rows from the main table. There is NO code in the database that deletes
records. There have been at least 2 instances of a corrupt rows appearing in
the main table and they may have existed unnoticed for some time. Could this
have caused the loss of rows? The table has a primary key consisting of the 4
digit year followed by a dash followed by a 4 digit sequence number. The
sequence portion is assigned by doing a DMAX and adding one (I did not design
this!). There are NO memo columns in the main table. The db is pretty simple
with the main table, one related table and a few lookup tables. No table
relationships have been set up. The database exposes the development window
because some people need to be able to create their own queries.

 

[Roger Carlson]

One obvious source is user-error, deliberate or inadvertant. No one will
own up to it, but you should consider it as a real possibility. I had one
database where a user would create queries, then delete rows from the query
that she didn't want there. She didn't realize that it would delete them
from the base table as well.

I suggest adding some AuditTrail functions to make certain.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "AuditTrail.mdb" which illustrates how to do this. You can
find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=399. A more
advanced version is here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=398

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[John W. Vinson]

I'm supporting a database that at some point appears to have lost over 200
rows from the main table. There is NO code in the database that deletes
records. There have been at least 2 instances of a corrupt rows appearing in
the main table and they may have existed unnoticed for some time. Could this
have caused the loss of rows? The table has a primary key consisting of the 4
digit year followed by a dash followed by a 4 digit sequence number. The
sequence portion is assigned by doing a DMAX and adding one (I did not design
this!). There are NO memo columns in the main table. The db is pretty simple
with the main table, one related table and a few lookup tables. No table
relationships have been set up. The database exposes the development window
because some people need to be able to create their own queries.

User error is indeed the most likely cause, as Roger suggests; but index file
corruption is another possibility. I've seen occasional strange problems with
fields containing hyphens (but not this one).

Try removing the Primary Key and all other indexes from this table (you'll
need to remove relationships); compact the database; and redefine the PK, the
indexes, and the relationships. Failing that... how's your backup situation?

 

[Jeff Boyce]

Which version of Access? How recently?

Something similar (spontaneous deletion) has been recently showing up in a
database I support. As far as I can tell, there is no code that deletes
rows in the application. And the users are locked into a user interface
that gives them no way to create/use queries...

The data itself is being stored in a SQL-Server back-end. Where's your
application storing the data?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

Which version of Access? How recently?

Something similar (spontaneous deletion) has been recently showing up in a
database I support. As far as I can tell, there is no code that deletes
rows in the application. And the users are locked into a user interface
that gives them no way to create/use queries...

The data itself is being stored in a SQL-Server back-end. Where's your
application storing the data?

Jeff, I wonder if deleting and reestablishing the connection would help. Do
the records disappear in the SQL table itself (as viewed from Enterprise
Manager)?

That's rather scary!

 

[Jeff Boyce]

Scary, si!

.... but wait, there's more! The way I was able to determine that rows had
gone missing was by finding 'orphans' in related "many" tables. In
SQL-Server. With Referential Integrity turned ON! (supposedly, with RI on,
it isn't possible to get a record out of a parent table until the related
child(ren) records are deleted ... and the SQL-Server DBAs concurred).

I've given the DBAs the lists of apparently missing parent IDs for them to
be doing their own research (hopefully via SSMS/Enterprise Mgr).

I used the "unmatched" query wizard to find child records with FKs that
pointed back at ... nothing. This happened in a half dozen tables, all but
one of which had RI turned on.

SCARY, SI!

(the only mechanism by which this could have theoretically happened,
according to the DBAs, is if a DBA with 'god' rights removed RI, deleted
some rows, then re-established RI. Personnally, I thought that SQL-Server
would choke on trying to establish RI if there were any orphans in existance
....)

Anyone else out there running into an Access-to-SQL-Server issue with
spontaneous deletions?

(FYI - this happened both before our agency converted to Office 2007 and
SQL-Server 2005, and afterwards. No apparent connection to the introduction
of these two new factors.)

Thanks for the idea ... I'll see if dropping/re-linking makes any
difference. Of course, absence of proof is not proof of absence!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[mscertified]

My situation is different to yours. I'm using Office XP and the db is Access
2002/2003. Both front end and back end are Access. There is no way to tell
when the deletions occured but most happened since July of this year.

The earlier suggestion that if the users ran a query and then deleted
'unwanted rows' from the result seems like a quite likely scenario in my
case. Is there any way to tell when a query last ran?

 

[John W. Vinson]

Is there any way to tell when a query last ran?

Not unless you log the transactions yourself (unlike the case with SQL).

 

[Jeff Boyce]

Handing the 'missing' IDs to the DBAs paid off. They found a few (not all)
of them.

When I examined the rows they provided, I noticed that all of them (the
"missing" records) had a Null in a field that my application uses to filter
by user. Because there's no value in that field, they weren't showing up in
my application.

Because SQL-Server 2005 doesn't offer row-level security, I used that field
to implement a pseudo-row-level security. If that field is null, the row
doesn't show!

So now the problem is smaller than disappearing records. Now I just need to
figure out how we can get disappearing fields! (I've asked the DBAs to
require non-nulls in that field as an interim solution, once I get those
values restored. Longer term, though, I'd rather figure out how folks were
replacing the previous values with nulls and prevent that!)

Thanks for providing a forum to discuss this. I'll post back with the
longer-term solution we come up with.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Scary, si!

... but wait, there's more! The way I was able to determine that rows had
gone missing was by finding 'orphans' in related "many" tables. In
SQL-Server. With Referential Integrity turned ON! (supposedly, with RI on,
it isn't possible to get a record out of a parent table until the related
child(ren) records are deleted ... and the SQL-Server DBAs concurred).

I've given the DBAs the lists of apparently missing parent IDs for them to
be doing their own research (hopefully via SSMS/Enterprise Mgr).

I used the "unmatched" query wizard to find child records with FKs that
pointed back at ... nothing. This happened in a half dozen tables, all but
one of which had RI turned on.

SCARY, SI!

(the only mechanism by which this could have theoretically happened,
according to the DBAs, is if a DBA with 'god' rights removed RI, deleted
some rows, then re-established RI. Personnally, I thought that SQL-Server
would choke on trying to establish RI if there were any orphans in existance
...)

Anyone else out there running into an Access-to-SQL-Server issue with
spontaneous deletions?

(FYI - this happened both before our agency converted to Office 2007 and
SQL-Server 2005, and afterwards. No apparent connection to the introduction
of these two new factors.)

Thanks for the idea ... I'll see if dropping/re-linking makes any
difference. Of course, absence of proof is not proof of absence!

Regards

Jeff Boyce
Microsoft Office/Access MVP