How good does acupuncture work for pain?

It depends. For certain painful conditions acupuncture can be very helpful; major surgery has been performed using acupuncture as the only anesthetic! but every person/situation is unique, so the only way to see how it works for you is to try it. And, or course, there are different styles/techniques of acupuncture, so some acupuncturists may help you more than others.
Type of pain. Acupuncture has been used successfully to treat a variety of types of pain. The degree of success depends on if the condition causing the pain is one that responds to acupuncture.