Warning: Math intensive
njmac said:
Is it a certain look that you can't get from a digital camera or will it make really huge prints that a 8 to 16 mp camera can't take?
The info about medium and large format cameras that I have found seem a little outdated.
Can you get the same results from today's super megapixel cameras?
Setting aside intangibles and looking purely at technology of image resolution potential, IMO, the short answer for digital vs film is probably still: "vs 35mm? Yes. vs Med/Large? No.
The longer answer:
On the digital side, there's physics limitations to how small you can shrink the CMOS sensor, since every "pixel" has overhead of circuitry that can't be removed from the sensor's face. You can shrink the "photon antenna", but not really this circuitry, so the net result is that as you go smaller, the percentage of your sensor's surface that's actually gathering photons goes down (a bad thing).
On the film side, our resolution limit is similar, and is called "grain" and/or "grain size". One common element of the trade-off is that as your ISO speed goes up, so too does your grain size (which is not good).
To get more resolution, the general solution for both is to go to a bigger physical sensor - - medium format, etc.
On digital, this can (and has) been done, but chip yields tend to go down the larger the chip is, so this gets very expensive very quickly. For example, Hasselblad is advertising a new camera, the H2D, which is a medium format 22 Megapixel SLR that's going to sell for a mere $27K.
On film, we call it Medium and/or Large format. Popular examples are 645 (from 120 film), 4"x5", etc.
That's the "input data" side.
Now lets look for a moment at our "Output print":
Insofar as print quality, it always, always, always comes down to how much data you have in your original, and how large you can enlarge it at whatever your minimum acceptable quality level is.
Anyone can take a little bit of data and somehow enlarge it - it just will get blocky. You can even create images with 10ft x 10ft "pixels" in Black & White:
http://www.un.org/av/photo/ga/images/unf.jpg
If you don't want to see grain or pixelation, the value that's generally claimed as adequate for a "sharp" print is 200-300 dpi.
If we do the math on a 4:3 ratio image to see how much data we need for a "sharp" 40" x 30" print, we can determine that we would need to have (40)(300dpi)*(30)(300dpi) = 144 x 10^6 pixels of information, or 144 Megapixels.
Even if we back off to only 200dpi, we're still talking about (40)(200)(30)(200) = 48MP being minimally required.
As such, the short answer to '...can today's super megapixel cameras make a good 40"x30"?', if your criteria for adequate sharpness is 200dpi or higher, the answer is No.
But can film?
Well, the general "Megapixel equivalent" (MPe) values that get thrown around (and argued about!) for 35mm film tend to be around 24-48 MPe on the higher end...where "higher end" means those values that are tending to give maximum credit to the resolution potential of film.
What this suggests is that a 40" x 30" print is probably the maximum acceptable 'sharp' enlargement for a 35mm original, all other factors being equal (such as best glass quality, tripod, etc).
Caveat: there's a lot of other factors that get thrown in here that I'm purposefully glossing over, such as "grain clumping", and the pragmatic ISO value needed to work with a particular subject, since the highest MPe values generally require very, very low ISO film - - you don't find many people out there taking landscapes with ISO 25 film these days, in no small part because its hard to find ISO 25 film these days (although I still personally have two bricks in my 'fridge 
).
Where medium/large format comes in is that it allows you to have larger sensors - - be it a digital sensor or a chemical (silver) sensor.
For a simple 120 film "645" medium format camera, it has a 60mm by 45mm negative, which versus the 35mm x 24mm negative from 35mm is 2700 mm^2 versus 840mm^2, or a bit more than 3 times (~3.3x) the area.
As such, if we *claim* that we can get 48MPe from a scan of a 35mm, a scan of a 120 negative would be expected to give us around 150MPe. if we go back to our 40" x 30" print, that's theoretically enough data to let us jack up the sharpness from 200dpi to 300dpi.
When we move up to large format, a 4" x 5" original is ~12,900mm^2, or ~15x the area of 35mm. As such, ath the 48MPe for 35mm benchmark, we have 720MPe, and at 24MP, we would have 360MPe. For the latter, at a 300dpi print, this would suggest a practical max print size of ~71" x ~56".
If we take a 71" x 56" print and go back to only 16MP of data, it suggests that the image would have to be printed at 63dpi...each pixel is roughly 1/64th of an inch tall and wide, which is slightly worse than what we used to get from an ancient 9 pin dot matrix printer (72dpi vertical).
Going to the extremes, the current state of the art is a 4 Gigapixel equivalent camera. Its a film camera that uses a huge negative that is then drum-scanned:
http://www.gigapxl.org/
For an illustration of the resolution each image holds, you can check out the November 2005 issue of Popular Science, or this page:
http://www.gigapxl.org/gallery-Parasail.htm
-hh
