Data Corruption - possibly caused by form?

  • Thread starter Thread starter Adrian
  • Start date Start date
A

Adrian

Wondering if someone can shed light on a data corruption problem.

I have an Access database reading its data from a back end .mdb file. The
main data entry form is bound to a query which obtains data from two tables
which have a one-to-one relationship: ie primary key linked to primary key.

The form itself has a combo box with which to choose the relevant record
which then displays OK. There are a number of buttons which allow a sub set
of the data to be displayed if data entry is needed into these fields of
secondary importance. They are all invoked with code which includes

if me.dirty then me.dirty=false

before they open in order to commit unwritten data to the database first.

The main form also has a button which invokes a couple of reports based on
the record currently displayed on the form. It leaves the form open when the
reports are invoked, inspected, printed and then closed by the user.

Using this main form appears to cause apparently random data corruption in
one of the underlying tables. This usually manifests itself with between one
to three records in the left hand side table being corrupted.

This can usually be solved with a compact and repair, a delete of the now
corrupted records and replacement with earlier data. But it's incerdibly
disruptive for the user, and data is at risk of being lost.

The databases are the PC's hard disc (ie not over a network). The PC has all
the latest service packs as recommended by Microsoft's piece on avoiding
data corruption.

Any thoughts would be much appreciated!

Thanks
 
Thanks a lot. yes, both Office and JET have latest Service Packs. Have had
a look through your notes and I one further questions, if that's OK.

The corruption almost always seems to occur to the data itself, not to the
forms, reports, etc etc. Thus the database opens fine, and it's only when
data is called by the form from a specific corrupted record in the
underlying query that the problems manifest themselves. The data that gets
corrupted is in the back_end file, not the (mdb) front end. I could supply
..mde only to the users, but from time to time the users need to change some
of the reports in the front end, so they would need to recompile the mde
each time.

I wonder if that narrows things down to a smaller number of potential causes
than the whole list!

Any thoughts much appreciated!

Thanks

Adrian
 
Okay, if the corruption is occurring in the back end data, I'm going to
assume that the users are NOT opening the back end and modifying the data
structure. I will also assume each user has their own local copy of the
front end, i.e. they are not all using the same front end mdb file. If so,
we can ignore the front end, and focus on the corruption in the back end.

Naturally, you discover the corruption when users go to use particular
records, but that is not necessarily the time when the corruption occurs.
The fun bit is going to be pinning down when it actually corrupts.

Is it the same field/fields that tend to get corrupted? What data type are
these fields? And what version of Access? For example, there was an issue
with the early release of Access 97 where Memo fields tended to corrupt.
That was fixed long ago, but memos are slightly more prone to corruption
(due to the fact that they involve another pointer to the data rather than
in-line data.)

What locking strategy are you using? i.e. under:
Tools | Options | Advanced | Default record locking
"No Locks" is recommended (also called "optimistic").

IME, the issue is likely to be the inelegant shutdown caused by a user who
crashes out without closing Access, a machine that hangs due to hardware or
software issues, or a network card/connection/hub that is unreliable. This
results in interrupted writes, which leaves the database in an inconsistent
(corrupted) state.

How many users are likely to be logged in at any one time? How could you
trace this kind of instability? One way would be to add a table that logs
users into and out of the database. That's a matter of adding a record to
the logging table to record when the user logged in as part of your
initialization routine (e.g. Open event of startup form, or AutoExec macro).
There is no Application Close event, so leave a small unbound form open and
hidden, and use its Unload event to simulate it and log the user out. This
should give you a record of who is crashing out, and you can then
investigate why.

The API call to get the network user name is at:
http://www.mvps.org/access/api/api0008.htm
and the computer name:
http://www.mvps.org/access/api/api0009.htm

HTH
 
Thanks v. much. I'll report back on what I find in case it's useful.

Only one user using front end, not supposed to be messing with back end. And
on the unshared hard disc of a PC. No memo fields. Access 2000 on Win 2000.
Not sure which field gets corrupted, although it must include the primary
key since the left hand side of the query doesn't find its corresponding
field on the right hand side. The keys are text fields, obviously index
unique and set to be the primary index.

I am going to create a table which logs the opening and closing of a record
on the form which seems to be active when corruption occurs, one entry on
open and one on close. And try this with one table in the front-end .mdb
and another in the back_end to see whether there is discrepancy between the
two. This would detect any problems in the front end forms writing to the
back end file, I think.

Thanks again

Adrian
 
Back
Top