Until the last month I have not owned a car and have rented both zipcars and traditional rentals regularly. Overall, I found that Zipcar was preferable for rentals up to 24 hours, and beyond that a traditional rental was cheaper. You say you won't drive to distant locations, but if you ever do, remember that Zipcar charges a hefty fee per mile over the 150 or so they give you per day.
Zipcar previously only insured up to the state minimum, and nobody really noticed for a while. This is probably the era your horror stories come from. The $300,000 liability coverage they provide today is much more reasonable, though someone with a high net worth may want additional protection.
I haven't had the misfortune of making a damage claim to a credit card, but this is what I do know. The card provides "secondary" insurance, meaning they do not get involved until all other insurances (your own, the rental company's, etc.) have paid out. They generally have significant deductibles ($1,000 maybe) and cap coverage at $25,000 or $30,000. Rental companies (and, I would assume Zipcar, now that they are owned by Avis) frequently try to claim "loss of use" damages, meaning the money they could have made by renting the car you wrecked while it's in the shop. The card companies generally either explicitly state they don't cover these fees, or deny liability for them after the fact. I suspect in the case of Zipcar, because the $750 damage fee is probably less than the credit card's deductible, the card's coverage never comes into play.
If you do an occasional traditional rental, keep in mind that you no longer have liability coverage under a personal insurance, and those companies only cover to the legal minimum unless you buy their supplemental liability policies. I always took supplemental liability coverage when I rented as a non-car-owner.
The auto insurance companies also have "non-owner" policies, but those are only worthwhile if you rent a lot (I found I would break even at 30 rental days per year).