[AccessD] Nvarchar(max)

[Anita Smith]

Gustav,
I have cracked open a new day and with it, a clearer head.

These are my findings:

Long story short - no matter the versions of Access and SQL server the nvarchar(max) issue persisted and always went away if I changed the field size to nvarchar(1000).

I did some more digging and discovered the following:

>From the customer log search form which displays the customer log items in a continuous form and allows the user to find log items based on various criteria (this form is snapshot - with a read only recordset), the user would click a button next to a log item that opens the customer screen - this customer screen has its own subform also listing the log items.

So now the customer log is opened twice on two different screens.

This caused the customer log record to freeze in the subform on the customer screen. This only happened if the field was nvarchar(max) and as soon as I changed it to nvarchar(1000) the problem vanished.

This indicates record locking issues that somehow gets worse with the larger nvarchar(max) field.

This is a good outcome as it is unlikely to happen in many instances and should not cause too much havoc out there.

I will change the field size on site as they only need about 500 characters by the look of the data in the table thus far.

Anita



Anita Smith 
M: 0402 022 462