Question How best reduce database corruption for multi user db over a WAN

kriswork

Registered User.
Local time
Today, 00:09
Joined
Jul 11, 2011
Messages
21
Hi All,

I'm using MS Access 2003 SP3.
I've created front / backend databases which are used over a LAN / WAN and VPN. Both databases are about 5MB each.
The backend keeps getting corrupted and most times when opening the backend directly through MS Access it has repaired itself.
I've about 20 users who are based local to the server, another 10 in another office(slow isdn type link) and 30 occasional users who connect over the internet using a vpn connection.
My guess is that the database is corrupting itself when the vpn connection falters or times out.
I've implemented code that does an application.quit so I can get everyone out of the backend database so I can repair it, but it doesn't always work as I imagine the front that had a problem will be unable to respond to the timer event to quit.

So, my questions...

Is there a timeout setting I can change so that the frontend waits a bit longer for the backend to respond?

If there is a problem with the frontend updating a record( I tend to use recordsets), can I capture this and maybe retry or atleast log what table was being updated?

Is there any option to increase the logging that Access write to the Event log?

I have 9 tables in the backend, one of which has hit the column limit a couple of times, but only has 500 rows. I know this sounds like bad design and probably could be better, but there is very little redundancy in each row. Would it be beneficial to split this table up as a way to reduce corruption (I'm not too worried about performance at the mo.)?

Should I try to use transactions as a way to reduce corruption?

I'm about to turn off autocorrect and subdatasheets as this may help.

My boss is getting quite twitchy when a whole department is sitting waiting for me to get the database file unlocked so I can then repair it (hopefully) and then get everyone back online.

Thanks for reading this.

Kristian
 
you would be better having the remote users logging in to a terminal server, rather than trying to connect directly to tables, but it's more expense.

you need to ensure that only 1 user uses any front end - each user should have their own copy.

you really need to sort your table design. it sounds like you are having to modfiy your tables to add columns, which is not what you should be doing. There must be redundancy. just having no null values in the table, sdoesn't mean it is well designed.

indeed having more but "liter" rows, may reduce the problems you have.

sorry, but it does point to a need for a redesign - which could be a long job.
 
Hi Dave,

Thanks for the reply.
How the database currently looks bears no resemblance to what I was originally asked to do (Originally it was going to run on Sharepoint with the logic done by the Sharepoint Designer).
The main table has grown in columns, but sort of follows the process.
The database is used to track the migration of data from one system to another. There are 100 or so steps to this with each step having several tick boxes, a timestamp and a textbox for paths and maybe a memo for notes. This equates to alot of columns but in hindsight could have been designed differently, but I'm kind of stuck with it now for the most part and so still need ideas on which way to best to reduce the likelyhood of the database becoming corrupt

Everyone does have a local copy of the front end which contains all the code and a few tables of this that don't change too often, like managers, teams, locations etc.
 
As Dave said a Terminal Server is a better solution as you can localise each user on the Terminal Server and with a generous SessionTimeout you can minimise data corruption. Besides, the Terminal Server sessions are connecting to a local resource and then only transmitting Screen Dumps across the WAN.

Simon
 
Hi,

Thanks for the suggestion - I'll look into the chances of using that - unfortunately most of our servers are very locked down with little chance of getting any configuration changes.

Kris
 
If your company has servers then it would not be a problem to add a Terminal Server. If I was you I would earwig the Management and argue that the current WAN configuration is susceptible to corruption and one day this is going to hurt the company.

Simon
 

Users who are viewing this thread

Back
Top Bottom