My problem is that my application requires unlimited number of users to access the DB.
multiple visitors to my website would often end up tring to update/edit/access the same record at the same time (simultaneously).
By demanding essentially real-time data accuracy and then allowing users concurrent access to the same record, you are making totally
incompatible choices that will doom you to abject failure. You'll NEVER get this to work in Access. You'll NEVER get this design to work right in any other DB either.
To make this work, you must do a simple yet profound thing. Choose a new data-flow design that does not require an update of the same record at the same time from multiple threads. Destructive interference is a fact of life that DOES and WILL happen. Your web users can NEVER EVER BE ALLOWED TO UPDATE THE RECORD IN REAL TIME!
You simply
cannot linearize "unlimited" web access without killing the performance of your site IF you insist on allowing direct updates of that record. I am using "direct" in another sense of the word than you are, perhaps - but trust me, what you describe is direct access. There would BE no locking problem if the access were not essentially direct, no matter how many layers are involved.
You need to linearize the algorithm. Build individual records that CAN be stored without destructive interference. Then, perhaps in a background process, apply the individual records to the counters you described in your earlier post. Do it as a linear process that cannot result in competition for that record from another thread.
Ask yourself some questions first:
What information in the database do the Web users need to see? In particular, do they need to see the contents of the record you are updating? (In a previous post on what appears to be this same subject, they did not. It was important only for your business, not for your Web users, to know the statistics.) If not, then why are they even toucing this record? As an issue in security, users should NEVER be able to trigger an event that causes data loss. Your current design invites that.
What aspect of the information you capture requires you to do a real-time update at your end? Is the real-time aspect so important that you will spend countless hours and bunches of money to migrate to another DB and maybe even a more robust platform? In other words, is the cost of getting this feature implemented worth the gain?
I suspect that someone in your management structure "has a dream." Wake that person up to reality by tossing in some cost estimates for time, software license fees, implementation costs, purchasing a more robust server to host said software, hiring another DB specialist & a new systems administrator for the high-powered server you want to run, .... The costs mount up quickly.
Forgive me if my response sounds a bit harsh. Sometimes it is necessary to be very blunt to bring people to a new and different mind-set. Sometimes that bluntness is mistaken for having a mean spirit. The latter was not my intent.
I am trying to get through to you that your problem is insoluble IN ITS CURRENT FORM. But I am NOT saying that you can't do something constructive that comes close. The art of problem solving is SOMETIMES the art of building something that comes CLOSE ENOUGH to what you wanted to satisfy all parties concerned. I.e. striking a balance between desire and possibility. In the end, peace is all about that kind of compromise.
