Please, give me the most serious arguments against this. Application directly opens a connection to ms sql server, directly executes queries. So what I'd like to ask:
1) Why it is wrong when the number of users can be up to 1000 executing huge queries?
2) What serious problems can that cause?
3) What should I do?:)
Arguments, the most serious arguments against this kind of implementation!
What about:
One of the things to consider is how the queries are done. 1000 queries against a SQL Server DB might be manageable, but 1000 Access queries in which the table is locked, or which are actually joins or views, could use dramatically more memory. It really depends on how the application is written. Some Access apps open a recordset and page through the records one at a time, or fetch a few dozen and work on those, but sometimes Access grabs the whole recordset, for example to allow users to page through data. And I have seen Access lock a set of tables to allow editing of them. That would be bad in your scenario.
Of course, I wholeheartedly agree with the "10 years out of support" issue. That is a guaranteed problem. Mine is only a possibility. And you should probably update SQL Server to a current version also, for the same reason.
Access 2003 or 2007 would be just fine for the scenario as long as you had an Access developer who was up to speed on how to develop for client/server with large user populations.
Access 97 is still an awfully nice version of Access. I think it's the best version ever produced.
But it is out of support and predates the alteration of default permissions in Windows implemented with the release of Windows 2000. This means that it has some problems in installing with its default permissions (it expects write access to its application folders and registry keys). An installation script can easily alter these appropriately, but you're still left with problems in certain contexts, like trying to run it in Windows Terminal Server/Citrix, where it very often just completely breaks.
I would like to hear an explanation of exactly why someone would choose A97 for new development. Of course, I may be misinterpreting. You may be asking about an existing app, in which case I'd go with "if it ain't broke, don't fix it," and then ask exactly what it is that is perceived as "broken." Those things can be fixed, though it's unlikely that simply upgrading from A97 to something more recent is going to do the job.
I’m currently nearly finished with a brand new application written in access 97 that stores its data in SQL server 2008. As has been said many times before the access/SQL server combination really works great.
Inline with my other applications it is completely unbound using ADO to get the data from the server. I wont drag up that debate again here but it is something you really want to look into as it can offer some great benefits.
Most of the SQL server guide you will find will ask you to check that you have the correct indexes and try to identify the slowest running parts of the system or the ones that get called a lot and then look at making them faster. That might cause you to make a covering index or to denormalise the data in someway.
Generally what is good practice for JET also works well for SQL server, make a good table schema with a good clustered index choice and good supporting indexes and you are 95% of the way there