I want to say something like adventure is in the eye of the beholder.
If adventure for you is mainly about how far off the beaten track your campsite is located, then a bigger camper/truck combo is going to limit your options. If adventure for you is more about what you can see and do from your camping location, then there are a wide possibility of adventures that can be had using a fairly large truck camper.
We have a Bigfoot C25-10.6 that we carry with a '99 F350. It's a big rig, and we don't take it on anything rougher than reasonably well maintained dirt roads. Most of the time we are actually in smaller campgrounds, but we have a tendency to use it off-season when cold weather keeps the crowds away. Obviously, this is also a time when the comforts of the camper are valuable.
Here's a picture of our campsite at Natural Bridges National Monument from a trip in 2007:
We were there for Thanksgiving week. Thanksgiving night as we sat around the campfire it began to snow. This was the scene we woke up to on Friday morning. The campground there is small - just 13 spaces I think. During the entire week only one other campsite was occupied. He was a solo camper, so we invited him over to have drinks and share our campfire one evening, ended up hiking with him a couple times during the week, and invited him over for Thanksgiving dinner.
During the week we were there we explored the Cedar Mesa area taking day hikes down into many of the local canyons looking for ancient ruins and rock art. We visited the Big Man Panel, a famous Barrier Canyon Style art panel. We had the canyon to ourselves that day save for the fresh mountain lion tracks.
Here is Fallen Roof Ruin, just one of many that we saw that week.
We have enjoyed many trips like this with this camper, using it as a base camp in not too out of the way campsites that give us access to other places to explore. Not having to tear down camp each day gives us more time to hike and explore. The camper allows us to camp in the off season with comfort.
We have other camping options for times when we want to camp way off the beaten track, but there is no limit to the adventure you can have with a larger rig like this.