MS Access Database Application
MS Access applications are suitable for small groups e.g. 2-10 users over a LAN, or 2-5 users over the Internet. If you push much beyond this, you'll more than likely start running into problems, especially if you have a relatively complex set of products, services, and business processes (all result in an increasingly complex database schema), and particularly if your business needs require more advanced types of Server-to-Client functionality, like database replication & synchronization.

For some of our MS Access customers, business is relatively small and static, and the goal is to "Make the MS Access Problems Go Away" in the most cost-effective manner possible. For others customers, business is continually evolving, the application is a moving target, and it's critical that the solution support current business needs, yet provide a stable path to accommodate growth. Regardless of your business situation, it's important to understand the strengths and potential trade-offs of the various solutions available on the Microsoft Business Platform.

Practical Differences -- MS Access, MSDE, MS SQL Server
There are important differences between what you can achieve with the various database Servers and Clients available on the Microsoft Business Platform. Principal considerations for deciding which combination is the best for your business (lowest hassle, biggest benefit for the investment) are the following:

Mission Criticality of the Business Application
Location and End Users (LAN vs. Internet)
Frequency of Use (Moderate vs. Heavy)
Number of End Users (5 users vs. Enterprise-wide deployment)
Business Product/Process Complexity (and associated Database Complexity)
Static vs. Dynamic Content (Read vs. Read-Write)
Audit ability, Industry Compliance (e.g. SarbOx, HIPPA)

MS Access Application (Client) with MSDE Database (Server)

If you've already Hit the Wall with your MS Access Application, or you need a Custom MS Access application designed & developed from scratch, the MSDE database engine is the next upgrade on the Microsoft server path. If Internet access is a requirement, SQL Server is your only option. They are the same identical database engine. Provided that your database is designed properly migrating to MSDE or SQL Server will have a make the problems mentioned above go away.

MSDE also has its own costs and technical limitations. For example, if your database is exposed to the Internet thru anything other than MS Access or Active Server Pages (.ASP), you cannot use MSDE, and must purchase (or already own) a SQL Server License from Microsoft. This is purely a Microsoft Licensing issue -- MSDE and MS SQL Server are the same database engine; the only difference is, MS SQL Server supports unlimited users on a LAN or the Internet, with no performance or licensing restrictions. In addition, MSDE has a built-in "performance governor" on Process Queues that limit the number of simultaneous End Users to 8 concurrent (active) sessions. MSDE is also slightly more difficult to develop custom business applications on, and more difficult/expensive to install and configure vs. MS SQL Server. If supporting two dozen or more end users over the Internet is a hard requirement, then MS SQL Server is the right choice.

The Real Cost of Staying with MS Access
Many people think that developing applications in MS Access is less expensive than alternatives e.g. MSDE or MS SQL Server. Our experience at EWAY SOLUTIONS TAMPA FLORIDA, from developing literally hundreds of custom business applications on MS Access, MSDE and MS SQL Server, speaks to the contrary: developing custom applications with MS Access CAN BE SIGNIFICANTLY MORE EXPENSIVE than developing business applications with MSDE or MS SQL Server. We tend to spend more time, more effort, and run into more headaches with MS Access, primarily because it's difficult to know what the Customer is running, easy-to-use MS Desktop-like User Interfaces conventions are more difficult to construct in MS Access, and MS Access is significantly more difficult to deploy and control as a distributed business application without driving up IT support costs. Finally, even if you've invested the extra time and effort to overcome these limitations, the MS Access platform won't scale.
EWAY SOLUTIONS TAMPA FLORIDA developers probably spend around one-third less time developing custom applications on the MSDE and MS SQL Server platforms. The results are far better, and the application is more scalable (has a longer shelf-life). Since time is money, applications that are built on the right components end up costing our customers LESS, and they get MORE! Our advise to customers: stop looking at MS Access as the lowest cost/cheapest solution, just because it ships with every copy of Windows Professional. It simply isn't true.

Making the Appropriate Decision
Any wrong turn with these "application design" considerations can (and will!) produce results that are both painful and expensive, and likely fall short of meeting your business objectives. All too frequently, customers call EWAY SOLUTIONS TAMPA FLORIDA because they built their application in a piece-meal fashion on the wrong database, and they are now experiencing lost data, slow performance, errors, crashed and corrupt databases.
As far as distributed Internet business applications are concerned, there would never be a reason to build your back-end database with MSDE, and always the reason to use MS Access for a few users on the Internet, or MS SQL Server for 10 or more on the Internet. With a few users, you can get away with an MS Access Database, provided your schema is simple and you don't expect your database to grow larger than 1.5 GB. When you need to support multiple users over the Internet is when you get serious about MS SQL Server.