Web front-end Vs Access front-end?

[VegaLA]

Hi all,
I have a question regarding pros and cons of using Access or Web front-end.
We are currently creating a DB which will be upsized to SQL server 2000 for our Dept and another Dept to share/use/update data to. We planned on developing the front end in Access 2003 as it will be quicker to design/create and there will only be 15 users writing/reading to the SQL server via ODBC but not all at the same time. The other Dept would prefer the front end to be Web based stating that Access would tie up the production server with 15 users accessing data at the same time wslowing down other DataBases/processes on that SQL Server. Would using Access as a front end, running from client machines locally really put a lot of strain on a SQL server, even if it was just 5 users connecting at the same time ?
How would using a Web based GUI ease strain 'talking' to a SQL server?

Any help, advice or Pros and Cons would greatly assist me.

Thanks in advance,
Mitch....

 

[tehNellie]

The biggest con to using Access as a front end, especially as an .mdb/e, is that it really expects to be the database and hence that you'll have linked tables/views at the very least. You can design your forms on the principle of using ADO[BC] to call stored procedures, populate forms from recordsets and so on but in many respects that kind of defeats the object of using Access as your front end to begin with.

A poorly designed Access front end using linked tables can cause problems via Locks, but if it's written properly then there really shouldn't be that much of an issue with 15 users even if your SQL "server" is actually an underpowered workstation.

As a quick fix, especially as you're upsizing an existing database Access can give you a working application with minimal effort [while you develop in parallel your new SQL server specific application] but by default, access only transfers your tables to SQL server, all your queries etc will still be run locally in Access and on that basis the department wanting you to create a web application might have a point. Not that a poorly written web application can't totally tank your db performance just as well as an Access application can.

 

[pbaldy]

I agree with tehNellie but for one small point. Access (Jet) is pretty good at having the server process a query if it can. Unless a query has a function or syntax not known to SQL Server, the server will process it and only send back the resulting records.

IT guys don't like Access (and I wear both hats) because of a history of poorly designed applications. A well designed application will not load the server any more than a web app would. Here are some thoughts on the matter:

http://www.sql-server-performance.com/tips/access_sql_p1.aspx