I ran into a problem with nText fields when porting a third-party app from MS Access to MS SQL Server. Wherever the existing code tried to get the value of an nText field in the RecordSet object, the return value would be blank.
I searched all over and found a workaround, which I did not like. The work-around suggested by many is that you must specifically select all fields in your SQL query In other words, you can’t do “SELECT * FROM”, you have to do “SELECT field1,field2,field3 FROM”. in addition to that, they say that you have to select the nText field LAST in your query. It has to be the last field in the list. And in addition to that, you have to then read that value from the RecordSet FIRST before accessing any other fields. So, you have to store it in a variable and save it until you need it.
Needless to say, this can be a painful process depending on how crappy the code you’re dealing with. However I found a solution that worked better. by simply changing the RecordSet LockType property to 1 PRIOR to opening the RecordSet, I don’t have this problem at all.
my RS settings that seem to work with nText fields are:
rs.CursorType = 0
rs.CursorLocation = 2
rs.LockType = 1
i set these properties prior to calling rs.Open of course.
best of luck with your code!
