Database disaster....

[systemx]

Hi all,

I have an Access 2003 DB that has been in use for about 4 months. The database is split F/E (local) and B/E (networked drive). It may have up to 30 concurrent users some of which are Access 2000 users.

Today it crashed in a big way.

I have had a number of instances where the B/E has become corrupted and a simple Compact/Repair fixes this up.

Today was different and these were the symptoms/steps taken (in order) -

1. Several users complained that clicking a command button (to create a new record) was not working.

I checked this problem initially, and found the code was being called but the 'write' action was not happening.

2. Compact/Repair backend. No difference.

3. Compact/Repair frontend. No difference.

4. Attempt to open front end. AutoExec macro fails.

5. Bypassed the autoexec. Got a 'Network may not be available error'. The network was available.

6. Attempted to import into a new DB. 1x Form and 1xStartup Module provided the same error as above.

7. Imported form and module from a backup. I had to replace a control on the form which appeared to be 'corrupted' - but it all seemed to be working again.

8. Then....I found out that the Autonumber had lost it sequence.

About a month ago, I started archiving old records by appending them to another table and deleting them from my main table. The database worked perfectly still. All of a sudden after this crash - it has lost it's sequence numbers and is trying to 'fill in the blanks' in one of my tables.

Now...on to my question. I have fixed all of the above problems using the common problem resolution techniques (import, compact, append query to fix the sequence problem, etc...)....but...

What would cause this to happen?

I wan't to prevent it happening again (3 hours to fix it!) but need to try and determine the cause first!

Thanks for any advice,

Regards.

Rob

 

[Dennisk]

The most common problems causing Access to become corrupted are :-
Users turning off PC whilst the db is still open or and this is probally the most common cause these days, a network glitch. I recommend you keep a security copy of the FE so if you have another crash you only have to repair the back end and re-link the front end to the data.

 

[GaryPanic]

not sure if this will help
but when you compact - first copy it on to a local drive /pc and compact it there - (a) faster (b) better results - also you will have a orgainl version (corrupt but still orginal )
also keep an empty back end as well - just in case so if it goes tits up you can try and import data into it

i used to also find on access 97 having the number lock on cause problems

Or the users who had NL on had more problems - spoke to my IT guy at the time and he said it was daft - but agreed it did seem to make some difference (weird shit)

might not help

also could be slow network or sloww connection on some PC thats lag behind the others causing probs - but thats network stuff - not my bag -
g

 

[RuralGuy]

And another opinion: Preventing corruption

 

[The_Doc_Man]

The problem with FE/BE splits is that several sets of file locks are involved. The ones on the FE (local machine) and the ones on the BE (file server). Not only does Access have record/table/object locks recorded in the .LDB file on the server but WINDOWS has file locks on both machines. If you have a hybrid FE/BE split (with temporary tables in the FE) then there is also the .LDB on the FE machine.

If you have a network hiccup - even a SMALL one - such that you think the network is up but for a moment it wasn't - then you have the potential for a really confused situation as Windows and Access BOTH try to sort out the locks. The failed network connections have to time out before they go away, and any locks dependent on those connections only slow down the process of image rundown.

What happens (and this is an overview) is that the file connection request comes in over a fixed port number but that request merely leads to a brief negotiation in which the server assigns a temporary port number to the incoming request for file traffic. Thereafter, traffic in the session connecting the local machine to the server rides that negotiated port. The server will likely have many such negotiated ports to support file traffic. If the network hiccups even for a moment, the next request for that port is in trouble if it is long enough that the connection starts timing out or loses synchronization.

If that happens, the local machine might well abandon the first port to open a second one - which looks like a new session. The locks associated with the old session are still in place waiting for the session to terminate so that image rundown can occur. And that means the locks are still visible on both sides. Which leads to all sorts of locking conflicts.

I suspect that the odd button behavior you noted - and particularly the inability to write in the event code - comes from locking conflicts. Any time something has to write to a locked file, if you didn't specify lock-conflict behavior, you will wait for the blocking lock to be released. (=hung process.)

A way to confirm that you lost the network is to check the local machine event logs. I think SYSTEM logs would do it but APPLICATION logs might also have good info. The app logs will not be on the server because there is no separate application running there, it is just a network file-sharing thing that is inherent in Windows protocols. But the local machines ARE running Access so might see an Applications event.

I believe this is the FIRST place to look to get definitive answers.

You might also wish to advise your users that when a lockup like this occurs, they must not make matters worse by turning off the machine. That will ABSOLUTELY guarantee issues on the server end of the network as it tries to run down all the open connections to the local machine that just got rebooted. And that will take time. And it will be visible to other users. Who will then be likely to take the same (incorrect) action.

Patience is a virtue and I've yet to see that many virtuous users. So besides doing research on whassup (or whassdown?) you also have user education to contend with. Good luck.

 

[systemx]

Hi and thanks all for the advice.

Ruralguy....the link provided provides some fantastic and simple advice.

Doc Man...your post has perhaps inspired me to investigate redesigning elements of the database to reduce the need to send/retrieve over the network. I'm thinking I could approach it differently by storing data locally and using a post function a couple of times a day.....

I agree I need to work on educating my users who not generally not 'tech savvy'...

Thank you to all...plenty to think about!

Regards,

Rob

 

[The_Doc_Man]

If you can redesign things so that you only need to update the central DB for a brief time, you reduce your exposure to network hiccups. If it is do-able, by all means do it. Don't forget to build in some smarts so that you can confirm the number of things you sent during one of these connection sessions that does the updating.

 

[MicroE]

My suggestions:

1 - Decompile to avoid corruptions
http://www.fmsinc.com/free/newtips/access/accesstip7.asp
2 - Create an MDE file for the FE
3 - Deploy the FE to each user’s local PC.
http://www.myaccesstips.com/deploy/deploy.html

 

[tim419762]

I've had the same type of issues in a situation that was similar. After some time and frustruation, I set up a form on the front end that was opened and hidden upon the user entering the FE. Every time the user opened or closed another form, the timer on the hidden form was reset. If the timer on the hidden form ever hit 55 minutes (which typically indicated a lack of activity), a message box popped up. If the user responded to the popup box, the hidden form's timer was reset. If the timer ever reached 60 minutes, the application was closed.

Upon implementing this system, crashes went away.

 

[RuralGuy]

The more I think about it, the more convinced I am that the idea from tim419762 is almost a must for any multiuser system. MVP Jeff Conrad has several links to such code: Kick Users Off - Detect Idle Time And searching this forum will turn up a few examples as well.

 

[systemx]

Thanks....I will definately add this feature next time I patch the F/E!

 

[MicroE]

Jeff just accepted a job with Microsoft on the Access Team. His new title is Software Development Engineer in Test. Also, the book he co-authored will be available in a few days. Access Inside Out