Think of it this way: Caldwell was a Colonel and he was a career military man. He was an old Colonel. He had yet to make it to Brigadier General. Even after all his great Commanding of the Daedalus through multiple events and battles he has still yet to be recognized as deserving of a promotion to General. Politically speaking, the Daedalus was old NEWS.
Meanwhile, Atlantis was constantly pumping out new discovered advanced alien technology. Politically. that would be a great place for recognition and his promotion. Then we consider that he did not like the manner in which Atlantis, humanity's greatest hope of advancement, was being run and put at what he saw as unnecessary risk. He could take over at Atlantis and have it running smoothly to the point where is was a cushy job full of recognition and promotions where he could ride out his remaining years until retirement.
This is important, because a Colonel (at the time) only made $4000 a month when retired, but: Brigadier General = $4800 a month. Major General = $5,500 a month. Lieutenant General = $5,870 a month. General = $6,650 a month. These retirement estimates are important because in reality a Colonel could not take Active Command of an entire base, only temporary Command. Because a stronger contingent was being sent to Atlantis it would soon become a permanent base instead of an outpost. By becoming the military Commander at Atlantis Caldwell would already be guaranteed the promotion to Brigadier General and easily make Major General in a year.
If things went well, he would pass Lieutenant General and make General before another 2 years. And we now circle back to what he saw as mismanagement of the Atlantis base. Caldwell was a career military man but he saw and described himself as "always being a career military strategist". This means that his greatest drive was to build Command infrastructure and plan for contingencies and outthink the enemy. Atlantis Command position would allow him to do this while also allowing him to utilize the greatest technology and tools the galaxy had ever seen. In short, there was a myriad of reasons why he would want to take Command of Atlantis instead of risking his life on the Daedalus for no recognition and his hands tied and probably dying in a tin can in space.