I share your fustration. I have an original MacBook Pro, first generation Intel with exactly the same problem. If I am a few feet from the access point/router (a genuine Time Capsule) then the MacBook Pro will connect 3 out of 4 times. If 30' away (once connected System Profiler reports "Signal / Noise: -73 dBm / -96 dBm") its 1 in 20, all if SSID is disabled. But with SSID enabled its 100% connect. Always stays connected just fine once its connected.
Same results over the years with an old US Robotics WiFi router. Same results with a WRT54GL. Same with an E4200. Only common element is the MacBook Pro.
So earlier this year I purchase the latest MacBook Pro (March 2011?) and it works lickety-split with or without SSID 100% of the time.
Think there is something amis with hardware or software on older Apple AirPort/WiFi implementations.