I've developed using the Jet engine and SQL Server from Access an Access/ASP front end. You need to determine what your requirements are before you pick ANY database engine. Never say Never because each was developed to satisfy different needs for different customers. There are significant advantages to using Access/Jet and for using SQL Server, then each has reasons you may not want to choose them. Some critical criteria include;
- Size of completed database (mentioned)
- Number of concurrent users (mentioned)
- Configuration (direct access vs network)
- COST (this is pretty big if you are not a corporation with resources to plunk out for a Database Server, Server licenses, SQL Server, etc. AND have a small application)
- Security (SQL Server has significantly improved security BUT the real question is, is it needed)
- Application (Is it a legacy system to be converted OR new system. Some legacy systems may be easier to rebuild from scratch with a new design)
- Etc, Etc
I do strongly recommend that if there is any possibility that the database will grow or require any of the features Jet isn't designed to satisfy then you develop the application in SQL Server. It is not fun or simple to upgrade an Access/Jet database to Access/SQL Server or Anything/SQL Server. The upgrade path may require complete redesign to take advantage of the features in SQL Server.
Please consider this as Steve's opinion. You should establish your own criteria and make your own choices. I will say that I have significant experience in Access/Jet with larger applications and a fair amount of experience converting some of those legacy applications over the past year.