SEEK with multi fields

[sal21]

I use ADO Seek to match a single value in field.
But possible to use a seek on multi fields, for example f1, and f1?

....my pseudo code

Code: Select all

1.	   Dim cnn As New ADODB.Connection
2.	   Dim rst As New ADODB.Recordset
3.	 
4.	     cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
6.	      "Data Source=C:\MYDIR\data\ct1.mdb;"
7.	 
8.	  
9.	   rst.Open "tblClubs", cnn, adOpenKeyset, adLockReadOnly, _
10.	      adCmdTableDirect
11.	 
12.	  
13.	   'rst.Index = "F1"
14.	                 rst.Index = Array("f1", "f2")

15.	  
16.	   'rst.Seek AAA, adSeekFirstEQ
  rst.Seek Array(strStore1, strTPC1), adSeekFirstEQ
17.	 
18.	  
19.	   If Not rst.EOF Then
20.	   Debug.Print rst.Fields("area1").Value
21.	   End If
22.	 
23.	   
24.	   rst.Close

Note:
f1 and f2 are indexed.

[HansV]

There must already be an index on the combination of the fields you want to search. You have to specify the name of this index.
For example:

S12.png

When you open a recordset rst on this table, you could use

Code: Select all

    rst.Index = "MyIndex"
    rst.Seek Array(2312, 37), adSeekFirstEQ
    If Not rst.EOF Then ...

[sal21]

There must already be an index on the combination of the fields you want to search. You have to specify the name of this index.
For example:

S12.png

When you open a recordset rst on this table, you could use

Code: Select all

    rst.Index = "MyIndex"
    rst.Seek Array(2312, 37), adSeekFirstEQ
    If Not rst.EOF Then ...

ok maked the index table.
Now, if i have understand 2312 is the refred value for searching in F1, 37 in F2? or not? an donly if the combination of this two value is true the two value existing in foields?
I can use a variable instead a fixed value in array?

[HansV]

Yes, 2312 is the value you're looking for in F1, and 37 is the value you're looking for in F2. A record will only be found if F1=2312 and F2=37.
You can use variables, e.g.

Code: Select all

    Dim MyVar1, MyVar2
    MyVar1 = ...
    MyVar2 = ...
    rst.Index = "MyIndex"
    rst.Seek Array(MyVar1, myVar2), adSeekFirstEQ
    If Not rst.EOF Then ...

[sal21]

Yes, 2312 is the value you're looking for in F1, and 37 is the value you're looking for in F2. A record will only be found if F1=2312 and F2=37.
You can use variables, e.g.

Code: Select all

    Dim MyVar1, MyVar2
    MyVar1 = ...
    MyVar2 = ...
    rst.Index = "MyIndex"
    rst.Seek Array(MyVar1, myVar2), adSeekFirstEQ
    If Not rst.EOF Then ...

very important for me this point!

Tks as usual for explain, Hans,

ops... but is possible to create on fly via vba code the myindex table, with the combination of filed f1 an f2?

[HansV]

Yes:

Code: Select all

    Dim strSQL As String
    strSQL = "CREATE INDEX MyIndex ON MyTable (F1, F2)"
    cnn.Execute strSQL

where cnn is your ADODB connection object.

[sal21]

Yes, 2312 is the value you're looking for in F1, and 37 is the value you're looking for in F2. A record will only be found if F1=2312 and F2=37.
You can use variables, e.g.

Code: Select all

    Dim MyVar1, MyVar2
    MyVar1 = ...
    MyVar2 = ...
    rst.Index = "MyIndex"
    rst.Seek Array(MyVar1, myVar2), adSeekFirstEQ
    If Not rst.EOF Then ...

Hummmm... one of variable is a Date, sa, DATA1 as Date, how to insert into array?

simple:
rst.Seek Array(MyVar1, DATA1 ), adSeekFirstEQ
or
rst.Seek Array(MyVar1, cdate(format(DATA1 ),"mm/dd/yyyy)) adSeekFirstEQ


note:_the field DATA1 have short date property

[HansV]

Try the first one.