I made two changes:
1.saved my Access 2000 mdb file to Access 2010 accdb.
2.changed the SQL Server Notes column of the tblContractNotes table, from ntext to nvarchar(max)
using the same DAO code
--I can save more than 8000 characters in Access 2000 using an ntext for the Notes column
--but in Access 2010 with nvarchar(max) for the Notes column, I now can't save a record when the notes are over 4000 characters, get a "type mismatch" error 13 and an ODBC call failed error.
code is:
Public gdbTables As DAO.Database
Set gdbTables = DBEngine(0)(0)
Dim rst As DAO.Recordset
Dim rstContracts As DAO.Recordset
Dim str As String
Dim strNotes As String
Dim dteDTS As Date
Dim tmpItem As Variant
strNotes = "" 'in other cases stuff is added, but not in this case that I tried
Set rst = gdbTables.OpenRecordset("SELECT * FROM tblContractNotes WHERE 1 = 2", dbOpenDynaset, dbSeeChanges)
With rst
.AddNew
!ContractID = mlngContractID
!SCView = Me.chkSCView
!Urgent = Me.chkUrgent
!UserID = glngUserID
!Notes = strNotes & Me.txtNotes 'tried more than 4000 characters
.Update
End With
In Access 2000, I use DAO 3.6.
I noticed in Access 2010, I didn't change any Tools/References, but it doesn't show any DAO, but maybe it's included somewhere. I used the default Tools References, which were:
-Visual Basic For Applications
-Microsoft Access 14.0 Object Library
-OLE Automation
-Microsoft ActiveX Data Objects 2.1 Library
-Microsoft Office Web Components 9.0
-Microsoft Office 14.0 Access database engine Object Library
-Microsoft Visual Basic for Applications Extensibility 5.3
Thanks for any suggestions.