Public array from recordset (1 Viewer)

[KenHigg]

Hello all -

I need to put the results of a recordset into an array and have the array avail as public but can't seem to get anything to work. Any suggestions or links to code. I can use rs.getresults to work but now I'm stuck - Thanks!, Ken

 

[CJ_London]

Are you sure you want to put your recordset in an array? recordsets are a lot more flexible.

However to create a public array you declare it in a module e.g.

Public StrArray() as String

 

[Galaxiom]

I assume you mean the GetRows Method of the recordset which will write the recordset to an array.

I am sure you know how to declare a public array. Just exactly where are you stuck?

BTW Is there any reason you prefer an array to a recordset?

 

[ChrisO]

Option Compare Database
Option Explicit


Public X As Variant


Sub test()
    Dim Y As Long

    With CurrentDb.OpenRecordset("Table1", 2)
        .MoveLast
        .MoveFirst
        X = .GetRows(.RecordCount)
    End With
    
    For Y = LBound(X, 2) To UBound(X, 2)
        MsgBox X(0, Y) & "  " & X(1, Y) & "  " & X(2, Y) & "  " & X(3, Y)
    Next Y

End Sub

Small demo attached.

Chris.

 

[KenHigg]

I'll try Chris's suggestion in the am. Thanks! I am writing a function to put in a query to look up a reference value and have it where it runs the recordset results every time the function is called and could be in the hundreds of thousands of times. Since the results of the recordset are only around 100 rows I figured I run the part to get it once and put it in an array, making the routine much more efficient - What do you all think - ?

 

[ChrisO]

Hi Ken.

If I think back over the years, I don’t think I have ever used an array in Access. If there is some efficiency (speed?) to be gained from using an array then the only way I know to find out is to write it both ways and time it.

I don’t think there is any easy way.

Chris.

 

[nanscombe]

Could you use the recordset to populate a hidden Listbox, or Combobox, and interrogate that instead?

Me.myListBox.Rowsource = myQuery might be easier to code than putting it in an array.


ETA:

Oh hang on, I'm just re-reading this ...

Is this actually looking up, and returning a single reference value each time, regardless of the content of the row, or could the value returned be different from row to row?

If it were the same value every time you could write a function which interrogated the reference table on the first run through, to get the value, and store it is a Static variable for subsequent use ...

Public Function myReferenceFunction(Optional byVal doRefresh As Boolean = False)
Static sVarMyReference as Variant

  If (len(sVarMyReference & vbNullString) = 0) Or (doRefresh = True) Then
    sVarMyReference = DLookup(myColumn, myTable, myCriteria)
  Endif

  myReferenceFunction = sVarMyReference

End Function

I usually include an optional parameter to allow me to force the function to refresh the value.

On the first run through, if the static variable is null it will go off and look up the value and store the result in the static variable.

On subsequent iterations the static variable will not be null so the function will not lookup the result again.

If I pass an optional value of True to the function it will force the value to be looked up again.

 

[Cronk]

I won't presume your purpose. However, one way is to pass the recordset as a publicly declared variable.

I've never done this as a public variable, but I have on one occasion passed the recordset as a variable to a number of subroutines for further processing.

 

[gemma-the-husky]

one difference - it's probably simpler to write some code to directly manipulate an array, compared with a recordset. maybe that's part of the issue.

 

[KenHigg]

Thanks for the feed back - Here's another attempt to explain what I am trying to do-

I have a table with thousands of records. One of the fields is a comment/memo. In this fld there are a number of keywords to look for and if one of these keywords are found I need to put the record in one of several categories. This keyword/category info is dynamic so I have it in a user editable table.

So I create a function and pass the comments fld, loop through each of the keywords in the keywords/category table and search the comments to see if it exists. If it does the function returns the associated keyword category. The way it is set up now the function has to open the keyword/category recordset each time it is called. I was thinking why not put the relativeley small (less than 100 rows) keyword/category data in a public var/array once and use it in the function - ?

 

[KenHigg]

I built this looping through two recordsets with each pass of the function and it works plenty fast without the array - Thanks for the input !