Data in Table 'lost'!!

[LB]

Hi

A very strange one....

I recently experienced 'lost' data in a table (yes, it's
true - I am not going loopy!!). The background is that
through DAO I imported data from another database into a
flat table to be used for reporting. There was around
8,000 records. As the report results did not tie in with
what was imported, I investigated. This is what happened:
- I would open the table - the correct number of records
were displayed.
- Did a sort on any other field, and the number of
records reduced (varying amounts) - by up to around 20
(grrr!).
- I then thought - "hey, it needs a unique field" - so
created an autonumber field. But, needless to say, same
thing occured.
- I then dumped each autonumber list (the original, and
the reduced lists) into Excel, and sorted them -
interestingly, it was always the top X records.
- I think (from memory) I also tried pasting the data
into a new table in another database - same problem.

Unfortunately the problem was sporadic and short lived
and has not occured again. This has happened to me a few
years ago, but at the time, I thought it was my
incompetence at fault! But now I know I was not
imagining things!!

Is this a bug? As I said, it has not reoccured, but as
this data is vital statistical information, used for
analysis, I have to be sure what is being generated will
always be correct.

Any ideas?

Thanks.

Regards LB

-

 

[Allen Browne]

The symptoms you describe suggest a corrupted index as the cause of the
problem.

When you select a field in table design view, the properties (lower pane)
allow you to specify whether the field is indexed. You can also view/edit
indexes in the Index dialog (View menu in table design). Additionally,
Access creates hidden indexes when you create relationships with referential
integrity enforced.

Access then uses these indexes to select and sort records. Which index(es)
it uses depends on your criteria and ORDER BY clauses. If an index is
corrupt, it may not contain all the records, so when your sort/filter
includes this field, the number of records will be fewer than the whole,
because the index is incomplete. "Fewer" can be just one unindexed record,
or it can be only a small fraction of the records if the index has been
truncated - usually because of an interrupted disk write (i.e. the computer
crashed, network failed, power failed, or user switched off while a write
was incomplete.)

Conceptually, think of indexes as being like the old library card indexes
that can find a book by author. When a new book is added (or the author
changed), the index "card" has to be created and inserted. If this process
is interrupted, the "last" card in the index might be an author starting
with B. Since this is marked as the last index record, the index insists
there are no more records after it reaches the Bs. (I actually saw that
once: no customers after B, due to a corrupted index, so the database
returned 600 instead of 13000 customers in some views.)

Most times, you can rebuild the indexes simplying by choosing Tools |
Database Utilities | Repair. In severe cases, it may be a matter of
carefully deleting the relations and indexes, and then exporting the view
that returns all the records to a new database, from where the application
can be rebuilt.

This kind of corruption is almost non-existent in a stable environment where
the power, network, hardware, and users are working reliably, the objects in
the database (tables, forms, reports, modules, ...) are not being modified,
the database is split (if multiuser), and service packs are applied
consistently.

 

[Guest]

Thank you Allen.

The information you provided was very detailed, and it is
appreciated.

I did suspect indexes, and subsequently changed a few
index settings. Even the compact/repair was not working,
hence, I was considering rebuilding - but then,
thankfully, as I mentioned, it did right itself. Maybe
this was to do with one of those changed settings? Who
knows!

It is essentially a front end, but as 2-3 users will be
accessing the table in question is being used on the fe
to store temporary report data. As this table will store
up to 750k records, the data is further summarised and
stored on other temp tables, which are in turn used for
the reports.

Again, many thanks - this will be useful information to
keep on hand.

Regards LB

 

[Jeff Boyce]

LB

Are you saying that there is a single copy of the database, and multiple
folks "hit" it simultaneously? If so, that can/will cause corruption, as
Allen mentioned.

 

[stevie]

How would this risk of corruption by multiusers be
reduced?
When you guys refer to splitting the database, do you mean
from front-end forms to back-end tables? Using 3rd normal
form? Or something else?

 

[Allen Browne]

By "splitting", we mean making sure each workstation has its own local copy
of the front end (queries, forms, reports, code) with attached tables
(tables in the back end, in a shared location).

 

[Jeff Boyce]

Stevie

Splitting an Access db means putting a copy of the front-end (everything BUT
the tables) on each user's PC, and the back-end (tables only) on a robust
server.

It's kind of a given that a relational database will be used with a
relational (i.e., normalized) data design. Not to say that it isn't
possible to "commit spreadsheet" on Access, but it isn't a very good idea if
you want to be able to use Access' real strengths and functionality.

Since you raise the question ("Using 3rd normal form?"), consider reviewing
the normalization as well.