Delphi 10.3.3 and SQL Server 2000 LOCATE problem

Question

I noticed, in a program written with Delphi 10.3.3, that it can happen that a LOCATE statement returns "false negatives".

All LOCATE statements look at the FULL KEYof the record, sometimes simply an integer, sometimes strings or dates, but the TYPE of the fields doesn't seem to be the reason for the error.

The instructions typically look like this:

result:= ADOTable.LOCATE('field1;field2;field3',VarArrayOf([value1,value2,value3]), [loCaseInsensitive]);

Where field1;field2;field3 is ALWAYS the primary key of the ADOTable table.

The database server is an old Microsoft SQL Server. I have never had any unsolvable problems with this architecture, with the exception of the use of LOCATE.

When the program was developed in a 32-bit environment, we used the FindKey instruction, and this, at least so it seems, never caused problems.

But when we "moved" the program to a 64-bit environment we had to make several changes, including replacing all the FindKeys with the LOCATE instruction.

We have proof that every now and then a LOCATE returns FALSE even if the record actually exists, but we were never able to find an explanation.

The ODBC driver (64bit) I use is "SQL Server native client 10.0"

My program is very large, but I have NEVER had any problem reading/writing data on SQL Server, I only have this problem with LOCATE.

I'm replacing ALL the locates (over 7.700 !) with queries (SELECT to see if the record already exists, UPDATE if it exists, otherwise INSERT INTO)

It's a VERY LONG job, but with the queries the processing times are very fast and I don't have any "false negative" problems

Before spending the next months doing this BORING job, has anyone had a similar problem? Were you able to figure out how to fix it?

A possible solution is to close and re-open the ADO table before each LOCATE, but this way the processing times become enormous.

I thank everyone in advance for their attention and any advice.