@The Captain George Lazenby bluffed his way into that role. He had done modelling, but had no previous acting experience at all - the audition came via a friend of a friend of Barbra Broccoli who'd seen him at a party. The story goes that after he was offered the job, he rang an actor friend in a panic, saying "But I can't act!". The friend replied along the lines of: "You stood in a room with Salzman and Broccoli, two of the biggest sharks in Hollywood, told them you were an experienced actor.. and they believed you. And you're telling ME now you can't act??"
Lazenby was right, though. His difficulty with screen acting was a major factor in delaying the shoot (it didn't help that Connery was a consummate professional: unusually efficient, and always well prepared). But he was a quick study, and you can almost figure out the shooting order by seeing his performance get less wooden as filming progressed.
Unfortunately, Lazenby's frustration at being out of his depth came out as a difficult, argumentative relationship with the crew, and that, combined with how much he cost in retakes, meant he was never going to get another film.
But yes, it is still the best Bond film, not least because of the ending. Best score definitely, and one of the best songs. I think No Time To Die foreshadowed the tone of it's own ending by quoting the music of OHMSS in several places...
As for who's next, I'm not fussed, but I would like them to reset the series, back to the 1950s of the books, at the height of the Cold War paranoia. Bond got silly when the character became a relic of a bygone age; put him back in his home era, and they could do something really good with it.