I'm firmly of the belief that it's almost impossible for film to be as good as a really great piece of literature -- part of what makes it great lit is the fact that the novel (or short story) is its ideal genre. However some of my favourite movies have been made from mediocre books -- books that have a great story, but aren't terribly well written. For others it's because the film does a better job of letting you see & hear things (like action scenes or music). For still others, it's just really good acting that bring the stories & characters alive better than the prose could.
Some that I can think of off the top of my head:
~ The Milagro Beanfield War -- one of my fave movies ever, but a pretty meh book
~ Ordinary People -- ditto
~ The da Vinci Code -- not the best movie ever but still miles better than the book
~ The Name of the Rose -- better than the book just because the translation was so turgid. (that and Sean Connery )
~ The Girl with the Dragon Tattoo etc. -- these ones are close, but I think the extraneous details & "research" -- plus all the Swedish political references -- in the book give the film a slight edge here. (Haven't seen Hornet's Nest yet.)
~ The Committments -- nothing wrong with the book, but it's about music for hank's sake, so obviously a film is a better genre
~ The Constant Gardener -- good book, but excellent movie
~ Primary Colors -- ditto
~ The Emperor's Club
~ Fried Green Tomatoes
~ Paper Moon
There are few of movies that I think tied their books for excellence, or at least came very close. One that leaps to mind is To Kill a Mockingbird -- thanks in part to the superb acting. And The Shawshank Redemption. The movies made from Nick Hornby's books have been about equal their novels too, like High Fidelity and About a Boy.
Of course I'm sure there are other movies I've loved based on books I never read ...