I live in southwestern KY, relatively land-locked like you. I dive locally at a quarry site, Pennyroyal Blue Spring Resort.
It's quite a drive to Dale Hollow Lake, where a group I dive with sometimes dives & I went once & might go again.
I've been to Bonaire 7 times, and done a smattering of dives elsewhere around the Carribean on cruise vacations.
I'm hoping to fly down to Miami or Ft. Lauderdale, rent a car & drive down for a week on Key Largo of the Florida Keys, diving many fairly shallow high-viz. warm water reef sites. This plan factors in family coming along, and the desire to avoid taking an infant outside the U.S.
I also hope to someday hit West Palm Beach & catch some charter boat diving to local areas such as Jupiter, too see sharks & goliath grouper.
Plenty of people do charter boat trips to wrecks off coastal North Carolina to see sand tiger sharks; I'd like to do that someday. A lot of it's pretty deep diving, but within recreational limits, and from what I understand nitrox would offer some bottom time advantages worth having.
Some northerners wreck dive in the Great Lakes, from what I understand, but that is some cold water diving.
My point is, U.S. diving & divers are diverse. A lot of people enjoy local diving, whatever that is, to some extent, but mix it up with some 'cram a bunch of dives in' tropical vacations to places like Bonaire, Cozumel, Roatan, etc...
Richard.