Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 7 of 7
  1. #1
    Junior Member
    Join Date
    Apr 2002
    Location
    Melbourne, Australia
    Posts
    4
    Member #
    81
    Hey Guys,

    I am currently loading RecordSet (Access) values into a ListView,
    some of these values need to totalised, and placed into TextBox's

    What is the prfered/fastest/recource-friendly way to obtain agregate Totals from a RecordSet.

    a. Use a Buffer, and add each value in the RecordSet load Loop routine, or
    b. Open a new instance of the RecordSet, and use an SQL string?


    Cheers,
    Bruce

  2.  

  3. #2
    Guest
    SQL is almost always the best way to go!!
    Loops through recordsets are slow and resource hungry.

  4. #3
    Junior Member
    Join Date
    Apr 2002
    Location
    Melbourne, Australia
    Posts
    4
    Member #
    81
    Originally posted by beacon
    SQL is almost always the best way to go!!
    Loops through recordsets are slow and resource hungry.
    Thanks for the reply Beacon (I was waitin for ya )

    I may not have explained sufficiently, that I am currently
    looping past my ListView to load the data from an Open RecordSet.

    As I can't use SQL "...Sum(FieldName)..." whilst in THIS
    routine (cause its already an open rst using an .Open SQL),
    should I therefore, use a Total Buffer, or on completion of this
    .Open routine, create a new rst.Open with the Sum Sql
    (Hope that makes sense).

    Alternalty, do I have other options; Like can I have 2 SQL's
    in the one .Open routine?

    [vbcode]
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\Pilot Logbook.mdb;Persist Security Info=False;"
    rst.CursorLocation = adUseClient 'Set to enable Cursor movement
    rst.Open "SELECT * FROM Table_Pilot_Logbook", cnn

    rst.Sort = "Date Asc"

    Do While Not rst.EOF
    lvSEL.Visible = False
    Set sItemSEL = lvSEL.ListItems.Add(, , rst.Fields("Date").Value & "")
    With sItemSEL
    .ListSubItems.Add , , rst.Fields("Type").Value & ""
    .ListSubItems.Add , , rst.Fields("Registration").Value & ""
    '
    'More Code - <Clipped>
    '

    'Option 1, Add a series of Buffers to total certain fields as they pass by ***

    rst.MoveNext

    Loop
    [/vbcode]


    Cheers,
    Bruce.

  5. #4
    Guest
    Use an SQL statement to select the records, then a loop to load em ... you are going to have to loop regardless....


    Much prefer the buffer approach myself if doing a lot of manipulation.....

  6. #5
    Junior Member
    Join Date
    Apr 2002
    Location
    Melbourne, Australia
    Posts
    4
    Member #
    81
    G'Day Jethro,

    As I have to loop anyway I thought of the buffers. However, I just thought I'd get the good oil first, before commiting to buffers
    and finding out it slows the rst.Open Load Loop to near a dead stop.

    So I gueesed I had an extra option, the seperate instace of
    Open RecordSet Sum-SQL.

    Bruce.

  7. #6
    Guest
    For sure use an SQL statement first, pushes a lot of the processing off to the server (if using one), and cuts down on the code lines.......

    General l would.

    1. Use an SQL statement to do the sort selection

    2. Open it as a recordset.

    3. Loop on the result recordset.


    Cheers...

  8. #7
    Guest
    Yep sorry mis-understood!

    Agree with Jellyboy!


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 09:49 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com