http://en.wikipedia.org/wiki/Castle_Amber_..._%26_Dragons%29
I recently read the Wikipedia article on Castle Amber, a classic D&D module. It made me feel very nostalgic as it's one of my all time favorites. I actually acquired a copy back when I was in 4th grade and I was able to buy the 1st edition blue cover rule book and a few modules including classics like Palace of the Silver Princess and The Keep On The Borderlands at a garage sale. At the time, as a 4th grader, I often tried to play D&D with this one friend I had but I think our grasp of the rules could be a little weak at times...
Anyway, though, I always liked Castle Amber the best back when I was little, and looking back on it today. When I was a little kid I felt that Castle Amber had the most colorful setting and the creepiest perils.
I loved the magical banquet table where the characters could eat various items from a French banquet and experience potentially very powerful affects on their stats. For example, eating the salad would actually switcheroo your stats around, which could be quite horrendous if you were a fighter who, say, got DEX and STR swapped! Eating the bread IIRC could result in your character either not needing to eat, or needing to forever more eat twice as much. The wine cured HP (after all it's French wine), but the brandy forced you to do save vs. death ray or die. There was some mushroom sauce where you needed to save vs. poison or die, but if you made the save with flying colors you got permanent bonuses to future save vs. poison rolls, IIRC. The roast beef was safe and normal and tasty. (Phew!) At the time I probably didn't fully appreciate how statistically devastating eating at the banquet could be, but the general concept of the ghost's banquet with very scary food was a compelling one and it really stuck in my head. Looking back at it now that I'm older, the banquet table is so bastardly that I love it for that reason.
The events which the characters might find themselves in were pretty imaginative too and were in a vivid medieval French setting. For example, one possible situation was battling a huge magical giant that would attack a walled city. The trick was that the magician controlling him was in a little basket n the giant's back, so IIRC while it was very hard to take down the giant you could try and take out the magician instead. The whole enterprise was moreso doable if you had a Fly spell, but in the event that you didn't you could try to take the giant down from the spire of a cathederal, which while dramatic was also really likely to get you pwnt. Nevertheless, what a colorful scenario that was!
There were a lot of references to situations from kind of pulp fantasy fiction literature of ages past. For example, in one part, there was a noblewoman who had been buried alive due to a mistake. She was still alive, though, and she would eventually escape thoroughly insane from her ordeal and attack a NPC, who in turn was like a character from an Edgar Allen Poe story. That's classic Weird Tales type drama. I really like retro-styled things so to have that in a D&D module would make me really happy. In fact, the whole setting was based on stories by Clark Ashton Smith.
Basically, I really like more old-fashioned fantasy fiction than contemporary fantasy fiction, and I feel like Castle Amber manages to really keep the spirit of these sorts of stories. I feel like you won't get that sort of atmosphere in most RPG modules you see today which often are more influenced by modern fantasy fiction.