Database keeps corupting

[Donkey1]

All,

Have run out of ideas on a problem we have been having for a couple of weeks. We run w2k network with users using access2000 on a database with FE and BE components. The BE is resident on one of our servers with the FE on each local machine that needs it. The system has been running quite happily for a couple of years until.....

A couple of weeks ago users reported that they couldn't get into the BE database because of an error message saying 'invalid database...not recognised' etc etc. The only way round this was to access the BE database and repair it. This would fix it for a while until the same thing happened again a few hours later. Some users also reported the error 'object invalid or nolonger set'. Once this was seen then the system crashed again. However, if users were already in the system, they could continue working as if nothing had happened, until they came out and tried to get back in.

Steps I have taken so far are:
Checked system for viruses
Repaired the BE database
Decompiled and recomplied the FE and BE
Repaired and compressed the FE and BE
Re-linked the FE to the BE
Created a new blank BE and imported the data into it
Checked out the refs - all appear to be the same except some machines use MS access calendar control 7 instead of 9.
Moved the BE to another server altogether

Have now run out of ideas. No one user seems to be causing the system to fall over. Several user have reported they can't get into the system with the same result that the BE database becomes corrupt and invalid. Something is happening which is causing it to trip-out.

Any ideas are most welcome...

Yours in desparation....Andy.

 

[rosiebob]

Having a similar problem with much the same setup (file server, fe, be). The size of the be mdb would grow 2x & 3x sometimes within 24 hours of compacting. (we now try to compact overnite as a Scheduled task - which seems to help)

Are you experiencing size growth also ?? I have no idea (just now) as to why this happens

I be reading your replies looking for a clue.

Good Luck,

Bob

 

[psemianonymous]

Corrupt Microsoft Access MDBs FAQ:

http://www.granite.ab.ca/access/corruptmdbs.htm

Threads on "object invalid or no longer set"--they look dissimilar to your problem at a glance, but it's worth a shot:

http://groups.google.com/groups?q="...tabases.ms-access&hl=en&lr=&ie=UTF-8&oe=UTF-8

Most of the people from comd.databases.ms-access say to check several things in case of corruption:
1. JET service packs are all updated
2. LAN Hardware is working (certain crappy NICs drop packets and this corrupts)
3. I can't think of a third thing, though I know there is one.



Other than the above I have no help for you. --
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

 

[Garridon]

You might want to check with your network people. After we converted to W2K, I had a horrendous time with my database. It corrupted several times a day and I did everything trying to fix it. Some of what I did helped some, but it didn't make the problem go away. Ultimately, it went away on its own, quite abruptly, which suggests it had something to do with the network itself.

Anyway, these are all the things I tried in addition to some of the methods you used. I isolated where the problems were occurring and targeted those areas--all on one form. I rebuilt the queries behind the form from scratch (this did help); I rebuilt the form from scratch; I rebuilt the tables from scratch; and converted to MDE.

 

[Donkey1]

All,

Thanks very much for all of your suggestions and help. I have been round all the users and found that some of them are still using W2K SP2 which does not have the latest version of the jet4.0 on. I have applied SP3 to all the machines that use the database and I am currently waiting for it for fall over again (or not as I hope!).

Just a couple of quick questions more if you have the time:

How can I set up my BE database to compact itself automatically every night? I've looked at scheduled task manager running jetcomp but when it fires off it asks for locations etc and you can't compact a db to itself, it has to be a different name.

Does any one know what the limits of Access 2000? e.g. max database size, max no of concurrent users etc?

Thanks...Andy.

 

[rosiebob]

Andy-

If I remember right:

Create a shortcut& change target to
source of access source of mdb /compact

put into scheduled tasks.

EX- C:\Program Files\Microsoft Office\Office\MSACCESS.EXE
C:\Program Files\Application\Name.mdb
/compact

Good Luck

Bob

 

[Junior1544]

one thing o note that i know i've seen on the threads here befor(A long time ago)...

things like this can happen (yes, to every one like this) if one of the computers using the database has a louse or not well made network connection... even one user with a bad cable can cause this...

Just my thought on this subject...

--James


junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[Donkey1]

May have finally solved this problem!

I think that our BE was corrupting because of our anitvirus realtime software. We run CA InoculateIT6 which installs with a real time scanner checking files coming on to our pc's.

I have now changed the real time setting so that any files associated with the process msaccess.exe are excluded from real time checking. Since doing this the system has not crashed once.

Will give it a few more days yet before I am totally confident.

 

[Junior1544]

Let us know once you're sure of this... I don't remember hearing about a virus scanner doing this befor, But hey, I havn't been using access for THAT long of a time

Thanks for letting us know the solution.

--James


junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[malo98]

Donkey1,

I had a simiar BE DB problem over a network using Windows 2000. The access system would corrupt 3-4 times a day and it was driving the users crazy. I am not sure if you solved the problem, but if not I have one for you or anyone else to try. There is a server setting called "opportunistic locking" that can be turned off. This is known to possible cause corruptions. The downside may be that other applications on the server may or may not be effected. In our case, our corruptions went from 28 occurences in April, to zero in the month of May. Just a suggestion for anyone out there looking for another possible idea that DB's are corrupting....

 

[Donkey1]

malo98,

During my exhaustive research into access FE/BE problems I came across the ops locking answer. However, I was put off by the possible side effects on other apps, so I steered clear of it.

As I write, it has been a week and a half now without a single crash or corruption. So, fingers still crossed, it looks as if the AV realtime was the cause.

It still seems strange that it started happening all of a sudden. The only thing that I can think that could have triggered it was a new build version of Inoculateit6. Either that or CA have changed the way their signature updates work in realtime monitor.

Not complaining too much though, as long as things stay quiet! Would like to some day migrate to SQL and have Intranet access to our DB system - have got a lot to learn before that day comes!

 

[denisedosp]

Donkey1, that is great to know. We had similar problems that the SPs took care of. I would have NEVER thought of that one though.
Denise

 

[MIKElaw]

We had very similar problems to this first on an NT server and then on a Novell. The solution, empiric as we have never found an explanation - set any printer other than the HP2100 as default. The problems then went away.

 

[Gibbie]

Donkey1,

We are constantly having access corruption. We use Norton Anti-Virus but I never mess with it. Can you do the same thing with Norton as you did with CA InoculateIT6? Can you elaborate more on what you did?

We are desparate and loosing customers over db corruption issues(we are a year away from SQL), so we need to fix this fast!

Thanks in advance!
Gibbie

 

[Donkey1]

Gibbie,

Haven't had any experience with Norton AV but what I did with CA Inoculateit6 certainly did the trick for me.

Ino6 has 2 parts to it, a normal AV scanner which you can set to scan all of your files on disk and a realtime scanner which you can set to scan files coming into your disk or leaving your disk, or both as it happens.

Our realtime settings are set to scan only incoming files i.e. only files that are written to local disk. Within the realtime settings there is a filter option which allows you specify processes and directories that will be exempt from scanning. I have added msaccess.exe to this filter which means that anyone running an access database doesn't get Ino6 checking the files every time you query the database.

With FE/BE setup that we have this is an enormous advantage as files are constantly being read and written across our lan to workstations. Obviously what was happening was that the realtime scanner was doing its job and checking every file that was coming over to the workstation when the user was in the database. This takes time, and in a multi-form FE database like ours it couldn't keep track of everything because of constant AV checking. This resulted in the database apparently corrupting itself and hence big problems.

Check Norton to see if there is some kind of filter on it to see if you can exclude msaccess.exe, which may solve your problems.

Hope this has been of some help,

Andy.