There are still sites out there for RB3D
The easiest Mod to use for both SP and MP is Hells Angels http://www.hellsangels.firstflare.com/
Another site for mods, but hard to easily find what you are looking for is: http://www.flybert.net
This site however is chock full of goodies, just requires hunting. I would really recommend the "Capt. Darwin's 2xFlameOut " for some spectacular flaming aircraft.
To get the best look, you need Glide graphics, and the best way to do that is with a wrapper. I have a few hosted here: https://skydrive.live.com/?cid=E196374A237958B4&id=E196374A237958B4!248
I would suggest trying CJglide2x_02 first. It is the easiest one to use (a single file) with everything preset for very good quality. But since you have ATI card, I would say, use the Open Glide one if CJglide2x_02 doesn't work for you, before trying the DGVooDoos. The DGVoodoo ones are supposedly more atuned for NVIDA cards where Open Glide was designed more for ATI cards, or so i have read.
While Hells Angels offers the OTE terrain (Over the Edge, if I remember right), I prefer Promise Land 2, which i also have hosted at: https://skydrive.live.com/?cid=E196374A237958B4&id=E196374A237958B4!120
The main file you want is PL2PLv1, pick a rar or zip version. If you want the rivers to be blue instead of muddy, also download PL2_Blue_Rivers_v4, and if you want lights on your aerodrome field, then also get PL2_Dromelights.
There is also a Promised Land 1 terrain graphics, but it has a tendency to crash the game under Vista and Win 7.
GabiLaser created a few starters for RB3D that mod the game. RedBullet gives you the ability to have an ammo counter in multi-player, whereas TViewMP allows you to use Track IR to look around your plane while flying. But the greatest mod he created is ReLoad, which is for single player. Reload, give you a bullet counter, the ability to use Track IR, different engine types, and the ability to set a constant wind speed and direction. I have them here: https://skydrive.live.com/?cid=E196374A237958B4&id=E196374A237958B4!423
I hope I did not over confuse you there