Let's take a look at SQL Server Express (SSE). First, I like the name change. SSE is somewhat self-explanatory. It is simlar to Outlook and Outlook Express in that both are e-mail clients, but Outlook also does a bunch of other things and OE has a newsreader. MSDE seemed to always have the phrase "SQL Server Lite" or some other modifier when speaking about it to others unfamiliar with it.
SSE has limitations built in because it is really targeted at moving Access applications to a different Storage Engine. if you look at the specs for Access 2003, SSE is closer to thos specs, but adds a little more bells and whistles from a management point of view.
I hope that there is still the Access Project (ADP) files that can act as a front end to SSE. I have not tried it out yet with Express, but Access 2003 does work as a front-end for SS2k5b2 from just a quick connect test.
In a recent newsgroup post, it appears that ADPs are not being pushed as much as they have been in the past few years. This probably has a lot to do with all of the BIG changes in Yukon.