When a clr has the input type of SqlString it causes nvarchar(4000) to be
the parameter type of the function in sql server. how to modify my CLR
function to use nvarchar(max) ?

Clearly, udt needs evolution, and it's inevitible that you may need to add
or remove some fields in a udt. when this happens, you need to preserver old
data and make data conversion.
Here are roughly the steps to evolve a udt,

I'm developing a VB6 application with a MS Access 2000 database (for
reporting purposes etc.) using an ODBC link. Some tables of it are linked
(another ODBC link) to a SQL Server 2005 database. I need to show some
records in my application coming from a MS Access query running on ...

I have created a Visual Studio SQL Server project (VS2005) in VB.NET
(although C# will do just as well). I have one stored procedure, which takes
an optional parameter.
When I deploy the sproc from VS and execute it in SSMS, I get a message to

mySqlBytes.buffer is getting converted to BigEndian even though both SQL
server 2005 and the CLR function are on the same machine which shows
BitConverter.IsLittleEndian == true
in tsql: select * from dbo.MY_CLR_FUNCTION(cast(1024 as binary(4)))

The following statements:
alter database Goldmine set trustworthy on
create assembly word from
'C:\WINDOWS\assembly\GAC\Microsoft.Office.Interop.Word\11.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Word.dll'