APA accreditation is only for PhD/PsyD programs in Clinical or Counseling Psychology (and School Psychology to some extent), as those are the ones that lead to clinical licensure. I don't know about all the schools you've mentioned, but for instance, Princeton does not have a Clinical or Counseling Psychology PhD program. Their psychology PhDs are strictly research focused. A Clinical or Counseling Psychology program has specific courses related to clinical practice and practicum experiences interwoven into the curriculum, which research only PhDs (like Social or Developmental) do not.
Are you applying directly to PhD programs or do you want to do an MSW then PhD? Are you looking to teach/research or do you want to also directly treat?
I would identify programs that have SUD sub-concentrations or certificates and/or have an affiliation with an interdisciplinary institute focused on SUDs and addiction.
Off hand, Michigan, Tulane, NYU (SARET) Penn, USC (IAS).... they have sub-concentrations or institutes specifically related to SUDs. Penn’s MSW program was just awarded a huge grant from SAMHSA.
Whether this is the right field - I would say yes, 100%. Social Workers not only frequently treat SUDs, they’re often on the front lines working with those disproportionately impacted by the “war on drugs.”