I’ve got a 1999 Mazda 626 that needs a new transmission. It’s going to be a lot of money to repair, but other than the transmission, it’s a good car. I know I’ll probably pay more to fix than it’s worth, but buying a new (used) car is going to cost even more.
I would get a few estimates for replacement. Remember you can always look into a used(more risk) transmission for typically the least expensive route.
Before you go used, I’d do a little research. I’m pretty sure that the 626s of this general vintage were well known for transmission problems. (I think these came out of the Ford/Mazda joint venture - Mazda did the engines/Ford the transmissions. Go figure.)
As such, in this case I’d be hesitant to go with used.