I've a Multiuser Databes created with Access 97, then convert to Access 2000. It was running well with ServicePack 2. With SP3 and SP4 I've following problem: Database Corrupt.
Sometimes there are f.Ex. 2 Users (sometimes 5, sometimes 10 Users) working on the Database and the third one (ore next one) can't log in, because he gets a message: Database corrupt. The other two Users, whitch one are allredy logged in and working on the database, can work continuously normaly. If ther 3. one wil log in, all of users must log off, then i have to repair the database and then they can log in again.
Please, help me to solve this problem. Where i've to look, where could i find the solution ?
Access really sucks for multiple simultaneous users
This kind of problem is very likeley to happen when you have multiple users opening an mdb file in access. It seems the more users you have in the database at any given moment, the more likeley the database is to get corrupted.
You are lucky the database hasn't been corrupted beyond repair.
Make frequent backups. And I mean frequent.
I recomend reading the following support article:
300216: HOW TO: Keep a Jet 4.0 Database in Top Working Condition
Why do you tell that access sucks. I've program insurance system for brocker in lithuania in access97 5 years ago. This system is using in 40 remote offices with more than 300 computers relating over e-mail. Database coruption not a problem, because coruptions occures very realy - some in a year and in some offices only. Financial analitic operates with more than 10,000,000 records throu 6 main tables in central office with no less speed than existing insurance systems based on sybase and oracle existing in other insurance companies in lithuania. And there's no distinct difference of mdb speed from own computer and from LAN with 50 users in the same time. Mentioned company is seeking for new system based on sql server. It's evidence, but there's no one company, that can sugest faster and more functional system for about $200,000-$400,000 price. Small company can't pay $15,000,000 for tilia. Or to buy licences for $2,000,000 price oracle financials with IE or Netscape interface and do programing by itself or externals with additional milions and problems. Yes it's that access sucks, but...
Well... we're really not here to debate whether "Access sucks." We're here to help solve Znidaric's problem!
I think that the root problem is simply "all the various problems that crop up in Windows networks when trying to share a file." The problem has to do with cacheing, with having out-of-date info in those caches. SMARTDRV.EXE had it; opportunistic locking had it...
Probably the first bit of advice I'd give is: make sure that every workstation has exactly the same version of Windows on it. Exactly.
I'w advice use short names in tables, fields and queries without spaces. But i,m not sure. But if you are using national alphabet, you'll get problems with coruption in access97 as minimum. Why? I don't no...
Are you sure what problem not in LAN with netcard drivers? I'v had problem with converting my access97 to access2000. Resolve very simple - convert access97 to accessXP and saved to access2000 format. Why not?
There is a Jet database utility that you can download from Microsoft. I had a client with a Windows OS problem that would corrupt the back-end database file each time she went in to modify a report linked to the server.
The Jet repair utility worked, even after the built-in Access compact&repair option failed to work.
Another thing I've found (and this occurred more than once) that Access sometimes won't forget certain printers. We had a client that changed printers but the old printer (no longer on the shared server) kept showing up in the print dialog. As soon as the user started running the Access application it would crash.
I work a lot with Office products in general and have found that other things can corrupt the application such as toolbars that have corruption in them, missing resource files, bad templates, and bad data in the hidden Access system tables.
One thing you might want to consider is to create a new, blank Access database and move, export or drag&drop all the objects from the bad database to the new one.
We had a database once that should only have been about a 1/2 meg but instead it was like 15 megs and kept growing in size. I finally created a new mdb and put all the objects from the bad mdb into the new one.
The size shrank to like 200K. I never figured out what happened to cause the bloat/size increase on the old mdb - we had no viruses on the workstation - but I've noticed that converting Access projects from older versions such as 97 sometimes causes strange things to occur.