SQL statement in Access is causing speed issues on Windows 10-CodePudding

I have a vb6 program that calls a vb.net program to print a DevExpress report. The programs have worked fine for years until last year when upgrading to Windows 10 on some pc's (these are brand new pc's). Normally it takes about 2 seconds for the report to print, but on some of the new pc's (not all) the program hangs when trying to fill a datatable/datareader with a dataAdapter. Others have suggested it's something in the sql statement even though we have had zero issues on all Windows 7 pcs out there and about 5 Windows 10 pc's. This is only happening on certain Windows 10 pc's.

Can this sql statement be cleaned up at all? SwatKey is the only variable there at the end:

              "SELECT WeightCert, [SwatLog].[SwatDate], TareDate, SaleCode, " &
                "Species, Qual, SaleDesc, Trucker, TruckNo, TruckState, " &
                "TruckLic, TrlState, TrlLic, TruckType, Comments, TareLoad, " &
                "ScaleLoad, LoadNo, Logger, LogMethod, Block, Val(Gross) as GrossWt, " &
                "Val(Tare) as TareWt, Weight, PrintAvg, Brand, Commodity, SortCode, " &
                "Deck, UserInfo1, UserInfo2, EmergencyLevel, ReprintCount, " &
                "Reason, LocationName, Addr1, Addr2, OwnerName, LoggerName," &
                "Contract, Weighmaster, TT, Reprint, TareoutBarcode, PrintTare, TruckName, " &
                "ManualWeight, DeputyName, CertStatus, ReplacedCert  " &
          "FROM Swatlog INNER JOIN tblTempCert " &
            "ON [SwatLog].[SwatDate] = [tblTempCert].[SwatDate] " &
         "WHERE [tblTempCert].[SwatDate] = #" & SwatKey & "#"

CodePudding user response：

First, make sure you have indicies on fields SwatDate.

Next, try filtering on the other table:

"WHERE [swatLog].[SwatDate] = #" & SwatKey & "#"

or on both:

"WHERE [tblTempCert].[SwatDate] = #" & SwatKey & "# And [swatLog].[SwatDate] = #" & SwatKey & "#"