Risk of data corruption over WAN with shared Access database

Question

I am developing an application which uses as a back-end an MS Access database (.mdb, not my decision). Recently I came across someone suggesting that using JET engine over WAN is not really a good idea, with a high risk of data corruption. Since my application should be doing just that (connecting to database on NAS (EDIT: not NAS, shared shared network drive), I got worried. It is really that risky? If so, is there any work around or is an MS Access database just unusable for that kind of application?

EDIT

The front end is .NET windows desktop application in C# (WPF). The system does not have many users, max 10. Most of the time they will approach the database from LAN and 99% of writing to the database will be done within the LAN (from the area of the company). However there are some cases where they will connect to the NAS (EDIT: not NAS, shared shared network drive) from outside the company via network (from their home).

You need to include a lot more information such as what front end? I have an mdb on a web site for many years. There are very few users and very little editing. It all runs with ASP classic. — Fionnuala, Oct 29 '15 at 14:09
Can you use Remote Desktop for those users connecting from outside the LAN? — HansUp, Oct 29 '15 at 18:21

score 1 · Answer 1 · answered Oct 29 '15 at 16:46

1

If you have a 100 Mb/s fibre, it will be OK, but if your line is, say, an xDSL line, it is generally an absolute no-no.

Convince the powers that be to move the backend to a server engine like SQL Server where the Express version is free.

answered Oct 29 '15 at 16:46

Gustav

53,498
7
29
55

score 1 · Answer 2 · answered Oct 29 '15 at 17:43

The scenario you describe is not a good fit for having an Access database as the back-end. The WAN users could very well find the application slow, but the NAS is the real cause for concern regarding corruption, and that would affect both LAN and WAN users.

Many (most?) NAS devices run on Linux and use Samba to provide Windows file-sharing services. The Access Database Engine apparently uses some low-level features of "real" Windows file sharing that Samba does not always fully implement (ref: here).

In fact, the only time I've seen repeated corruption problems with a shared Access back-end (and a properly distributed front-end) was when a client moved their file shares from an older Windows server to a newer NAS device. The Access application continued to work for the most part, but every few months they would find that the primary keys of some tables would disappear after they did a Compact and Repair on the back-end database file. That never happened while their file share was on the Windows server.

Sorry I hadn't specified the problem correctly, it's not a NAS but just regular network drive, I didn't realized there is a difference. So you are saying that it should be ok? — MartinVotruba, Oct 29 '15 at 18:43

score 0 · Answer 3 · answered Oct 29 '15 at 16:04

Splitting a front-end from a back-end removes the majority of the risk of corruption. Of course, with Access there's always the possibility and if you're looking for something that reduces the risk to close to nil then you might want to consider SQL Server or MySQL. However, using Access is fine as long as you take proper precautions.

For example, you might want to look into record-locking on tables that will get edited, to prevent multiple simultaneous writes. Backing up your DB on a regular basis is always good, too.

Risk of data corruption over WAN with shared Access database

3 Answers3