Missing Records

[David]

Hello,

I was wondering everyone thought on a problem that just
occured with one of my tables.

Yesterday I noticed that in one of my tables that all the
record prior to yesterday were gone. I never have seen
this before. I have lost records before, but that was
through curuption. With curupted records you would see
misc characters, jibberish, and here it's like the
records started at autonumber 680. Here is the DB design

Thers is a TBLStudentData and TBLTestingData.
These to tables are linked. The TBLTestingData table is
intact no errors.

In a form when a student signs in, their name and address
go into TBLStudentData, and the Testing data goes into
TBLTestingData.

Does this sounf like curuption or does sound like some
deleted the records.

 

[Allen Browne]

Hi David. Either of your suggestions is possible, but if you doubt that
someone deleted the records and suspect a corruption, the first thing to do
is to make a copy of your database (without overwriting any existing
backups). This way you get multiple attempts at fixing it.

One possiblity is that the index has corrupted, but the data is still there.
When this happens, the records disappear when the index is used to select
the records, but may reappear if the index is not being used:
1. Working in the copy, create a query into TBLStudentData.
2. Drag a field that is NOT the primary key into the grid, e.g. Suname.
3. In the sorting row under this field, choose Ascending.
4. View the query results. Any luck?

For more information, and suggestions on rebuilding a table that has a
corrupt index, follow the steps under the 2nd symptom in:
Recovering from Corruption
at:
http://members.iinet.net.au/~allenbrowne/ser-47.html

 

[David]

Allen
Thank you for the reply I tried your suggestions.
Nothing that I see shows the the db file is currupt. It
dos not crash,no other tables that are similiar show
signs of curruption, or is it running slow. I am able
retrieve the data from backups made on our network, so I
am not to worried about getting the data back from the
file. I'm at the point I feel this was done by accident
or on purpose. Is their any way I can track this kind of
thing in the future? I guess I would need to create a
log file that logs when a record is deleted and what time
Datem it was. Could you point me the right direction for
me to be able to do this.

Thank You Again

David Ehrenreich

 

[Allen Browne]

Access does not provide a way to do this, and if you let the users get to
the tables, you cannot log it. However if all entry and deletion is done
through forms, you can use the events of the forms to log inserts, edits,
and deletions.

For details, see:
Audit Trail - Log changes at the record level
at:
http://members.iinet.net.au/~allenbrowne/AppAudit.html

One possibility we did not discuss was cascading deletions. You may want to
check if there are cascading deletes on your relations (Tools |
Relationships), such that if someone thinks they are deleting a category of
student, they are actually deleting all students in the category as well.

 

[Armen Stein]

Access does not provide a way to do this, and if you let the users get to
the tables, you cannot log it. However if all entry and deletion is done
through forms, you can use the events of the forms to log inserts, edits,
and deletions.

For details, see:
Audit Trail - Log changes at the record level
at:
http://members.iinet.net.au/~allenbrowne/AppAudit.html

One possibility we did not discuss was cascading deletions. You may want to
check if there are cascading deletes on your relations (Tools |
Relationships), such that if someone thinks they are deleting a category of
student, they are actually deleting all students in the category as well.

Another idea for future consideration is to upsize the back-end database
to SQL Server. There, you can detect and log all changes to a table
using triggers and stored procedures, regardless of where the changes
are originating (forms, tables, queries, web, etc.)

--
Armen Stein
Access 2003 VBA Programmer's Reference
http://www.amazon.com/exec/obidos/ASIN/0764559036/jstreettech-20
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com

 

[Amanda C]

Hi Allen
I read your post below in the hope of finding help with a database that's
just died on me. I tried to compact & repair a database (Access 2002, in a
Windows XP OS) for maintenance & before completing it returned the error "AO
Index" is not an Index in this Table. Does this mean my MSysObjects Table is
stuffed?

I can't run the compact & repair utility now - I just get the same error.
Similarly, I can't create a new d/b & import all the objects because I get
the same error. I have tried running the Jetcomp.exe from Microsoft - and it
runs but before completing returns an error with compacting the database.

Do you think this file is corrupt beyond repair then? Any other suggestions?

Thanks in advance for your help.

Amanda
(also from Perth.. but working in London!)

 

[Allen Browne]

Hi Amanda. Long way from home!

If you are able to post a follow-up message rather than a new thread, I have
a better chance of finding and following through. Thanks.

There are various ways to approach this kind of issue. Nothing I can
guarantee will work, and many that probably won't even get started.

Presumably you can no longer open the table in design view to delete the
indexes that way?

You could try programmatically removing any relations the table is invovled
in, and deleting indexes programmatically. For an example of looping through
and deleting relations, see:
http://members.iinet.net.au/~allenbrowne/DelRel.html
There's a couple of functions below for showing indexes, and you can delete
from the Indexes collection as well.

If that doesn't work, the next thing is to try to create a query that sorts
by another field. It's just possible that this will circumvent JET calling
the corrupted index.

The corruption the jetcomp fixed should not be present in Access 2002.

There are some other things a professional recovery service could try, but
it then comes down to whether you have a good enough backup or whether the
expense is worth it for you.

HTH

Function ShowIndexes(strTable As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index
Dim fld As DAO.Field

Set db = DBEngine(0)(0)
Set tdf = db.TableDefs(strTable)
For Each ind In tdf.Indexes
Debug.Print ind.Name, IIf(ind.Primary, "Primary", ""), _
IIf(ind.Foreign, "Foreign", ""), ind.Fields.Count
Debug.Print " Field(s): ";
For Each fld In ind.Fields
Debug.Print fld.Name;
Next
Debug.Print
Next

Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
End Function
Function ShowOtherIndexes(strTable As String)
Dim db As DAO.Database
Dim tdf As TableDef
Dim ind As DAO.Index
Dim i As Integer

Set db = DBEngine(0)(0)
For Each tdf In db.TableDefs
If (tdf.Attributes And dbSystemObject) = 0 Then
For Each ind In tdf.Indexes
If ind.Name Like "*" & strTable & "*" Then
i = i + 1
Debug.Print i, tdf.Name, ind.Name, ind.Fields
End If
Next
End If
Next
Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
End Function