Microsoft Access Team Blog

I'm running your exact same scenario with Citrix and Access 2003 with about 80 concurrent users and have no problems at all.

I’ve been a fan of thin client technologies for a long time. I can’t say I’ve had anything with a 100 users. However, since your back end database is using SQL server, I really can’t see why there’d be any problem having 100 users on the system. I mean if your system performed extremely well with 30 users, it likely to work well with 60.

This really comes down to how optimized and how well your application runs with SQL server. You don’t mention if this is a ADP project,, or mdb linked tables to SQL server?

If you’ve spend time to optimize and remove some of the slower bottlenecks and queries in the access front end part, then I think you should be fine.

On the other hand if this was just a front end and back end application that in some point in time was upsized to SQL sever and you NOT put much efforts in performance turning, then this application might not scale. The reason for this is that your front end to SQL server is a LOCAL connection without any network (or a high speed lan). This setup is SO GOOD that it can hide performance bottle necks. I mean it is hard to kill sql server with 30 users! However if you scale to 200 users, then you might start to notice these lack of performance issues.

If your application is set up well to behave with SQL server, then I don’t think you should have a problem scaling to 200 users.

I don’t think the 100 users is problem in terms of ms-access here. The 100 users issue will be much that of how well the terminal server (or Citrix) handles that many users. A 100 users on terminal server needs some good hardware.

The other issue here is you have to start taking a little bit more caution and sensitivity as to having concurrent users wanting to work on the same information. Most overall designs in access applications tend to resolve down to the record level editing. When you start having applications with 200 users, then often the needs arises that more then one user may need to update data in the same record.

If the general workflow and design of this application is such that users will NOT be stepping on each other to edit/use the same record, then think MS access can work quite well given your setup of thin client and using sql server.

Albert D. Kallal

Edmonton, Alberta Canada

kallal@msn.com

Thanks Mark that's encouraging. Any reason to suppose that your deploy would run into issues if the concurrent user numbers doubled?

Albert, it's an mdb with linked tables and a mix of data requests via plain Access queries and some sql server sprocs called via passthrough queries. I will be moving more of the system into stored procedures over time. I know what you mean about larger user populations and concurrent edits on the same record. I may have to put more focus on that, but the data is fairly partitioned between certain groups, so the likelyhood of collisions isn't quite a great as it might seem.

I commend this article.  Their numbers are in the 15-20 range across a large number of sites but errors are very and stability is high.

http://vb123.blogspot.com/search?q=niche

Garry

Ok, so it standard mdb to sql server. That is the usual choice for moving existing applications to sql server.

As you’ve mentioned, you’ve started paying attention to some of the things like using views and pass through query. You also commented on the concurrency issue.

In most cases just as long as you tend to have some kind of prompt or search form in which you ask the user what customer to work on and THEN open up the form to the one record, you be in good shape.  You just want to avoid forms with lots of records and users searching with ctrl-f.

Sounds to me, you’re already setup and addressed some performance issues anyway. I am sure you be just fine with a 100 users.

You have to admit that access has really an amazing amount of flexibility here, from a little desktop system, to being teamed up with SQL server. When things like a wide area networks come into play, then  you can use terminal services. And lets not forget we have SharePoint options also.

There really no shortage of flexibility and choices we have for access applications, and some of these choices allow good scalability.

Albert D. Kallal

Edmonton, Alberta Canada

kallal@msn.com

I previously worked as a developer for a company that used Access 2000 for all their internal programs. Those programs were running on a Terminal Server with regular Access databases. At the time I change work we had around 10 Access applications that were being used by 15 people, so there were 150 concurrent Access processes running at the same time without any problems. Each process used about 25 MB. So the server had plenty of memory and fast hard drives. This setup worked well, the system was fast and easy to manage.

What I learned was to be careful so that each application (which was quite complex) was running good and did not leak memory or did other bad stuff. And to optimize the queries so that the query only returned the data I needed. With other words, what you always have to do as a developer. :-)

What I learned is that Access can do and cope with way more than people usually thinks.

If you have a application of size of 100+ concurrent user, its time that you move your application to better platform.

Access is like a 'MIRAGE' you might see that solution is in sight and try to push yourself and your company, but going there, there is another work around to be done, putting your client and your company under lot of trouble, headache and heartache.

In my work experience over 10 years (starting with Access 1.1) I realize that Access is good for in-house development and a small user base or reporting purposes. Other than that its a risk.

For example, you setup Terminal services, your are connected to HO server from showroom POS, fine, now, how you will you setup printing invoice on your local printer in your showroom? Things like this will pop up with such type of solution.

And let say you finally achieved solution with MS Access, at what COST? And what maintenance it will require over the time of support/warranty.

I am not against Access, I make living out of it. But these are my experiences.

Hope this helps

regards

I have nearly 110 users using ADP, across 4 terminal servers. No Citrix, just remote desktop to the terminal server. Using Access 2003 runtime.

For the frontend, I use Tony Toews AutoFE program, to copy the frontend into the "Documents and Setting" for each user. So they each have their own version of the ADP frontend.

Each user is running their own version of access. But then each user is also usually running Outlook, Excel and Word. So Access will not be near the top of the list for memory usuage.

Looking at Task Manager on one of the servers now, I see that most of the Access instances are around 22Mb, while Outlook is 85Mb.

If you're scaling to have user use Office, you'll have no problem fitting Access in.

We have several instals of multi-user apps on Access 2003 using Terminal Services.  No need for Citrix.  Windows Server 2008 is recommended for it's remote printing capabilities, but 2003 is good too if you limit to redirect only default printers.

