CODEWITHSUNDEEP
ms-accessjetwan
MartinVotruba
MartinVotruba

Reputation: 343

Risk of data corruption over WAN with shared Access database

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).

Upvotes: 2

Views: 722

Answers (3)

Gord Thompson
Gord Thompson

Reputation: 123829

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.

Upvotes: 1

Gustav
Gustav

Reputation: 55981

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.

Upvotes: 1

Johnny Bones
Johnny Bones

Reputation: 8404

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.

Upvotes: 0

Related Questions