It really depends on the specifics of where you're going. There isn't one size that is a sudden cutoff for access to areas, since camp sites and roads aren't built out of lego blocks with space in fixed increments. Every inch you add eliminates some sites that you can no longer fit in, but it's a very small portion of the sites for any given inch change. The best suggestion I can make is to check as many of the areas and sorts of areas you want to access and try to determine what would not be too uncomfortable to get there.
Having said that, I suspect a 40' fifth wheel would be quite unwieldy and pretty hard to get into a lot of the areas you're talking about. I also suspect most fifth wheel trailers of that size would typically be set up to be best used in more developed campgrounds, and would not be particularly suited for longer term use without hookups, at least not without a good bit of modification and improvement. There may be (and probably are) exceptions.
Besides length, take a good look at ground clearance and height. More clearance underneath and less required above are quite helpful for getting to out-of-the-way places. Trees along back roads are not frequently trimmed, especially to accommodate tall vehicles.