Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2005
    Posts
    196

    Unanswered: Using Arrrays with ADO

    Using the following code:

    Code:
    Dim AdoConnect as new adodb.connection
    Dim RecSet as recordset
    Dim ArrayOne as variant
    
    Set AdoConnect=CurrentProject.Connection
    RecSet.Open "tblUsers", AdoConnect, adOpenDynamic, adLockOptimistic
    
    ArrayOne =RecSet.GetRows(100)
    What I'm trying to find out is how can you then search through the array you have created for a specific record? I've achieved what i want to do by looping through the recordset but I understand that using an array instead is quicker. So how do you extract data from the ArrayOne variable? The table in the recordset has 5 fields with up to 100 records?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is the 100 just for testing\ dev purposes or is that what you will use in your final solution? If not - how many rows are you likely to use? What are you going to do with your recordset\ array?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sorry - just reread. If you only have 100 records - stick with the recordset. You will have to loop through the array to find specific records. You can use the find method with the recordset to go straight to it.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Have you tested your assumption?

    BTW, you won't get dynamic cursor on Jet tables, but static or keyset should work.

    If I where you, I would test. For ADO, you should test the three following methods up against working with arrays

    * opening a new recordset with a where condition
    * using the .Find method (which is slow, and restricted to one column)
    * using the .Filter property

    Edit - my initial question was meant for the OP
    Roy-Vidar

  5. #5
    Join Date
    Aug 2005
    Posts
    196
    Quote Originally Posted by pootle flump
    Sorry - just reread. If you only have 100 records - stick with the recordset. You will have to loop through the array to find specific records. You can use the find method with the recordset to go straight to it.

    HTH
    Thanks for all your replies. In this particular instance, it's a user table and there will never be more than 10 records. The main reason I was asking the question was because in the near future, I will be working with recordsets on tables with thousands of records, and I have been told that using an array rather than a loop is much quicker. So basically I was wondering if there was a couple of lines of code which can take the array you have declared. Perhaps you know of a good resource on the net? I can't seem to find any tutorials that give me the answer.
    Cheers
    Last edited by Fuerteventura; 04-20-07 at 08:17.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    a quick google (admittedly I probably have a better idea what to look for) and:
    http://www.google.co.uk/search?hl=en...+array+vb&meta=

    Top one looks more than adequate. As Roy says - don't assume that this is quicker. Also, if you are using a client server model you could get the server to do this. Or if you aren't it might still be quicker to do this set based. Depends on what you actually do though of course.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Aug 2005
    Posts
    196
    Many thanks for the link and for all your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •