[AccessD] Nvarchar(max)

[Gustav Brock]

Hi Anita

Thanks, it certainly helps to have the full picture.
I'll forward your research and results.

Have a nice weekend!
Gustav

-----Oprindelig meddelelse-----
Fra: AccessD <accessd-bounces+gustav=cactus.dk at databaseadvisors.com> På vegne af Anita Smith
Sendt: 2. juli 2021 08:12
Til: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Emne: Re: [AccessD] Nvarchar(max)

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

-- 
AccessD mailing list
AccessD at databaseadvisors.com
https://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com