[AccessD] Nvarchar(max)

Gustav Brock gustav at cactus.dk
Tue Jul 27 09:28:37 CDT 2021

Hi Anita

I got this back:

Sorry for the late response. I haven't been able to reproduce the issue when attempting to setup the design as described. Perhaps you could supply a sample database for us to use in the reproduction?

Would you be able to do that?


-----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)

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 Smith 
M: 0402 022 462 

More information about the AccessD mailing list