The Oscar-winning actor will always be associated with his smooth, Scottish-accented portrayal of the suave spy