openrecordset work around?

[meacho]

hi the problem i have is that i cant get the value out of a query without setting/restting the recordset every time. and this takes ages ( about 1 sec but * 10,000 times)

i was originally using Dlookup to get the value out of the query but this is even worse!
my code is as follows as you can see each time i go aroud the loop i have to set the recordset to refresh it and get the new value strored in the query


hope someone can help becaue i'm all out of ideas.

            Set rstd2 = currentdb.OpenRecordset("SC_Volume_BU_Calculated")
            Set rstd1 = currentdb.OpenRecordset("TempProjectList")
            For i = 0 To rstd1.RecordCount - 1
                For j = 1 To 13
            ' =========================== put a list of projects into the answers table= ===========================
                

 sqlstring = "Select sum ([SC_Volume_BU_Calculated].[Budgeted_Units]) as SUMBU FROM [SC_Volume_BU_Calculated] " & _
"Where [SC_Volume_BU_Calculated].[Project_ID] = " & Chr(34) & projID & Chr(34) & " " & _
"and [SC_Volume_BU_Calculated].[Period_Number] = " & Chr(34) & j & Chr(34) & " " & _
 "AND [SC_Volume_BU_Calculated].[cat] > '73' "
                    
                      Set myqdf = currentdb.CreateQueryDef("append1", sqlstring)
                       Set rstdappend1 = currentdb.OpenRecordset("append1")
                                                rstdappend1.MoveFirst
                       BUVariable3 = 0.33 * (rstdappend1.Fields("SUMBU").Value)
                                                myqdf.Close
                                                Set myqdf = Nothing
                                                                                                
                                                currentdb.QueryDefs.Delete "append1"
                                                                                              
                                                If IsNull(BUVariable3) Then
                                                BUVariable3 = 0
                                                End If
                                                BUVariable3 = BUVariable2 + BUVariable3
                                                
 sqlstring = "UPDATE [Volume Answers SC] SET [Volume Answers SC].[Budgeted Units] = " & Chr(34) & _
BUVariable3 & Chr(34) & " WHERE [Volume Answers SC].Project_ID = " & Chr(34) & projID & Chr(34) & " and [Volume Answers SC].Period = " & Chr(34) & j & Chr(34) & ";"
 
         Set myqdf = currentdb.CreateQueryDef("update1", sqlstring)
            DoCmd.OpenQuery "update1"
            DoCmd.Close acQuery, "update1"
            currentdb.QueryDefs.Delete "update1"
        
            DoCmd.Close acQuery, "append1"
        Next
    
rstd1.MoveNext
Next

i've cut this bit out to show how i'm trying to get the result of a query out into a variable and look

if anyone can help speed this up i'd be sooooo greatful

thanks
Matt

 

[The_Doc_Man]

You need to do the reset because (it appears) you are rebuilding the inner query for every loop. This "invalidates" the previous iteration's SELECT.

Consider splitting the query parts so that at least some part of the inner query is totally invariant. Open that query outside the loop.

Inside the loop, use the FINDNEXT methods on the query where you supply the search criteria dynamically. Look up the FINDNEXT method in Access Help if you are not sure about it.

Since you are searching this puppy so hard, also consider whether you should define the underlying tables to have indexes on the fields you search most often. A query can make use of that index even though you open the query, not the table.

 

[meacho]

thanks for the advice, i've had a look into it and i can't really see how to use FindNext,

what i'm trying to achieve is:

search a table for the sum off three or so values where the record has the correct project id and period number, each project has a possible 13 entries so i'm loopoing through this 13 times changing the search criteria (j = 1-13)

i am then getting the value of this select query by setting the recordset then setting it to a variable

each time i loop i have to reset the recordset to get the updated value held in the query

how can i use findnext to avoid this?

thanks for the help.

do you think shoiuld sack off the sql and just use vba would that be quicker

 

[meacho]

well...... i gave it some thought and followed your advice and this is what i came up with

Function PLVolumeAnswers(Territory_DD As String)
DoCmd.SetWarnings False
'Echo False
'Application.Screen = False
Dim myqdf As QueryDef
Dim mydate As Date
Dim ThisYear As String
Dim rstdcalc As DAO.Recordset
Dim periodNP As String

