[AccessD] Memo fields; Access and SQL Server

Hi,
I am currently grinding my teeth on SQL Server 2005 after 
having upsized an Access FE BE mdb. Only upsized the BE and 
am now using ODBC to link tables in SQL Server back into the 
Access FE.

A few questions:

1) Access Memo fields have been upsized to the ntext() data 
type. However, the online documentation for SQL 2005 
recommends using the nvarchar(max) rather than ntext() 
saying that ntext() will be dropped in future SQL release.
Can I safely ignore this for the time being, or should I 
change all ntext() to the nvarchar(max) ?

2) In the old Access FE BE world I could safely update a 
memofield with a SQL update using the following code snippet:

    Set Wsp = DBEngine.Workspaces(0)
    Wsp.BeginTrans
    On Error GoTo ErrorTransaction
    Set Db = CurrentDb
    Db.Execute strSQL, dbFailOnError
    Wsp.CommitTrans

This dao code does not work well in general with SQL Server 
tables as the backend, but needs to have added the option 
dbSeeChanges like this:

    Set Wsp = DBEngine.Workspaces(0)
    Wsp.BeginTrans
    On Error GoTo ErrorTransaction
    Set db = CurrentDb
    db.Execute strSQL, dbFailOnError + dbSeeChanges
    Wsp.CommitTrans

But using this dao code snippet will NOT!! update a memo 
field, SQL Server truncates around the 256 character. 
The strSQL string being built in the code contains ALL the 
memofield text.

Creating this ado code snippet and using a stored procedure 
does the trick:

    Dim cn As New ADODB.Connection
    Dim lngRecsAffected As Long
    With cn
      .Provider = "Microsoft.Access.OLEDB.10.0"
      .Properties("Data Provider").Value = "SQLOLEDB"
      .Properties("Data Source").Value = 
CurrentSQLOLEDB_DataSource
      .Properties("User ID").Value = CurrentODBC_UID
      .Properties("Password").Value = CurrentODBC_PWD
      .Properties("Initial Catalog").Value = 
CurrentSQLOLEDB_InitialCatalog
      .Open
    End With
    
    On Error GoTo ErrorTransaction
    cn.BeginTrans
    cn.Execute strParameterString, lngRecsAffected, 
adExecuteNoRecords
    Debug.Print lngRecsAffected
    cn.CommitTrans

(The connection properties are set using some static 
function calls, the strParameterString contains the stored 
procedure name and all the parameter values including the 
Memo text; the adExecuteNoRecords is apparently important 
for procedures that do not return records; the 
lngRecsAffected is optional and is given the value of -1 
when running the execute command)

This procedure runs ok and updates the ntext() / memo fields 
fine. 
Any text with single quotes should have the single quotes 
doubled in order for the sp to run though.
So the string variable that holds the memo is massaged with 
a 
strMemo = replace(strMemo,"'", "''")
And date fields should be parsed in a strings like 'yyyy-mm-
dd'....

Now my questions from this experience are: 

Is this the best way to do an update of a record (unbound 
form), i.e. stored procedure and adodb connection? 

Apart from not handling memo fields are there other areas 
where dao code will fall short when working with odbc linked 
SQL server tables?

Currently I am ending up with code that is a mix of dao and 
ado - adodb being using wherever we see the code fall over 
and not behave properly.

regards
borge