FAST FIND in big array

[sal21]

my code:

...
IN MODULE
PUBLIC BIG_ARRAY() As Variant
IN MODULE
 
'BIG ARRAY
    SQL = "SELECT DESCRIZIONE & ' - ' & CODICE & ' - (' & PROVINCIA & ')' AS RIGA FROM COMUNI UNION SELECT DESCRIZIONE & ' - ' & CODICE & ' - (' & PROVINCIA & ')' AS RIGA FROM STATI "
    Set RSB = New ADODB.Recordset
    RSB.CursorLocation = adUseClient
    SQL = "SELECT DESCRIZIONE & ' - ' & CODICE & ' - (' & PROVINCIA & ')' AS RIGA FROM COMUNI"
    RSB.Open Source:=SQL, _
             ActiveConnection:=CON, _
             CursorType:=adOpenForwardOnly, _
             LockType:=adLockReadOnly
    RSB.Sort = ("RIGA")

    RSB.MoveFirst
    Erase BIG_ARRAY()
    BIG_ARRAY = RSC.GetRows()
    RSB.Close
    Set RSB = Nothing
    'BIG ARRAY

Possible to find MyString ( as string dimensioned) without loop the array (it contain approx 150000 item!)

and return a booelean=true is the string is in array.

my test esperiment but dont work!!!

STRINGA = UBound(Filter(BIG_ARRAY, Me.CCOMNASC.Text)) > -1

STRINGA = InStr(1, Join(BIG_ARRAY, vbNullChar), Me.CCOMNASC.Text) > 0

If InStr(Join(BIG_ARRAY), Me.CCOMNASC.Text) > 0 Then Stop

 

[Andrzejek]

So, you have MyString variable that contains whatever you are looking for, and a couple of tables in your data base: COMUNI and STATI
When you create a recordset, that’s an array. Then you copy this array into another array called BIG_ARRAY And now you are trying to find out if MyString is anywhere in your BIG_ARRAY?

Why not simply create a small recordset where you can ask for whatever you are looking for directly from your data base? Something like:
[tt]
SELECT DESCRIZIONE & ' - ' & CODICE & ' - (' & PROVINCIA & ')' AS RIGA
FROM COMUNI
WHERE DESCRIZIONE & ' - ' & CODICE & ' - (' & PROVINCIA & ')' [blue]like ('* & MyString & *')[/blue]
[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

Your source is a database - which is really rather good at finding stuff ...

So why not leverage that?

E.g. (and a reminder this is an illustration of how to do this, NOT production code)

[blue]...
IN MODULE
PUBLIC BIG_ARRAY() As Variant
IN MODULE
 
'BIG ARRAY
    SQL = "SELECT DESCRIZIONE & ' - ' & CODICE & ' - (' & PROVINCIA & ')' AS RIGA FROM COMUNI UNION SELECT DESCRIZIONE & ' - ' & CODICE & ' - (' & PROVINCIA & ')' AS RIGA FROM STATI "
    Set RSB = New ADODB.Recordset
    RSB.CursorLocation = adUseClient
    SQL = "SELECT DESCRIZIONE & ' - ' & CODICE & ' - (' & PROVINCIA & ')' AS RIGA FROM COMUNI"
    RSB.Open Source:=SQL, _
             ActiveConnection:=CON, _
             CursorType:=adOpenForwardOnly, _
             LockType:=adLockReadOnly
    RSB.Sort = ("RIGA")

    RSB.MoveFirst
[b]    RSB.Filter="RIGA LIKE '*" & Me.CCOMNASC.Text & "*'"
    If RSB.RecordCount > 0 Then msgbox "String currently in Me.CCOMNASC.Text found in recordset RSB"[/b]
    RSB.Close
    Set RSB = Nothing
    'BIG ARRAY [/blue]

 

[2009luca]

No.
I need bigarray!
I use this for other parte of code.
My really problem Is to find if string exists in Array.
The Array contain approx 150.000 item!
I know the day ti find string by for next looping.
But i need a fast method to find string in Array without looping.

 

[Andrzejek]

Possible to find MyString ( as string dimensioned) without loop the array (it contain approx 150000 item!) and return a booelean=true is the string is in array.

Both strongm and my logic allows you to set your "booelean=true" when your string is found.
And is it "a fast method to find string in Array without looping" since your "BIG_ARRAY = RSC.GetRows()"

"I need bigarray!" - well, you can keep your bigarray.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

>I need bigarray!

I suspect that you didn't, to be honest, to start with. Most of the times you have populated bigarray from a recordset (from the various examples of the various problems you have been having that you have posted here), and the reality is that you'd have been better off directly using the recordset rather than toying with bigarray. But you made a design decision, and I guess it is now too late.

 

[Andrzejek]

I have seen similar approach before: creating a recordset, then copy the data into an array, then copy some or all elements into another array, and then again into something else. Then modifying some of the data in some of the array elements. At the end, nobody knows what is what and where is the data that you want to use. Especially when the names of the variables are very similar.
It is a pain when the code needs to be modified, even to a person who wrote it. But it is a nightmare to modify it by somebody else. In most cases like this, I simply rewrite the code because it is easier and faster that trying to untangle the existing mess.
I am not saying that is in this case, just a word of warning...


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

That being said, not quite sure why Filter didn't work for you in this particular case.