Multiple forms causing data corruption?

[Guest]

Using Access2000 for development (end users using Access2003)

I have a table of clients, T_CLIENTS and companies, T_COMPANIES. There's a
1:M relationship between the two (1 client, many companies for that client).
There's an autonumber on the T_CLIENTS table.

I have two forms, one for the client entry, FRM_CLIENT, and one for the
company entry, FRM_COMPANY. User can enter a new client in FRM_CLIENT, and
from there, add companies under that client.

Both forms are bound to the respective tables.

Hopefully, that's enough to determine what could be going wrong. What is
happening is that sometimes, the T_COMPANIES table has a corrupt record. The
only way to fix it is to compact the db and remove the record (which, at this
point, has garbage data in the fields, including the key fields). I can't
delete without compacting first; if I try, it says "search key not found".
This error doesn't happen all the time, but it's happening more frequent than
before (once a day now, opposed to like once every couple weeks).

Any ideas what could be going on? I'm wondering if I didn't design the forms
correctly.

Thanks!
-Mark

 

[Guest]

Two common causes:
(1) are you or your users shutting down their PCs while the access
application is still running?
(2) is this a multi-user application and you don't have the application
split into a front-end and a back-end?

Dorian

 

[Guest]

Hi Dorian,

(1) they might be doing that, but I don't think so.
(2) FE and BE is split, sorta. The FE and BE are split, but there is only
one FE db, on the server. In other words, each user doesn't have a local copy
of the FE, they all access the same FE db.

I know that's not optimal, but it's the best that can be done now.

Thanks,
-Mark

 

[Douglas J. Steele]

The first thing to do is get a copy of the FE on each user's machine.
Multiple users sharing the same front-end significantly increases the risk
of corruption.

Take a look at the free Auto FE Updater Tony Toews has at
http://www.granite.ab.ca/access/autofe.htm

 

[Guest]

I'd like to be able to have the FE database on all the local computers, but
the change would take more time and effort than I have. It's not so much
doing the work, it's convincing management and getting the users to comply.
I've seen 2 hour jobs take 2 months because of issues totally unrelated to
the task.

So, I'm wondering if there's anything on the design side I can change to
minimize the error. We have another db that's on the server. One table, one
form, almost no data corruption. I gotta wonder if having multiple forms and
tables in this other database is what's causing it (or if I just designed it
in a less than optimal way).

-Mark