Night shots with flash are like spitting into a tsunami - it won't turn the tide.
I have gotten wonderful pics with both film and digital. Its not the medium, it's the quality of the camera, combined with the skill and experience of the user.
You can get absolutely stunning photos with a cheap, $4 Fuji dispoable camera, IF you know what situations the camera works best in and play to it's strengths.
Likewise, you can take crappy shots with a $2000 camera, IF you don't know how to use it - digital or film.
A quality camera is certainly preferable over a cheap one. The biggest difference between cheap cameras and better ones is usually the quality of the lens, although of course you do get more bells and whistles on a better camera.
Film cameras have a wider exposure lattitude than digital, but for 90% of the average vacation shots, it doesn't matter - a quality digital will still take great pics. However, a quality digital will cost significantly more than a comparable film camera.
Example: A Canon EOS Elan 7, an excellent consumer 35mm SLR camera, can be found in a kit with a single lens for under $400. Canon's cheapest digital SLR, the Digital Rebel, comes in a kit with a single lens and costs at least $800.
Basically, the answer to your question, "How do people get such wonderful shots?" is two-fold:
1) We spend a lot of time learning photographic basics and familiarizing ourselves with all the features of our equipment, and
2) We got lucky enough to have our cameras handy when we saw something worth taking a shot of.
Pick up a basic photography book, like photography for dummies or something of that nature, skim through it a little, and you'll be surprised how quickly the stuff you learn starts to make sense when you play with your camera. After reading the book, you might find that your current camera won't do all the stuff you want it to do, and you'll have a better idea of what to look for when you go to buy a new camera.