Smit-101 (2/23/2015)
Hi, thanks for replying. Our infrastructure chap is back from holiday and kindly provided a little more information...We are now running:
Failover file server,
Shared storage,
Using Distributed File System,
NOT load balancing,
Running Windows 2012 Server (Not R2),
We were on Windows 2008 R2
Finally, the provider used to show Microsoft.Jet.OLEDB.4.0, now its Microsoft.ACE.OLEDB.12.0
When opened Access shows as (2000 file format), my local machine runs Access 2010.
All users opening the HTML web form are located in the same office. I expect only 20 users max to be entering data at once.
As far as we can tell the issues are with the database lock file crashing when too many users input at once.
We originally thought the server was managing copies of files in different locations which in turn was causing sync issues. After our discussion with infrastructure this seems unlikely.
You're well outside of the recommended concurrency level for an Access DB in my experience. Anything much over 10 users tends to lead to a LOT of crashes and corrupted DB's. This also goes up dramatically if you have the Access DB in a folder being scanned by some real-time AV provess.
You should definitely look at moving the data into SQL Server rather than Access. I've used Access as the "UI" to a SQL server database for years in small environements, but the locking and consistency issues on the data required moving the data out (and spending some time optimizing data access). In its default state - Accesss tends to be very "write intensive" and will tend to ovewhelm disk substructures if it's in charge of persistence.
You should be able to run this in a SQL Server "express" copy, although I'd consider putting it into a Stadnard edition (which will make it much easier to use some of the automated tasks, etc...) Either way though - you will likely need a good amount of help from said infrastructure specialist AND someone versed in wiring up Access to external data sources AND a SQL SErver DBA: this is not trivial by any means.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?