Missing Records

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.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David" wrote in message
...
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
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
-----Original Message-----
Hi David. Either of your suggestions is pssible, but if
you doubt that
someone deleted the records and suspect a corrupton, 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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David" wrote in
message
...
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.


.

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David" wrote in message
...
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
-----Original Message-----
Hi David. Either of your suggestions is pssible, but if
you doubt that
someone deleted the records and suspect a corrupton, 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" wrote in
message
...
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.

In article ,
lid says...
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/AS...jstreettech-20
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com

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" wrote:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David" wrote in message
...
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.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Amanda C" Amanda wrote in message
news
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" wrote:

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" wrote in message
...
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.