troubleshooting Question

rst.MoveLast method takes hours.

Avatar of ltdanp22
ltdanp22Flag for United States of America asked on
Microsoft AccessVisual Basic Classic
17 Comments1 Solution405 ViewsLast Modified:
I have a recordset based on a simple query that contains several million records...

("SELECT T024_ITM_NBR, T063_LCT_NBR FROM Demand_Check_C;")

When the code kicks off the rst.MoveLast method, it takes hours to run that one line. Is this normal or am I doing something wrong? It seems like such a simple thing to just move to the last record in a recordset.

If this is normal, is there a way to figure out the number of records in a recordset more quickly (I'm calling the MoveLast method in a block of code that determines the number of records in a recordset)?

The complete function is available as an attachment.
'Speed up qry10002 with literals
Public Function GetDemand()
    Dim dbs As Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim sInsert As String, sSelect As String, sFrom As String, sWhere As String, sSQL As String
    Dim lRecordCount As Long: lRecordCount = 0
    Dim lCounter As Long
    
    DoCmd.SetWarnings False
    
    DoCmd.RunSQL "DELETE * FROM tblDemand"

    'sInsert = "INSERT INTO tblDemand"
    sSelect = " SELECT LOWES.T2398_IFM_FRC_VAL.T024_ITM_NBR, LOWES.T2398_IFM_FRC_VAL.T063_LCT_NBR, CASE WHEN SEN_BGN_WK_NBR<=SEN_END_WK_NBR THEN SEN_END_WK_NBR-SEN_BGN_WK_NBR+1 ELSE 52-SEN_BGN_WK_NBR+SEN_END_WK_NBR+1 END AS ""Selling Weeks"", " & _
    " LOWES.T2355_SEN_PRL.WK1_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK2_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK3_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK4_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK5_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK6_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK7_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK8_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK9_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK10_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK11_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK12_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK13_BAS_IND_FCT AS ""Q1 Sum of BIs""," & _
    " LOWES.T2355_SEN_PRL.WK14_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK15_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK16_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK17_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK18_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK19_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK20_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK21_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK22_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK23_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK24_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK25_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK26_BAS_IND_FCT AS ""Q2 Sum of BIs""," & _
    " LOWES.T2355_SEN_PRL.WK27_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK28_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK29_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK30_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK31_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK32_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK33_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK34_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK35_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK36_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK37_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK38_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK39_BAS_IND_FCT AS ""Q3 Sum of BIs""," & _
    " LOWES.T2355_SEN_PRL.WK40_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK41_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK42_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK43_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK44_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK45_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK46_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK47_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK48_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK49_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK50_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK51_BAS_IND_FCT+LOWES.T2355_SEN_PRL.WK52_BAS_IND_FCT AS ""Q4 Sum of BIs"""
    sFrom = " FROM (LOWES.T2354_ITM_LCT_PRL INNER JOIN LOWES.T2355_SEN_PRL ON LOWES.T2354_ITM_LCT_PRL.T2355_PRL_TAG_ID = LOWES.T2355_SEN_PRL.T2355_PRL_TAG_ID) INNER JOIN LOWES.T2398_IFM_FRC_VAL ON (LOWES.T2354_ITM_LCT_PRL.T063_LCT_NBR = LOWES.T2398_IFM_FRC_VAL.T063_LCT_NBR) AND (LOWES.T2354_ITM_LCT_PRL.T024_ITM_NBR = LOWES.T2398_IFM_FRC_VAL.T024_ITM_NBR)"
    sWhere = " WHERE (LOWES.T2398_IFM_FRC_VAL.T024_ITM_NBR = -1 AND LOWES.T2398_IFM_FRC_VAL.T063_LCT_NBR = -1)"

    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("SELECT T024_ITM_NBR, T063_LCT_NBR FROM Demand_Check_C;")

    If rst.EOF Then
        lRecordCount = 0
    Else
        rst.MoveLast
        lRecordCount = rst.RecordCount
    End If

    rst.MoveFirst

    Do While Not rst.EOF

        lCounter = lCounter + 1
        sWhere = sWhere & " OR (LOWES.T2398_IFM_FRC_VAL.T024_ITM_NBR = " & rst("T024_ITM_NBR") & " AND LOWES.T2398_IFM_FRC_VAL.T063_LCT_NBR = " & rst("T063_LCT_NBR") & ")"

        If lCounter > 60 Or rst.AbsolutePosition = lRecordCount - 1 Then
            sSQL = sInsert & sSelect & sFrom & sWhere ' & " WITH UR"
            Set qdf = CurrentDb().QueryDefs("qryPassThrough")
            qdf.SQL = sSQL

            DoCmd.OpenQuery "qryDemand"

            'DoCmd.RunSQL sSQL
            lCounter = 0
            sWhere = " WHERE (LOWES.T2398_IFM_FRC_VAL.T024_ITM_NBR = -1 AND LOWES.T2398_IFM_FRC_VAL.T063_LCT_NBR = -1)" '-1 = dummy # to deal with 1st OR
        End If

        rst.MoveNext

    Loop
    
    DoCmd.SetWarnings True
    
    rst.Close

End Function
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 17 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 17 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros