No. Depending on the OS you will access the data the same way as you access it now.
For example, on Linux a hard drive is mounted as a directory. If your host allows you to mount the drives yourself, you can, for example mount a second drive as /data/
then you can just access files from the /data/ directory which would really read the data from a second hard drive.
The first hard drive has a folder called /uploads/
When the first hard drive runs out of space, does the system automatically store all new uploads in the second hard drive? (even though the directory is in the first hard drive) Or do I need to explicitly use the path to the second hard drive to do this?
Unless you have the drives configured to form a single virtual drive then each drive would have its own directry path and you'd need to specify the appropriate directlry for which you wanted to access.
If you did have three 250Gb drives configured so that the operating system sees them as one big 750Gb drive then a failure of any one of them could make it difficult to access the data on the other two.