I don't think there's that much of a correlation between how good a film adaptation of a book is, and how closely it sticks to the material of the original book.
IMO it all comes down to the identity of the film in its own right, and whether the director has an original and persuasive vision for the film.
Stephen King reportedly hates Stanley Kubrick's adaptation of The Shining because he attributes motivations to characters that weren't there in the book, and changes the story too much. But by god The Shining is a great film.
There are films where the characters are nothing like I imagined when I read and loved the book, and bits of the story are changed, but that still manage to win me over. Ang Lee's Sense and Sensibility and Stephen Spielberg's The Color Purple both spring to mind. Also the first Bridget Jones film. The directors seem to have a clear vision of what they want the film to be, and they create something new and fresh that is good and enjoyable in a different way to the source material.
Other adaptations may or may not stick faithfully to the story of the book, but still be boring with nothing new to add. Like the second Bridget Jones film - it gave me nothing good, it was just a repeat of the formula of the first film using material from the second book, but without the sly directorial humour that made the first one so good. The adaptation of the Time Traveller's Wife was also both rubbish as a film AND not very good at dealing with the source material.
I'd put LOTR and The Hunger Games in a category where there's obviously been a lot of money and care invested in the films, and the result is neither bad nor good, just respectable and watchable. It's always novel seeing books you've read and enjoyed acted out on screen, but I still prefer the books and don't have much reason to rewatch the film instead of reread the book.