Max number of Users in Access

[G37Sam]

Hello,

I'm to design an inventory/process management/accounting package for a factory using MS Access, expected number of users will be around 100.

The factory manager is doubting that MS Access can handle that many users while I'm quite confident that if split and properly designed, it will run without a glitch as long as the hardware supports it.

The manager does not believe in Access and needs some sort of supporting proof/documentation that it can handle 100 users. Does such a thing even exist? Should I email Bill Gates and have him send the factory manager an email?

Any help would be appreciated fella's

Sam

 

[PaulO]

Found this on Microsoft's website ... suggesting 255 users

http://office.microsoft.com/en-us/access-help/access-2007-specifications-HA010030739.aspx

 

[gemma-the-husky]

It certainly depends on the design of the system, and of the type of use the users are doing. The more intensively the users are pushing the system, the fewer users you will support.

You would certainly be pushing a scale Access back-end to service 100 "intensive-active" users - but I expect a SQL based back end would be challenged as well, with that number.


The other thing is, you are probably taking on an enormous project. Do you work for the company, or are you a consultant? Process management and inventory are very complex systems. You need a very clear brief of the project.

 

[G37Sam]

Thanks for all your feedback, I will certainly use that link PaulO

The other thing is, you are probably taking on an enormous project. Do you work for the company, or are you a consultant? Process management and inventory are very complex systems. You need a very clear brief of the project.

