I'm writing C# applications that store their data in MS Access databases, (I'd rather use a different type of database but currently have no other option). The applications are in use by multiple people at a time and so when there are multiple connection attempts (or someone mistakenly opens a table), some of them fail to connect.In order to get around this I've written a couple of methods that reattempt a connection/disconnection using Thread.Sleep(n) inside a while loop (I realise the disconnection reattempts probably aren't necessary but have left it in just in case).Is there a better way of accomplishing this? I was reading this thread on 'Why is thread sleep so harmful' and I thought maybe in the case it would be alright. I'd rather have the application look like its frozen for a few seconds than pop up an error message for the end user, which would cause confusion and my inbox to be inundated by users requesting support. Am I just looking at this completely wrong?x-posted from code review SE, @mdfst13 advised 'You might get a better answer to your primary question from Programming.SE, as one way to address this would be to add an intermediate service to talk to the database.'
Connecting to an MS Access database (OleDB) with multiple users
c#;database;microsoft access
The solution that I was suggesting is introducing a service that talks to your database.  Your application would then talk to the service.  The service would maintain a permanent connection (or connections if it has multiple threads or instances).  The advantage of this is that instead of retrying the connection, the service can queue requests.  It can only process a fixed number at a time, but it can continually process them.  This gives it a higher throughput than a system where you are waiting for a connection to be available.  For one thing, it eliminates the need to connect or disconnect.  More importantly, it won't be sleeping while waiting for the connection to become available.  Another advantage is that you can process the request queue in order.  In your application, bad luck could result in an application exhausting its retries.  If another application grabs the available connection just before your application wakes up, it can run out its attempts even though a connection was available.  The service always answers requests and you can process the oldest one first.  That gives a more predictable behavior.  The first downside of the service is that for a single client, it is more overhead.  It has to do all the same work of querying the database while also communicating with the client application.  The second downside is that you have to run it somewhere.  Since there is contention for the database, it is apparently a shared resource.  Perhaps it could run there.  Or maybe not.  A third downside is that it is more programming work.  You have to write the intermediate service.  Most of the queries can be based on what the application currently has, but then you have to write a service that processes requests and modify the application to call the service rather than the database.  While the list of downsides is longer, I think that the upsides are often stronger.  Higher throughput and more predictable behavior are both useful.  The downsides are all addressable.  Apparently you have a programmer to do the work (you).  Hopefully you can find somewhere to run the service.  And the increased overhead on the easy case is likely more than offset by the improved behavior in more difficult cases.  Most people won't notice an additional second of overhead so long as it returns in less than eight seconds total.  But waiting through multiple connection attempts can take you over the eight second threshold where people start getting frustrated.  A side advantage is that this decouples your application from the database.  So if you change your database, you don't always have to redeploy the application.  You only need to update the client application if the service interface changes.  I'm thinking of a service composed of a web application.  So your client application would pass parameters which the service would convert into an Access query.  The service would package the results and send them back to the application.  While waiting for the next request from the application, the service could process other requests.  WSDL-based services are designed for this kind of thing.  Note that I have limited experience with C# and Microsoft Access, so I may be missing subtleties related to those two aspects.  I'm more familiar with using this pattern with a Perl or PHP frontend talking to a C++ or Java service.  