I have an Access database and a C # program working with it. While all testing in a single user mode. But the program itself must perform the role of a client application for the cadastral office, and therefore there is a lot of user access to a single database. How to avoid problems with simultaneous connections of several users, access to the same data, because there will obviously be a terrible loss of integrity.

  • 2
    server write awake? it will be your exclusive deal with the base. for work with data it is better to use transactions. In general, there are a lot of nuances, read about the three-tier application architecture - Dmitry
  • 2
    "How to avoid problems with simultaneous connections" - instead of Access use SQL Server (there are free versions). example of use for the map service - here - Stack
  • Thanks, I will look. But are there any options on Accsess? Since a considerable part of the work has already been done (and alteration of the entire database is rather complicated and time consuming ( - Alexey Sazonov

2 answers 2

There will be no loss of integrity, at least at the database level - Аccess is still a database, and it ensures the integrity of all data, including foreign keys. And yes, it fully supports transactions. Those. when working from .NET, it is almost the same as SQL Server, except for the connection string.

But you will have to revise your code for assumptions that may be false in case of simultaneous work of several users - for example, if users actively delete data - you should carefully handle cases of opening them for display.

The only real problem with Access is the limit on 10 simultaneous connections. What can be a very serious problem when hiring an 11th employee :)

  • 10 connections are quite normal for now) I’m still going to file a file on SQL Server anyway. It's just that the terms are burning) But frankly I did not quite understand about the false assumptions. In general, the initial alarm is that I’m afraid that a situation might happen when there will be two users writing to one cell. It would be ideal if only one connection to the database would work at the same time, while the others hung in the queue. I understand this is an analogue of serializable mode in SQL Server. On the account of the fact that it is very slow, I don’t worry, as the volumes of both the data and the staff are small. - Alexey Sazonov
  • @AlexeySazonov if two users write to the same cell - then who last recorded it is right (access will not allow simultaneous recording and damage of access). My office has a product - a time tracking system. works fine on the usual shared base access. - PashaPash ♦
  • @AlexeySazonov about false assumptions - for example, you have an order and lines in it. and there is a code that calculates the amount of the order in rows. so by the time the lines are calculated, it may differ from the actual amount in the database (because someone added a line at the same time). Those. there may be errors in logical integrity, but not in physical. But they will also be in SQL Server (if they are possible at all in your particular application) - PashaPash ♦
  • Thank you very much, I understand. I will test what comes out for me) - Alexey Sazonov

From the question it is not entirely clear how you are going to provide multi-user mode - whether it is a three-tier application (that is, there is a middle layer in the form of an application server), or just a client-server. I guess the second.

But in the case of MS Access there is a special situation - in fact, this is a file server. I once participated in the creation of applications that use the .mdb base in this way. The base is laid out on the file ball, when connecting from the client, the path to the file on the ball is indicated, of course, all participants must have rights both to the ball and to the file. Jet-driver correctly handles locks while simultaneously accessing tables, there are no problems here. But due to the fact that this is a file server, performance may subside.

What you should watch out for:

  1. The size of the base. If I remember correctly, there is an upper limit on the file size — it seems to be 2 GB. Accordingly, there’s nothing to devour a place, such as documents in the form of fields, and so forth in the database.
  2. After deletion, the old data remains in the database file - apparently, to speed up the delete operation. Therefore, from time to time you need to clean the base (Compact / Repair). For this operation, you need exclusive access to the database file.

About no more than 10 connections - https://support.microsoft.com/en-us/kb/154869 - this is only for 1.1, 2.0, 2.5; for Jet 3.0 it is already possible more. But there may still be a limit on the number of connections to the ball.

But in any case, we must understand that the file database is only for small tasks and a small number of users.