I can't tell you precisely why the OS designers decided to confine the length to 260 characters, beyond that I'm sure that memory allocation considerations had something to do with it. But I can tell you a trick to shorten path names so that there are only eight characters per directory (otherwise known as 8.3 aliasing). That will allow path names that are 27 levels deep, which should solve most, if not all, of your problems.
If you have this path name:
C:\my long directory\my other long directory name\my long directory again\my long file.txt
the short version would be:
C:\MYLONG~1\MYOTHE~1\MYLONG~2\MYLONG~1.TXT
To get the short path name programmatically (borrowed from here):
Private Declare Function GetShortPathName Lib "kernel32" Alias "GetShortPathNameA" (ByVal longPath As String, ByVal shortPath As String, ByVal shortBufferSize As Long) As Long
'The path you want to convert to its short representation path.
Dim longPathName As String
longPathName = "C:\my long directory\my other long directory name\my long directory again\my long file.txt"
'Get the size of the string to pass to the string buffer.
Dim longPathLength As Long
longPathLength = Len(longPathName)
'A string with a buffer to receive the short path from the api call…
Dim shortPathName As String
shortPathName = Space$(longPathLength)
'Will hold the return value of the api call which should be the length.
Dim returnValue As Long
'Now call the function to do the conversion…
returnValue = GetShortPathName(longPathName, shortPathName, longPathLength)
MsgBox(shortPathName)
You can use the short path name in any context that you would use the usual path name.
Since you're interested in the whys of things, Windows originally replaced MS-DOS, which allowed file names and/or directory names of up to eight characters only, with an optional three-character extension. Windows wanted to support longer file and path names without breaking compatibility with the DOS format, so they came up with this method of abbreviating long file names.
For more information, see Naming Files, Paths and Namespaces.