I'm trying to retrieve specific data from SQL Server via ADODB.recordset connection, and certain records are not found for some reason, (while they are found when running the same query in SQL SERVER itself) and I can't figure out why.

My code:

Dim adset As Object, cnct As Object
Set adset = CreateObject("ADODB.recordset") 
Set cnct = thiscnn
adset.Open "SELECT prt FROM prt WHERE prt = 2204018", cnct, 1

While in other module:

Public thiscnn  As Object
...
Set thiscnn = CreateObject("ADODB.Connection")
thiscnn.Open "DSN=...;Description=...;DATABASE=...;;UID=...;PWD=..."
thiscnn.CommandTimeout = 30

I'm talking here about code working well for years.

The thought that such a simple and useful part of my program which is in vast use, doesn't fetch the data loyally, drives me nuts. I need to give my customers proper, real and true information. I can't have such code sometimes returning and sometimes not returning data.

I know other ways of fetching data, but it is very important for me to understand why this simple way doesn't work all of a sudden in specific cases. It's so peculiar.

I would be greatful for any clue as to what could be happening in my case.

I tried fetching the same query in SQL SERVER itself, and worked fine - but that's the problem, it works in SQL SERVER, but not in VBA with this code above.

I tried looking for differences between records getting fetched and those which don't, and didn't see any special difference.

I tried refetching the same record a few times, but the fetching status for those specific records which don't get fetched (via ADODB.connection / recordset) stays the same.

I tried fetching the same records using the BETWEEN operator rather than the equals (=) operator, yet with no success.

I tried ordering by id column (prt), perhaps the problem is from certain id and up. I tried ordering by the timestamp column thinking maybe from certain timestamp we have a problem - but that didn't give me any clue.

I thought to cast timestamp to date, but I found out that it's only rowversion and doesn't preserve a date or time.

I tried ordering by row size using this, thinking maybe certain ro size is to much for some odd reason, but that didn't help either.

CodePudding user response：

Just a guess, but can it be some dirty data that isn't persisted? Try selecting with nolock:

SELECT prt FROM prt (NOLOCK) WHERE prt = 2204018

And perhaps change cursor type:

adset.Open "SELECT prt FROM prt WHERE prt = 2204018", cnct, 2

Or you are looking into wrong database. Usually these errors are something stupid :D