This is one of the most hotly debated topics around so expect quite a few opinions. My TechEd talk (which might be posted online on the Microsoft site sometime in the near future) discusses where SQL Server Express Edition fits in relation to JET/Access and the newest DBMS system SQL Server Everywhere (SQL Ev). Yes, there are a lot of choices and each has its place.
A number of factors should be considered when you're trying to decide which approach to take. These include (but are not limited to):