I need to design a system that will capture about 4000 records
everyday, with 10 concurrent users. For the result analysis,
I need to join 8 to 10 tables.
Is it recommendable to use MS Access for the application, let
say in MS Win2K Adv Server? Any limitation with Access, in
term of concurrency, database size and performance issues
that I need to consider?
4,000 records a day is 4,000 times a client will pull more-or-less the whole backend .mdb from the server and 4,000 times it will send it back again with a new record. a backend .mdb does ZERO processing: all the smarts are at the client.
i have a 20,000 record backend .mdb with a complex table structure sitting on 800MHz NT4 file server via 100Mb/s switches. there is almost nothing else eating network bandwidth. three clients feed max 80 new records/day (20,000 records is three years data so average is thirty-something new records/day). performance is perfectly acceptable, but i'm slowly inching towards a mySQL backend for the new version.
you are proposing 50 times the transaction rate: your backend will reach my record count in the first week!
i guess your users run the risk of being disappointed after a few weeks, and your network people will worry where their bandwidth went.
simplest is to try it with a dummy 100,000 record file (6 months for you) on a single client. it can't get better with 10 clients!!