1

I'm developing a C# .NET 4.0 program that occasionally executes an insert query into an Access database on a network location with Microsoft.ACE.OLEDB.12.0. This program will be used by as many as 200 people simultaneously, which I think could lead to multiple insert queries executing at the exact same time.

Would there be an exception if multiple insert queries were executed at the exact same time? I'm thinking if there is an exception, I could make the code try it again once, so that a few milliseconds later it might not try inserting simultaneously. So the second question is, what exception would I look for to indicate multiple queries were executed at the exact same time?

//one of several queries that may be executed by the program
//access 2010 database on network location, Microsoft.ACE.OLEDB.12.0
using (OleDbConnection connection = new OleDbConnection(globals.logAccessConn)) 
{
    connection.Open();
    string sqlText = "INSERT INTO log ... "
    OleDbCommand command = new OleDbCommand(sqlText, connection);
    command.Parameters.AddWithValue(...);
    command.ExecuteNonQuery();
    connection.Close();
}

sorry if this is a duplicate - i tried searching.

Alex
  • 689
  • 1
  • 8
  • 22
  • 1
    I would be very leery of using an Access database in an application that could have as many as 200 simultaneous users. – Gord Thompson Oct 03 '14 at 13:51
  • There is no alternative due to company rules, but I am curious to know why not? We have other Access databases with as many as 800 applications connecting and it seems to run ok. – Alex Oct 03 '14 at 13:55
  • It's _extremely_ unlikely that 200 users would simultaneously open a connection to the database, even though 200 users are using the application which has these queries. The logic is such that these queries don't fire very often. – Alex Oct 03 '14 at 14:06
  • @AlexB The size limitations of Access vs the implicit reality of ~200 users utilizing an AccDB is really a cause for concern regarding your choice of database storage. Access has not evolved the same way SQL Server/Oracle/MySQL have. It is best used for RAD for small processes and is not ideal for a system supporting ~200 users. – Mark C. Oct 03 '14 at 14:14
  • Thanks for the explanation. I'm not very concerned about 200 users utilizing it, the logic is such that the connections are established infrequently, under narrow circumstances, and closed immediately following a short insert query. With 200 users running the c# application, I might expect 2 or 3 database connections at most. The questions I am asking are: Would there be an exception if multiple insert queries were executed at the exact same time? If so, what exception would I look for to indicate multiple queries were executed at the exact same time? – Alex Oct 03 '14 at 14:23
  • Access has a theoretical limit of 255 concurrent connections, but in most cases the practical limit on concurrent users is much lower than that. (10 to 20 concurrent users is often cited as a practical limit for a "typical" Access application). For your particular usage case(s) you might get away with it, but with 200 (or 800) users counting on the application there could be a fairly strong case to be made for switching to a server-based database back end. P.S. There are always alternatives.... – Gord Thompson Oct 03 '14 at 14:23
  • Thank you both, but these do not answer either of my questions. – Alex Oct 03 '14 at 14:30
  • 1
    You might this [this](http://stackoverflow.com/questions/1672077/setting-up-an-ms-access-db-for-multi-user-access) useful. – Mark C. Oct 03 '14 at 15:07
  • Thanks, from reading that it seems there won't be exceptions with 2 users simultaneously executing insert queries. – Alex Oct 03 '14 at 15:26
  • The answer was found here: http://stackoverflow.com/questions/694921/ms-access-mdb-concurrency – Alex Oct 03 '14 at 15:34
  • Well it wasn't entirely a duplicate since that answer was referring to update queries... – Alex Oct 03 '14 at 15:51

0 Answers0