Portland OR light rail access to the airport is a rare example of transit access that works. Seattle is another one, sort of, and Baltimore. BART works (it's in terminal area) but has an airport surcharge on fares there. DC of course has a stop at National. Chicago has a line to O'Hare from downtown.

My impression, though, is that transit to the airport, still, is more for airport workers than travelers. For day trippers to someplace with minimal luggage, maybe, it would work if it's going to your local destination without transfers, or coming from ditto. With a rolling carryon, less practical (especially for cars where you have to climb steps rather than low-floor or high-platform) but still possible. But with luggage for a week away, no.

My good data point: LA. I can fly in to Burbank and catch Metrolink or Amtrak just off the airport (easy walk; no extra airport fare). If schedules align, I can be off the plane (with carryon or on a day trip), on the train, and downtown in 1/2 hour (i.e. about the amount of time needed to shuttle over to car rental, stand in line, and acquire wheels); helps that Burbank is a small airport due to its age. Once at Union Station, transfer to Red Line or shuttles for downtown destinations or Gold Line are easy and don't require stair climbing.

Then the bad data point: Sacramento light rail. No airport service yet (some day ... but it'll be at least an hour's ride from downtown with many stops for what takes 20 minutes on a Yolobus now...), but it does serve the Amtrak station. I've used it for Amtrak runs on one-day trips (just a briefcase), and it's OK for that. I do see people trying to use light rail with luggage - not pretty. The trains are old, high-level with steps (no high-level platforms except for handicapped, and luggage-dragging doesn't count as handicapped); it's a pain to get luggage inside that can't be hand-carried, in one hand. In my observation of other airports I've been through in the US, more transit connections are via bus or something like Sacramento's light rail than otherwise, and even if you have high platforms for easier access there's still no place to put luggage while you're on the train or bus (except in San Diego's airport expresses) since carrying airport travelers is not what they're designed for.

For those familiar with BART, consider trying to get to or from the airport with a week's worth of luggage (a couple of big roller bags and hand baggage/backpack) at rush hour. If you're not attacked and have your stuff stolen, you'd probably be verbally (at least) harasses for taking up seat or standing space with all that junk. Much the same elsewhere.

Bottom line: airport transit in the US is great for a few of the airport workers (most will drive to work anyway), but its usefulness for air travelers is limited and requires fairly intimate knowledge of local transit conditions and services. If I still lived in SF, I would never suggest to my German relatives that they should come to SFO and take BART and Muni to my neighborhood (I had a bus within a block, that connected to the Metro, that connected to BART). I would simply drive to the airport and pick them up, or have them rent a car if arriving outside commute periods. Even more so now that I live farther away - adding intercity transfers to the mix is a total loser.