Results 1 to 4 of 4
  1. #1
    neuk is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    36

    Dictionaries versus Collections

    This was a great post about the difference between collections and dictionaries:

    https://stackoverflow.com/questions/...ons-and-arrays

    about midway down neilt17 says the following:

    “reading from a dictionary takes about twice as long as reading from a collection. Creating a dictionary in the first place is way slower than creating a collection.”

    To prove his point he then shows some calculations. Although dictionaries have a few more features, collections satisfied my requirements so, I deferred to a collection. However, there is one little thing that bothers me about collections. I notice that the key needs to be a string. For reference see here:



    https://docs.microsoft.com/en-us/off...r-applications

    Since all keys in my collection are numbers I have to convert each key to a string for this to work (object.Add item, Cstr(key)). With all things set aside, do you think that even after converting each key to a string in a collection would still make a collection faster than a dictionary? Mind you that dictionary can handle an index that is a long variable type.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    I doubt use of CStr() function will make much difference in processing time but I suppose only comparison testing would determine for sure.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,387
    with regards performance, it matters more what you want to do with the data. They are different things, like a motorcycle and a car. The former is not what you want if you need to transport 4 people.

    Depends on what you are doing but don't forget ado recordsets which are a form of collection.

    not tested for collections but are you sure you need to use Cstr? yes it will store as text so you need to treat as text going forward, but pretty sure assigning a number to a string will work.

    this compiles and runs

    Code:
    Function teststr(i As Integer)
    Dim s As String
    
    
    s = i
    Debug.Print s
    End Function
    
    ?teststr(4)
    4

  4. #4
    neuk is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    36
    Thanks for the information. For context as to what I did please refer to Zev Spitz contribution in the following post:



    Basically he wrote a function in VBA to execute recursion in a query. I took his concept and rewrote it, for my purposes, using the collection method instead. I'm pleased with the overall results. I was just curious about what the experts thought.

    Thank you.



Please reply to this thread with any new information or opinions.

Similar Threads

  1. Does Intellisense not work in collections?
    By MatthewGrace in forum Programming
    Replies: 4
    Last Post: 05-22-2020, 04:19 PM
  2. Hanging Forms, Collections.Type
    By FmrVBAJunkie in forum Programming
    Replies: 5
    Last Post: 05-04-2016, 01:17 PM
  3. Types, classes and collections
    By martingaleh in forum Programming
    Replies: 4
    Last Post: 12-02-2015, 05:12 PM
  4. Budget and Collections(Actuals)
    By dref in forum Queries
    Replies: 20
    Last Post: 08-23-2012, 06:12 AM
  5. Budget and Collections(Actuals)
    By dref in forum Forms
    Replies: 0
    Last Post: 08-19-2010, 03:39 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums