table corruption on backend

Here is my problem:

We have a multi-user Access 2000 frontend/backend environment where the front-end database contains all of the forms, queries, reports, code, modules, etc and the backend contains just the linked tables. We have the front-end installed on about 10 computers, but the problem detailed below has occured with as little as two users.

At random instances of manipulating data (appending a new record, updating a record, ...) a table on the backend will become corrupt. The events that seem to spawn the table corruption are not constant (i.e. often they work normally). Various errors encountered include "Requires a newer version of the Microsoft Jet Engine", "Invalid field data", no error or response whatsoever, Access totally crashes out. These errors happen whenever access is attempted to the corrupted table. I am unable to recover any of the data in the table with queries, forms that access the table, or even importing the table from a new database.

I very much suspect that perhaps the data access from multiple users might be causing this corruption. The Client stations are running Windows 98 and we have tried backend on Windows NT & 2000.

Also, I have noticed that the 'ldb' file on the server tends to keep users marked as accessing the database long after they have terminated the frontend application.

Other notes:
I have totally recreated the tables in a new database, but the same corruption problem continues.
All attempts at Repair & Compact fail.
We have similar frontend/backend Microsoft 2000 database applications that are working fine. The backend of a similar Access application even resides on the same server as the one that my backend is on.

Any help would be greatly appreciated!

Hi Linda,

It really sounds as if you've done your homework and are still in a pickle.

First thing I would check is whether you have any make table or join queries and where the data types do not agree between the first tables and the make / joined table.

Secondly, I would check all of my data types to make sure that someone wasn't misusing one or several. For instance a number data type that someone is entering text into. Use validation and input masks to stop errant entries.

Thirdly, I would look for a user of this db who does not use the others that do not corrupt. Perhaps they are exiting the db improperly.

With some luck you might find the problem.

Troubleshooting a corrupt A2K db.

http://support.microsoft.com/default.aspx?scid=kb;en-us;306204

Autoeng, thank you for the ideas.

I do not have any make-table queries, and i've double-checked that all relationships(foreign keys) between tables and all data types agree.

As far as user input, on various forms input is derived from selections from list boxes and entry into text boxes. The entry for the most part goes into text fields, and a few date fields. After double-checking, i am positive that all date inputs are masked properly (most, if not all are done at the table-design level).

I'm looking into the idea of improper shutdowns of the front-end database, but I remember this happening while the only two stations that were active were (1) at my "developer" station and another one that I was supervising the users' actions.

If I didn't mention it in the first post, this table corruption has occurred on different tables, after different processes. It's hard to peg down one constant, in terms of which events (in programming logic) might attribute to the corruption, although it seems to occur during query appending/updating of tables.

Thank you again for looking into this issue for me!

I would go with AutoEng's third point. Perhaps someone is running a large query which is manipulating the table that gets corrupted. Its taking a while - user gets bored - next thing the db is being crashed out of (ctrl+alt+delete style) while the data within that table is being manipulated.

If you crash out of a .mdb file 'unnaturally' then the lock file will remain until you go back in and then come out again.
The next time it does it ask your guys whether anyone ctrl+alt+deleted. It could be someone is doing this and not telling you. If they say yes - then ask the obvious question why?!?!
Call me cynical but a 'random' problem like this more often than not involves an end user.

What about the possibility of someone manipulating the server mdb file whilst clients are accessing it? If you run a compact/repair its vital that no other clients are accessing that file.
Perhaps the fact that the .ldb file takes longer than you expect to disappear suggests that you are not aware of all the users or databases that are linked to it? Could you have accidently linked this table to one of your other databases you have on the server?

I'm running out of ideas now lol

Good luck

Dan

You say you had your developer's station up when a crash occured. Were you doing any work to the db? Just a thought.

Autoeng, While at the developer station, I was probably doing some combination of running test data and modifying front-end forms and code onto the local database. the structure of the tables on the back-end database was not touched. The developer's station frontend db being open, unfortunately, has not been a constant with these corruption of tables. In fact, while I was away on lunch today the corruption occurred (which prompted me to post this problem in the forum.) BTW, thank you for the earlier Microsoft link, I'm looking through it step-by-step.

Dan-Cat, I'm looking into the possible abnormal termination issue. I have only compacted/repaired the back-end database before other employees come in to work in the morning, and I've made sure that there is no ldb file prior to opening it. The weird thing about the ldb file is that, with multiple users accessing the database, when one of them ends the application, they are still retained in the ldb file (I can see this by opening it in notepad) for quite sometime after they've left the front-end. This makes me think that a possible multiple-user locking problem might be something to look into.

I'm currently combing through code removing some stray SendKeys commands in VBA (to pre-set a couple of forms), and i'm hoping (with crossed fingers) that this might be a cause of the problem?

Thanks Autoend & Dan-Cat for the replies! Keep 'em coming if anything else comes to you!

I don't think that the ldb file is updated when someone exits the db, only on entry. Then when the last person closes the db the entire file is deleted. Therefore, if you sign onto the db first thing in the morning and then Mary signs on right after you if you exit but Mary remains in both you and she would be listed in the ldb file until she exits and the file is deleted.

Everyone, thank you for your input!

I believe that I solved this issue.

A local computer on the network had a Realtek network card driver (rtl8029.sys) that was included in Windows 98. This driver has been known to send corrupt data packets and I believe that it was this that was causing the table corruption on the backend of the database.

Again, thank you all for your time & efforts!

Ooooo! Nice detective work. I hope this solves your problems. How did you discovery the card issue?

I may have a similar problem - how did you discover the NIC problem - are there any details anywhere ?
tia

Details about the Windows 98 default realtek driver problem can be found here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;189778

It took a few days, but I was able to isolate the table corruption to one computer & after talking with the user, I found out that they had stopped using another database (frontend-backend) program because it "had problems". After some testing, I found the initial error for the corruption was a "Disk or Network Error". Having seen this error before (along with many others that were found with the post-corrupted database), I first suspected the network card. Well, it turns out it wasn't the network card itself, but just that the default Windows 98 driver for Realtek NIC's sent occasional corrupt data.

Since mid-yesterday, everything has been running fine.

I will try this immediately !!

thanks again