Oh of course, I have 15 pages worth of flowcharts (some that didn't fit on A4 paper) and over 50 inter-departmental forms. I will also be meeting with the factory manager on a weekly basis until he gets bored of me.

 

[ChrisO]

Why would it have to be Access XOR something else?

Access is probably the easiest, fasters, cheapest way to produce a prototype.
Build, modify, and test in Access; it’s cheap.
If it works under load then use it.
If you need to port it to something else then the basic design is already done.

Chris.

 

[Galaxiom]

Use Access but design for a server based backend from the outset.
Be sure to avoid Access only features like Multivalue fields.

 

[G37Sam]

How hard would it be to switch to an SQL server down the road? I've never worked with SQL Server before

 

[gemma-the-husky]

out of interest - do you have a lot of experience in all this? This is an enormous undertaking.

 

[The_Doc_Man]

How hard would it be to switch to an SQL server down the road? I've never worked with SQL Server before

If you do a front-end/back-end split, you have half the battle already done. There is the issue of avoiding things that your chosen upscale back-end couldn't handle, but otherwise there is actually a wizard that will help you with the back-end upgrade.

 

[G37Sam]

out of interest - do you have a lot of experience in all this? This is an enormous undertaking.

I've been using Access & VBA since high school and it has never let me down so far, I'm pretty sure I can do it

If you do a front-end/back-end split, you have half the battle already done. There is the issue of avoiding things that your chosen upscale back-end couldn't handle, but otherwise there is actually a wizard that will help you with the back-end upgrade.

Thanks for the tip, they client is being stubborn and insists on using SQL Server for his back-end for reasons he can't explain.

 

[gemma-the-husky]

out of interest, this came up on another forum

http://www.utteraccess.com/forum/Microsoft-Access-Database-t1968157.html

 

[stopher]

Thanks for the tip, they client is being stubborn and insists on using SQL Server for his back-end for reasons he can't explain.

Given the choice, I would recommend SQL Server too.
I'm sure Access will run 100 users. But, as others have mentioned, you'd have to stress test.

The strain wouldn't just be on the server. The network traffic under Access B/E would be increased. Also, you might want to consider the file size given that you have 100 users all generating data.

Then there's security. Of course is possible to apply a reasonable level of security with Access. For for 100 users I think I would want to use a B/E that is deisgned around security.

Also think about backup and recovery. How mission critical is the system?

I really can't see why you wouldn't go with SQL Server if the client is paying. Why take the risk with Access B/E?

Chris

 

[Galaxiom]

If the client is insisting on SQL Server, grab the opportunity with both hands. Why not?

Your resistance can only suggest you are uncomfortable with SQL Server because you don't have the experience. Continue down this track and you will be powerfully conveying that impression to the client.

You are pushing the envelope while sticking with what is comfortable because you "are sure it will do it", yet this claim based on faith alone because you haven't done a project on this scale before.

One day this strategy will reveal its limits when it breaks. Maybe not this time but you won't see it coming when it inevitably does.

The posts you have thanked are those that encourage the Access backend. You have asked this question not for advice but for support of your prejudice for Access. Take a step back and look at it objectively.

If you go with an Access backend and it doesn't cope then the client will have every reason to say "I told you so" and they will be right.

You also need to also think about the capacity for expansion. If the system works well it will facilitate growth and it is obvious that SQL Server will be better able to cope with the future. Moreover a front end designed to work with SQL Server is far more compatible with a wide range of database servers.

 

[spikepl]

A little OT: I am totally impressed by your client going for design from scratch of an entire ERP/MRP/Whatever system on this scale, instead of customizing some COTS system. I wish I had clients like this

BTW: Don't forget transition from whatever it is they have now, and migration of data - there are days and weeks of "fun" hidden there

 

[G37Sam]

If the client is insisting on SQL Server, grab the opportunity with both hands. Why not?

Your resistance can only suggest you are uncomfortable with SQL Server because you don't have the experience. Continue down this track and you will be powerfully conveying that impression to the client.

You are pushing the envelope while sticking with what is comfortable because you "are sure it will do it", yet this claim based on faith alone because you haven't done a project on this scale before.

One day this strategy will reveal its limits when it breaks. Maybe not this time but you won't see it coming when it inevitably does.

The posts you have thanked are those that encourage the Access backend. You have asked this question not for advice but for support of your prejudice for Access. Take a step back and look at it objectively.

If you go with an Access backend and it doesn't cope then the client will have every reason to say "I told you so" and they will be right.

You also need to also think about the capacity for expansion. If the system works well it will facilitate growth and it is obvious that SQL Server will be better able to cope with the future. Moreover a front end designed to work with SQL Server is far more compatible with a wide range of database servers.

Thanks for your post, and yes, what you're saying is right. I'm very confident with using MS Access that's why I was pushing for it. But if SQL is what they want, then SQL is what they'll get. I've bumped the price up a bit and already purchased an SQL server book for my reference.

I'm guessing it'd make more sense to design it in SQL from scratch rather than use Access then migrate it to SQL?

 

[Galaxiom]

Definitely best to build it as SQL from scratch. You get some big performance advantages using SQL Server with triggers and stored procedures.

Note that the Developer version of SQL server is available for a trivial price because they want developers to use it. It has everything included but I am sure they take a very dim view of anyone using it outside of the licence agreement.

 

[jdraw]

There is a person on another forum who wrote, sells and maintains an ERP system in Access. You may get some ideas from reading his FAQ or just talking to him.
He has a website http://www.e-z-mrp.com/faqs.htm

 

[The_Doc_Man]

This is the reason that any active back-end server will be a better choice than raw Access Front/Back. Network load will kill you if everybody gets online at once. The big gotcha is that Access must bring the entire table through its workspace to process the records, and if it has an Access Back End file, that is the whole table. Because the original design of Access is that a Back End file in Access is PASSIVE. The server is just a file server. You are doing remote file scanning, but your workspace is still on your workstation. That is where the work has to be done.

If you have an ACTIVE Back End such as SQL Server, Access can be "told" that it has a helper - the SQL Server. Access will pass SQL to its helper. The helper will locally scan through the back end files and return ONLY the result set, not the whole table, for a given query. Network traffic on an update or delete query might be nil until the query is complete. The whole mess becomes a localized set of queries for the SQL server plus transfer of SHORT query text from Access and transfer of (relatively) short result text from SQL Server. When dealing with a hundred records, this is chump change. When dealing with 100,000 records, network traffic becomes significant.

It is this network traffic that will govern the EFFECTIVE number of users you can manage. You can define a hundred users - but if your load overwhelms your network at ten users, guess what your REAL limit is?