projID = ""
periodN = [Forms]![Mihub_MBR1]![Period_Number]
periodNP = periodN + 1

                Set rstd1 = currentdb.OpenRecordset(Territory_DD & "_ProjectsList")
                Set rstd2 = currentdb.OpenRecordset("PL_Volume_BU_Calculated")
                Set rstdcalcBU = currentdb.OpenRecordset("PL_Volume_BU_Calculated", DB_OPEN_DYNASET)
                Set rstdcalcAC = currentdb.OpenRecordset("PL_Volume_AC_Calculated", DB_OPEN_DYNASET)
                Set rstd5 = currentdb.OpenRecordset("Periods")
                                
                                        rstd5.MoveLast
                                        rstd5.MoveFirst
                                         mydate = date
                                        For p = 0 To rstd5.RecordCount - 1
                                            If mydate >= rstd5.Fields("Start_Date").Value Then
                                                If mydate <= rstd5.Fields("End_Date").Value Then
                                                    ThisYear = rstd5.Fields("Year").Value
                                                    Exit For
                                                End If
                                            End If
                                        rstd5.MoveNext
                                        Next p
            rstd1.MoveFirst
For i = 0 To rstd1.RecordCount - 1
   projID = rstd1.Fields("Project_ID").Value
    VariableFYFAtCompletion = "0"
    VariableHistoric = "0"
    VariableFYFBudgetedUnits = "0"
    VariableYTDAtCompletion = "0"
    For j = 1 To 13
    VariableBU = "0"
    VariableAC = "0"
    
    
                    
                    
                    sqlstring = "INSERT INTO [Volume Answers PL] ( Project_ID, Territory, Portfolio, period )" & _
                                "SELECT distinct PL_Volume_BU_Calculated.Project_ID, PL_Volume_BU_Calculated.Territory, PL_Volume_BU_Calculated.Portfolio, " & j & "  AS Period " & _
                                "FROM PL_Volume_BU_Calculated " & _
                                "WHERE([PL_Volume_BU_Calculated].Project_ID) = " & Chr(34) & projID & Chr(34) & "  AND [PL_Volume_BU_Calculated].delivery_Year = " & Chr(34) & ThisYear & Chr(34) & ""
            
                 Set myqdf = currentdb.CreateQueryDef("append", sqlstring)
                            DoCmd.OpenQuery "append"
                            DoCmd.Close acQuery, "append"
                            currentdb.QueryDefs.Delete "append"
                            
                            
               rstdcalcBU.FindFirst "[Period_Number] = " & Chr(34) & j & Chr(34) & " And " & "[Project_ID] = " & Chr(34) & projID & Chr(34)
               rstdcalcAC.FindFirst "[Period_Number] = " & Chr(34) & j & Chr(34) & " And " & "[Project_ID] = " & Chr(34) & projID & Chr(34)
                  
                  
                  
                    If rstdcalcBU.NoMatch And rstdcalcAC.NoMatch Then
           
           
           Else
               Do While rstdcalcBU.NoMatch = False Or rstdcalcAC.NoMatch = False
            'numBU = rstdcalcBU.AbsolutePosition
            'numAC = rstdcalcAC.AbsolutePosition
                                                        
                               
                                variabletempBU = "0"
                                variabletempBU = (rstdcalcBU.Fields("Budgeted_Units").Value)
                                VariableBU = VariableBU + variabletempBU
                                
                                variabletempAC = "0"
                                variabletempAC = (rstdcalcAC.Fields("Completion_units").Value)
                                VariableAC = VariableAC + variabletempAC
                                                 
                                If rstdcalcAC.Fields("Period_Number") = (periodNP) Then
                                VariableTempHistoric = "0"
                                VariableTempHistoric = (rstdcalcAC.Fields("Completion_units").Value)
                                VariableHistoric = VariableHistoric + VariableTempHistoric
                                End If
                                
                                VariableTempFYFAtCompletion = "0"
                                VariableTempFYFAtCompletion = (rstdcalcAC.Fields("Completion_units").Value)
                                VariableFYFAtCompletion = VariableFYFAtCompletion + VariableTempFYFAtCompletion
                                
                                VariableTempFYFBudgetedUnits = "0"
                                VariableTempFYFBudgetedUnits = (rstdcalcBU.Fields("Budgeted_Units").Value)
                                VariableFYFBudgetedUnits = VariableFYFBudgetedUnits + VariableTempFYFBudgetedUnits
                                
                                If rstdcalcAC.Fields("Period_Number") <= periodN Then
                                VariableTempYTDAtCompletion = "0"
                                VariableTempYTDAtCompletion = (rstdcalcAC.Fields("Completion_units").Value)
                                VariableYTDAtCompletion = VariableYTDAtCompletion + VariableTempYTDAtCompletion
                                End If
                                
                                If rstdcalcBU.NoMatch = False Then rstdcalcBU.FindNext "[Period_Number] = " & Chr(34) & j & Chr(34) & " And " & "[Project_ID] = " & Chr(34) & projID & Chr(34)
                                If rstdcalcAC.NoMatch = False Then rstdcalcAC.FindNext "[Period_Number] = " & Chr(34) & j & Chr(34) & " And " & "[Project_ID] = " & Chr(34) & projID & Chr(34)
                      Loop
                        '============
                        'At Completion and Bugeted Units update
                        '============
                        sqlstring = "UPDATE [Volume Answers PL] SET [Volume Answers PL].[Budgeted Units] = " & Chr(34) & _
                        VariableBU & Chr(34) & ", [Volume Answers PL].[At Completion Units] = " & Chr(34) & _
                        VariableAC & Chr(34) & " WHERE [Volume Answers PL].Project_ID = " & Chr(34) & projID & Chr(34) & " and [Volume Answers PL].Period = " & Chr(34) & j & Chr(34) & ";"
                        
                        Set myqdf = currentdb.CreateQueryDef("update1", sqlstring)
                        DoCmd.OpenQuery "update1"
                        DoCmd.Close acQuery, "update1"
                        currentdb.QueryDefs.Delete "update1"
                        
                        '==========
                        'Historic Update
                        '============
                        sqlstring = "UPDATE [Volume Answers PL] SET [Volume Answers PL].[Forecast] = " & Chr(34) & _
                        VariableHistoric & Chr(34) & " WHERE [Volume Answers PL].Project_ID = " & Chr(34) & projID & Chr(34) & " and [Volume Answers PL].Period = " & Chr(34) & periodN & Chr(34) & " + 1;"
 
                        Set myqdf = currentdb.CreateQueryDef("update2", sqlstring)
                        DoCmd.OpenQuery "update2"
                        DoCmd.Close acQuery, "update2"
                        currentdb.QueryDefs.Delete "update2"
                        
                        '==========
                        'FYF Forecast/Actuals & FYF Current Budget &YTD
                        '==========
                        sqlstring = "UPDATE [Volume Answers PL] SET [Volume Answers PL].[FYF Actual /Forecast] = " & Chr(34) & _
                        VariableFYFAtCompletion & Chr(34) & " ,[Volume Answers PL].[FYF Current Budget] = " & Chr(34) & _
                        VariableFYFBudgetedUnits & Chr(34) & ", [Volume Answers PL].[YTD] = " & Chr(34) & _
                        VariableYTDAtCompletion & Chr(34) & "  WHERE [Volume Answers PL].Project_ID = " & Chr(34) & projID & Chr(34) & " and [Volume Answers PL].Period = " & Chr(34) & periodN & Chr(34) & " ;"
    
                        Set myqdf = currentdb.CreateQueryDef("update3", sqlstring)
                        DoCmd.OpenQuery "update3"
                        DoCmd.Close acQuery, "update3"
                        currentdb.QueryDefs.Delete "update3"
                        

                     
                     End If
                     Next
                
                
        rstd1.MoveNext

Next

End Function

this is the full module and it's a bit quicker but still not as fast as i really need it to be

can you spot anything that i could change to try and make it faster?

thanks for the help
Matt