50 users run Access just fine on Terminal Server.  As long as you have enough RAM.  If you have an Access back end, I recommend using multiple BE files having tables separated by user role to cut down the number of concurrent users in a single BE file.  Nightly repair and compacts of all BEs is a must.  We use Group Policy logon scripts to copy a fresh version of each front end into the user profile directory and delete the files at logoff.  This keeps the files small and doesn't leave copies on the server when the user is no longer logged in.  This is also a way to implement group security as users will only have the app if they are in the group.  Makes it pretty handy to roll out changes.  Simply update the production source file and ask users to login again to get the changes.  

We prefer to have Access apps which run on TS only accessible via TS.  That way we can use linked tables to local drives for better performance.

Multiple front ends are handled by adding a reference (like a library mde) in vba to the other app.  That way a user has only a single instance of access.exe running in the server.

I've never used Access run-time so I'm not sure adding references on the fly is an option with that.

Best of luck,

Josh

In my experience i have a access front enb, mde or accdr, with the following experience.

One terminal server, DELL 2850 Terminal server, fast hard drives, 4gb of ram, with windows 2003 standard, using the runtime, we used from 2003 until 2007 came to the market, then we changed to access 2007, using always the runtime.

The SQL Server database was placed in another server in the same network.

All was access linked tables to SQL, using always DAO.

My experience showed that we supported over 50 users at the same time, without no problems, when we reached the 100 users we have some performance problems.

BUT we realized that the problem arised when users where using the internal access (ctrl+F) find solution, because we have placed a button with that functionality.

Once we realized that was the problem, we removed that functionality, and created a better find solution, to find by customer, by order, by date, etc,etc, all query based. . .

Then it was all excellent, we also scaled to more than 100 users and all worked great without any problem at all.

Our solution worked without our intervention for more than 6 months, and then one day they called us, with performance problem.

We examined the whole scenario and found that the front end was almost 2 GB of space, (how would that be possible, it was only a 12mb file) so, we compacted and repair and it reduced to 13 mb. . .

So, we created a procedure that at some point during the night the application replaces the old mde/accdr with a new mde/accdr file.

Problem solved.

Some more things:

1) Reboot

All the terminal servers are rebooted nightly. Not just for Access, but to clear any other locks that might have occured.

2) Printing.

We're using Windows Server 2003. The remote desktop session is set to inherit the printers from the PC, so users can the same print options as when they were on thick client.

The printers on the PC are setup using a VB Script on logon, but thats a whole other story...

3) Runtime

Initial set up was in 2007. I had an MSDN sub, which included runtime Access 2003.

As we migrated the users to thin client, it was a major cost saving to just buy 150 versions of office without Access. Even if we didn't have MSDN, the cost saving would have justified the purchase of an MSDN subscription.

If you're using Access 2007, the runtime is free.

@Josh - thats an interesting way to open the frontends. Never heard of that before.

"I recommend using multiple BE files having tables separated by user role to cut down the number of concurrent users in a single BE file."

"That way we can use linked tables to local drives for better performance."

Hi Josh, do you mean you store one backend per local drive or that each local drive has a frontend installed on it?

"Multiple front ends are handled by adding a reference (like a library mde) in vba to the other app."

What's the "other app" in this case and why would you need to add references to it on the fly?

Great feedback! Each of those posts is useful and interesting. Edwin's in particular maybe because it highlights the effect of 'loose' practices and how you discovered the causes, applied the solutions. It's a sure thing that if I expect the application to scale well it'll need to employ a spartan approach to data retrieval.

It'd be interesting to hear of any deploys on TS in the 200 concurrent user range, but from what I see above, there does not seem to be any reason that wouldn't be a viable if done right.

@grovelli,

"I recommend using multiple BE files having tables separated by user role to cut down the number of concurrent users in a single BE file."

By this I mean we have many BE files each having different tables.  Customer data is on one file while sales data is in another and invoicing in a thrid for example.  Depending on what task a user is doing, they may have locks on one BE but not all of them.  Separating the tables into diffent BE files also keeps the file size down and shortens the time it takes to repair and compact all the files each night.  Also cuts down on how many users are inconvenience when a file gets corrupted in production.  (notice I say when and not if)  Even when you repair and compact nightly, there will be periodic corruptions.  I've never had one that wasn't fixed by a repair though.

"That way we can use linked tables to local drives for better performance."

This was in reference to my comment 'We prefer to have Access apps which run on TS only accessible via TS.'  By that I mean once the app is deployed via TS, we don't recommend using the FE on various client machines by linking the the BE on the server share.  Instead, we allow access to the apps via TS only.  Because folks are logging into a server to run the app, the FE files can have linked tables to the BE files which reside on local disk drives of the TS server.  I prefer this because it takes the network out of the equation as far as linked tables.

"Multiple front ends are handled by adding a reference (like a library mde) in vba to the other app."

What's the "other app" in this case and why would you need to add references to it on the fly?

Similar to breaking the data tables out into multiple files according to user role, we break out the UI into modules.  A salesperson might use the customer entry and sales entry modules, but not the invoicing module.  All users open a FE file which is like a switchboard menu that has the UI options they are allowed to use.  When they click on Customer Entry, a new reference is added in code to the cust.mde FE and the Customer menu is opened from a separate mde file.  This happens in the same MSACCESS instance which keeps each user in that 20-40mb RAM range.  Rather than starting up another insatnce of Access.  There is no way for the user to tell a separate file has been opened.  We've been doing this since Access97 and  got the idea from how access does this with the wizard

library files.  The inportance of adding them on the fly is so the module doesn't load if it's not needed.  Like if they don't want to edit customer data, then the cust.mde is not in use.  This helps changes get rolled out quicker to.  If they haven't opened cust module yet, then they don't have to login again the get the changes.  They get the new version the next time they click the customer button.

There are some caveats like docmd and dlookup, but once these are replaced with functions in each FE it works great.

Hope this helps.

Josh