How to implement Ms Access in a safe Multi user envirnoment

(1) I'm required to deliver the access application system in a supermaket envirnoment, now the number of cashiers will be 20 (Users) accessing the same sales accounting or tables. My application is split into backend & front end of which per user will have their own front end linked to the backend on the server.My fear here is the locking system even if all my forms show the record locks property  - No Locks, any idea how go about this?

(2) I have also experienced some issues of power failure in some clients work place, now because of the same you find in the FE folder sometime their is an extra file generated after power failure any idea how to sort out this? Though my current advice to the clients is to invest in UPSs

Regards

Chris
LVL 3
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
1. Apart from proper error handling, if your code performs updates that might lock, you can use the technique described in my article:

Handle concurrent update conflicts in Access silently

2. Indeed, a small UPS for each cashier's computer is a cheap and simple investment. However, also network hardware like switches and, of course, the server must have an UPS to keep the entire setup running in case of a power failure.
Note too, that with an Access (accdb) backend, cashiers cannot be WiFi connected, they must use a wired LAN connection.

That said, in such an environment as this where uptime is critical, I would go for a backend database hosted on SQL Server.
ste5anSenior DeveloperCommented:
0) Even in the best Access FE/BE system, corruption can happen. Thus I recommend using a RDBMS like SQL Server, Postgres or MySQL as backend. These are much better prepared to handle DR scenarios.
The other fail safe mechanism is a local transaction log.

1) Locks? Where do you need locks in this scenario? I guess were are talking about a POS-like system. In such a system you only add rows to your transaction tables. You never delete or modify rows. Instead you add a cancellation row and add a new row with the correct values.

2) You need to look at deployment strategies. I would always delete the old FE directory on machine startup and copy a new FE.

And for the UPS thoughts: What is the exact reasoning? Active UPS makes only sense for covering instable power support (surges, frequency), but not for power-loss. Typical UPS covers only minutes.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
While you can do this and have a relatively stable set up, you are reaching the point where are you seriously want to consider the SQL Server backend.   When you start getting into 20 and 30 users you reach the point where you start wanting features like online back ups, restores Withrow roll forwards, etc. all of which jet cannot provide.

The main reason for commenting   However is the “no locks"  mention. No locks does not mean you have no locking going on. It means you have no edit record locks on the forms, which is pessimistic locking. That is the lock is held from the time the user starts to edit the record until it is committed. With no edit record locks you have optimistic locking which means the record or more correctly the page, is not locked until just prior to save and then  immediately released.

With any RDBMS system, you always have locking going on  in one form or another.

Jim
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
Thank you so much sir;


Regards

Chris
Armen Stein - Microsoft Access MVP since 2006President, J Street TechnologyCommented:
Hi Chris,

I'll add my voice to the suggestion that you use SQL Server.  We would never implement an Access BE in the scenario you describe.  Too many users, too many rows, and the data is too important.  SQL Server won't corrupt with power losses (or really anytime), and it can be backed up while in use.

You don't need to pay for SQL Server.  The Express Edition is free, and supports a database up to 10GB (far larger than Access).

To use SQL Server correctly with Access, you need to keep some considerations in mind.  I've written a PowerPoint presentation on this topic.  It's called "Best of Both Worlds" at our free J Street Downloads Page:

http://www.JStreetTech.com/downloads

It includes some thoughts on when to use SQL Server, performance and security considerations, concurrency approaches, and techniques to help everything run smoothly.

Cheers,
Armen Stein
J Street Technology
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
Thank you so much I will surely go that way!

Regards

Chris
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Accounting

From novice to tech pro — start learning today.