How to make my databases sturdier?

[sueb]

I work in a County hospital, and we are all on a County-maintained intranet. Although the IT department gets testy whenever I suggest it, I'm pretty sure that our intranet (servers, connections, etc.) is a little flaky. There will be days when my databases repeatedly fail to perform tasks they've had no trouble with (queries fail, table links fail, forms can't find their subforms, etc.); the next day, all is well.

Is there any way I can make these databases hardier? Any trick of coding or installation or anything (over which I have control)?

Any advice is appreciated!

[NeoPa]

It is fairly well understood that Access databases perform very poorly over a Wide Area Network. Access seems to have no concept of recovery from a data access failure. If any attempt to see data fails, then Access pretty well dies in the water. I never run Access databases except locally (If we have remote users who want access to the database we give them Remote Access to a machine that has the database available locally).

Access is also quite well known for not being the most robust engine around I'm afraid. That's probably much to do with multi-user access and it not always being set up to support that well in the options. If you have multi-user access then pay close attention to how the options are set up in Access on each machine that will be involved.

A back-end of something more grown-up (EG. SQL Server, MySQL, Oracle, etc) will also contribute to a more robust system.

Anything flaky in the network will manifest as problems. Access is particulary unforgiving compared to most other applications.

[sueb]

I was afraid that would be the answer. :(

However, about the options: What should I look for? What options should be set to what values?

[NeoPa]

That was a very quick response. I almost missed it.

The safest approach would be :
In the advanced tab set Default open mode to Shared; Default record locking to Edited record; Check Open databases using record-level locking.

Be warned - these settings can affect performance adversely in many situations. Try it out. Compare it with what you already have.

[sueb]

Okay, thanks for the tips, NeoPa! I'll check those out.