Access Scalability Advice

[Nimrod]

Hello Access Guru's:

I've got a bit of a problem in that I have a vendor that want's to sell a client of mine an Point Of Sales System that utulizes access as the back end. And I'm having a hard time believing the vendors product can support my clients enviroment .

The Access database would need to be able to handle the following:
1: Be able to contain between 2 million and 3 million Records
2: Be able to contain 50 - 100 Tables
3: Be able to handle 12 - 20 sessions accessing the DB at one time

Does anyone here have either comments on this or know where I can get some literature on the limitations of Access ... Thanks

 

[BrianB]

This contains some info
http://www.microsoft-accesssolutions.co.uk/access_specifications.htm#General

Interesting that they specify in terms of memory usage rather than numbers. Makes sense because tables vary in terms of row/column numbers.

I have found this a good forum :-
http://www.access-programmers.co.uk/forums/

 

[Norie]

Nimros

It sounds to me that this is out of Access' capabilities.

Especially the number of sessions using the database.

 

[Nimrod]

Hello Brian:
Thanks for the post and links.

Hello Norie
Yes Norie , that's why I posted this, however I need documentation to prove this to the client.

 

[CT Witter]

I would also keep in mind backup/restore and transaction ability. Access supports transactions but it definitely not enterprise level. In addition, there is no backup and restore capability like most enterprise db's.

HTH,
CT

 

[Andrew Fergus]

My only thought when I read your first post was "no way". The reason? Access is not sufficiently secure or robust for that sort of environment - I see Access as more of a financial support tool rather than an integral operational tool. Maybe I am over-reacting but the entire organisation will be depending on this system - the success or failure of this system will materially impact the business so, quite rightly, this decision should not be taken lightly.

I can't provide documentation to prove that it can't be done, but then I can't provide documentation to prove that a bus can't do the Canadian Grand Prix either. We might all laugh at the bus proposition (because it is ridiculous) but we all intuitively know the bus could finish the race, albeit a month after the winner. Possibly not the best analogy but I think you get my point.

Joking aside, the number of tables is not a problem and I agree that multi-user sessions and Access do not go well together - I would be concerned if some of the sessions were in remote retail locations (e.g. off-site branches?). How would they deal with that?

The weak point in Access versus enterprise databases is the number of records (as well as the backup & restore facilities), so attack that. Create a test model of the largest (expected) tables and stress test it with 500k, 1m, 2m and 3m+ records. You may even breach the 2GB limit before you get over 3m records (depending on how you set up your tables and fields). But keep in mind that records can be archived away from the main database so the number of transactions may not be an issue. Some years ago, airline flight reservation systems only held data for about 2 weeks before the data was archived away from the main operational system - clunky but it worked.

You never know but a stress test might prove that it can be done and you would then have a greater degree of confidence.

You client might also want to see working models / examples from the vendor at other clients - a low blow I know but you don't want your client to be the test site, not for such a crucial system. You need to put the onus of proof back onto the vendor.

My 2c
Andrew