X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Concurrent Users, Access 2010    
post Sep 24 2017, 03:53 PM

Posts: 592
Joined: 11-January 09
From: UK

This is probably similar to asking 'how long is a piece of string' - but any advice would be appreciated.

Current setup: standard FE (user's C:Drive) & BE on network folder. Roughly 20 tables (7-8 main tables) - many of the other tables are 'lookup tables'. One of the main tables has roughly 1,3k rows (and unlikely to increase more than 100 over 5 yr period). All other tables are roughly 100 rows with likelihood of this growing to roughly 5-15k rows ea.

The number of concurrent users the application should be able to handle is roughly 30-35 users (I know Microsoft say 255 concurrent users - seems quite high...).

Is Access 2010 capable/ recommended to deal with this setup? If not, what other alternatives could one use - is Access 2016 any better than 2010 to handle it (or no difference at all) - considering I'd need to retain the FE in Access?

Go to the top of the page
post Sep 24 2017, 08:23 PM

UtterAccess VIP
Posts: 2,691
Joined: 12-April 07
From: Edmonton, Alberta Canada

I certainly seen quite a few setups with that number of users.
I do think that when you reach 30 users, it is “a good time” to adopt SQL server (or at least start preparing for such). With 30+ users, then things start to favor SQL server (and the company likely has at that point more than one server hanging around that store files etc.).

You can feed SQL server some really “messy” SQL with all kinds of “messy” joins etc., and the performance is nothing short of amazing. So the result is “more” leeway in less than perfect designed applications. Hardware and computers are low cost, but human and developer time is still VERY high cost.

So you can really achieve some real nice performance gains by adopting SQL server, and do so with “less” concern of performance issues. Of course to gain such performance you do need some experience with SQL server, but once that skill is obtained, then performance gains and keeping good performance occurs will LESS developer efforts.

And more often an issue of reliability vs that of performance makes choosing SQL for the back end is what seals this deal.

Given that the “express” edition of SQL server is free, then I tend to suggest that after 20 users, then adopting SQL server for the back end is a prudent choice.

This is not some “all” or “nothing” choice, nor a “must” do choice.

With a stable Access application, 30-35 users is rather do-able, but with such a number of users, then it likely time to consider SQL server.

I think anything approaching 50 users is certainly a good metric and time to adopt SQL server.

And no, recent versions of Access really don’t perform better or worse in regards to Access + a file share back end.

So you can “get” away with 30-35 users, and it really depends on the application. However, that number of users in my experience is quite much the time to consider a real database server for the back end.

Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
post Sep 24 2017, 11:13 PM

UA Admin
Posts: 33,749
Joined: 20-June 02
From: Newcastle, WA

The only thing I would add to Albert's overview is that I've found good interface design is often more important with a SQL Server back end than with an Access back end. It's true, of course, that the SQL database engine can run circles around Access, but having to move data back and forth between a server and a client requires much more thoughtful interface design.

Many years ago, I helped a small company run their application on Access/SQL Server Express for many years. We ended up with over a million records combined in the tables in that database, no problem. However, we had to modify the way the forms worked in order to optimize network traffic to support the workload.

As another small example, on a different project on which I'm working this week, I had created a complicated query in Access as the recordsource for a form. It was taking up to 10 seconds to load that form. Moving the query to a SQL Server stored procedure dropped load time for that form to one or two seconds. That's the kind of thing to which Albert alludes, I believe. And I doubt that the actual SQL was that much better written smirk.gif Also, because the stored procedure means it is now read-only, I also had to change the approach to add/update/delete for that form, though.

I've come to the conclusion that applications supporting as few as a half dozen users can often be moved profitably to SS Express.
Go to the top of the page
post Sep 25 2017, 12:36 AM

Posts: 867
Joined: 26-January 14
From: London, UK

I would agree with the advice of Albert and George that SQL Server (or some other SQL DBMS) seems like the right choice. Among many other things you will benefit from superior transaction management and backup/recovery features.

Moving a ready-built Access database to SQL Server involves a certain amount of work: refactoring certain bits of code and typically redesigning the database as well. I believe it's a mistake to start off with an Jet/Ace database with the intention of upgrading later. If you ultimately intend to use a SQL Server database then it is much less effort to start off with SQL Server as soon as possible. The tools provided with SQL Server can make you more productive during development anyway. Access works fine as a front end to a SQL DBMS, so use Access for what it is best at and use SQL for what it is best at. That would be my advice.
Go to the top of the page
post Sep 25 2017, 03:17 AM

Posts: 592
Joined: 11-January 09
From: UK

Thanks All - great feedback. What about pushing the BE to SharePoint as lists?
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    16th October 2018 - 06:05 PM