locked
dao recordset : filter or not ? RRS feed

  • Question

  • i have this code to do things for each month ..

    For i = 1 To 12
      strcriteria = "SELECT  datum,field1, field2,field3  from myfile  where year(datum) = " & jaar & " and month(datum) = " & i

     Set rst = CurrentDb.OpenRecordset(strcriteria)
         If rst.EOF Then GoTo verder
               ' do things
        rst.Close
        Set rst = Nothing

    verder:
    Next i

    is this code ok ?

    or is it better to filter the rst.recordset ?

    or is there something else i should do ?

    Saturday, September 28, 2019 11:55 AM

Answers

  • Microsoft states about the Filter property:

    "In many cases, it's faster to open a new Recordset object by using an SQL statement that includes a WHERE clause."

    So I'd stay with your code. I'd probably avoid GoTo, but that is not essential:

        For i = 1 To 12
            strcriteria = "SELECT datum, field1, field2, field3 FROM myfile " & _
                "WHERE Year(datum) = " & jaar & " AND Month(datum) = " & i
            Set rst = CurrentDb.OpenRecordset(strcriteria)
            If Not rst.EOF Then
                ' do things
                ...
                rst.Close
            End If
        Next i
    


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Saturday, September 28, 2019 12:34 PM

All replies

  • or is there something else i should do ?

    Hi tekoko,

    There are many ways to solve your problem. In general, the better you filter, the more eficient is the code.

    In presume that you run your monthly overview at regular times, and in a special case a yearly overview composed from 12 monthly overviews.

    In that case I would make something like:

    Sub Jaar_overview (jaar As Integer)
    
      Dim i As Integer
    
      For i = 1 To 12
    
        Maand_overview (jaar, i)
      Next
    
    End Sub
    
    
    
    Sub Maand_overview (jaar As Integer, maand As Integer)
      Dim sql As String
      Dim rst As RecordSet
    
      sql = "SELECT datum,field1,field2,field3  From myfile  Where Year(datum) = " & jaar & " And Month(datum) = " & maand
    
      Set rst = CurrentDb.OpenRecordset(sql)
      Do While (Not rst.EOF)
       ' do things
        rst.MoveNext
      Loop
    
    End Sub

    You can use the same code for both one month and a complete year.

    Of course, it also depends on what  "do things"  are supposed to do.

    Imb.


    • Edited by Imb-hb Saturday, September 28, 2019 12:29 PM typo
    Saturday, September 28, 2019 12:27 PM
  • Microsoft states about the Filter property:

    "In many cases, it's faster to open a new Recordset object by using an SQL statement that includes a WHERE clause."

    So I'd stay with your code. I'd probably avoid GoTo, but that is not essential:

        For i = 1 To 12
            strcriteria = "SELECT datum, field1, field2, field3 FROM myfile " & _
                "WHERE Year(datum) = " & jaar & " AND Month(datum) = " & i
            Set rst = CurrentDb.OpenRecordset(strcriteria)
            If Not rst.EOF Then
                ' do things
                ...
                rst.Close
            End If
        Next i
    


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Saturday, September 28, 2019 12:34 PM