The Scotsman, best known for starring in seven 007 films, won an Oscar and numerous other awards during decades-long career