VBA SQL SELECT STATEMENT NOT PULLING ALL DATA

[Larry Haydn]

In my Excel VBA Application, I have the following SQL statement:

Set rs = SQLConn.Execute("SELECT CustCode, CustName, ProdName, TapeDir, '', ReelLen, BoxQty, SRLblType, '', MoldingMethod, MatType, MatWidth, DimK, SID, RFID, HarmonyID, ReelType FROM PRODMAS ORDER BY CUSTCODE;")
PRODMAS.Range("A2").CopyFromRecordset rs

As you can see, there is no filter in the SQL statement.

There are 389 records in the database.

When I run the SQL Statement in SQL Server Management Studio, it pulls 389 records, as expected.
However, the same SQL statement in VBA pulls out only 349 records.
(50 records missing).

Why?
Anyone encounter similar issues?

I am using Windows 11, version 10.0.22621 Build 22621

 

[Larry Haydn]

I ran another SQL Statement in SQL Server Management Studio on another table.

SELECT * FROM SUBCONSTOCK;

It pulls out 38,805 records.
When I put it in Excel VBA, it pulls out only 48 records.

I ran another SQL Statement in SQL Server Management Studio on another table.

SELECT * FROM FS1;

It pulls out 144,185 records.
When I put it in Excel VBA, it pulls out only 62,919 records.

So it seems to me that the problem is consistent, and this may be a problem in recent Windows or Office updates.

 

[Alex Blakenburg]

Any chance it just needs delay to finish loading all the records ?
Would any of these make a difference before running the CopyFromRecordset
• Add a DoEvents Line OR
• Add a timer loop OR
• Add a loop outputting the column headings