[Little Writer]

"Myst" certainly looks great, but I couldn't play it. But I liked the way it looked so much I even got "Riven", which looked even better but of which I understood even less. I finished both games with walkthroughs in hand, just to see everything there's to see, but I couldn't tell you anything about the content or plot of those games.

I find there are many games that look great, at least when it comes to the environment. But usually the addition of characters causes an unnatural perspective, as if they've been obviously photoshopped onto the screen, only by using Paint instead of the actual Photoshop. Many times it's obvious that the artists working on the backgrounds are different people and/or use different tools than the artists working on the characters.

Of course characters need motion while backgrounds usually stay static, so that adds to the problem. Yet then there are games where the combination is a lot more fluent and hardly perceptible, so I guess it's mostly down to budget and available design talent.

[Twizman]

I haven't played it, but Riven. Looks really mysterious and beautiful...

[Jadefalcon]

Both the Tunguska games are quite nice looking for photo realism in my opinion.

[smulan]

If by "modern" you mean an adventure that is as modern as about from yesterday the answer is simple: Machinarium.