I, too, love adventures. I've got the entire run of Dungeon, have all the WotC adventures, and all the Necromancer Games adventures too. (I also have a bunch of Goodman's DCCs, but consider them to be only barely mediocre.)
My opinion? Up and down. I think that - overall - the adventures for 3e are very much comparable, if not a little better than past editions (well, 1e). 1e does indeed have tons of gems that are still awesome today (since I have converted them and play them to this day - the whole "nostalgia" comment is reserved for idiots). 2e, OTOH, outside of Dungeon magazine is universally terrible (again, I've converted all those to 3e as well. If it wasn't in Dungeon magazine, it was a painful experience. Dungeon magazine, though, continued to be great all through it's run. (In fact, we're currently going through the 2e Dungeon magazine adventure "Legerdemain" in Dungeon #39. right now.)
However, some of the additions to 3e adventures - notably in Dungeon mag - are really appreciated. That would mainly be the "Adventure Synopsis" section, which is absolutely
wonderful and very much appreciated by me.
I've discovered that, overall, the "delve" format does very little for me. There are too many problems for the benefits the format does offer (and there are some benefits), and I don't think these adventures will be remembered as classics in any way (my totally wild made-up guess). They're too short and limited, which may cause problems. I also think that recent adventures that use WotC's seeming policy of "use only monsters that are currently available as minis" will also result in unfocused adventures that can't hope to gain classic status. Again... wild guesses here.
Really, though, I have no idea what 'the masses' will think are classics - I certainly have no market research available to me (and, to be honest, I don't particularly care what the D&D masses think, either). I can tell you what I think will become classics for me:
- Red Hand of Doom: what you said, above. Epic plot, great maps, and lots of things to do.
- Sunless Citadel: it helps being the first. But it's also a decent size, and has a good map with interesting locations.
- Lost City of Barakus (Necromancer Games): will only be considered a classic by a select group of people, simply because of distribution, and coming out right at the 3.0 to 3.5 switch-over. Big, great maps, and
tons of things to do. Lots of variety and has enough information to still allow DMs to add whatever they want.
- Shackled City: the hardcover is one of the first of its kind - a mega-module going from 1st to 20th
- Age of Worms AP: as above, even though it's serialized in a magazine. Going against
? Really? Cool!
- Savage Tide AP: as above again. Seafaring, pirates, jungle islands, and demons. And Demogorgon. Destined to be a classic.
I think that's it. There's loads of other good adventures out there, and probably more than a few from Dungeon mag may gain "classic" status (I hear Mad God's Key mentioned fairly often, for example).