ADO.RecordSet.Seek on a MultiIndex Field

[nwb1]

Hi Guys,
Need your help again.

Can anyone let me know how to use RecordSet.Seek method for an multi-field (Access 2000) index please?

My Index is built on 2 fields
(AccountID=Chr & AccountDate=Date type)

Thanks

 

[SemperFiDownUnda]

ADO's meathods don't actually utilise the back end methods to do their seeking, sorting, filtering.

If you want to find a record based on multiple fields I would suggest, most cases, requering the back end. But if you want the exsisting data to stay and just scroll thru it the next option I would do is send a query to the database based on your search critirea and return the primary keys of all the records that match then using that primary key as the field used in the search.

If you have a multi field primary key then...sigh... you have to do it a bit convoluted. Basically use the first field as the .SEEK then manually check the other columns you are trying to match on ... if no match do another seek from that point.


Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[nwb1]

Thanks for your information. I have created another field (Text field to store both field info ie: AccID & AccDate info)
Still I have a problem using this index. I have error saying
"Current provider does not support the necessary interface for index functionally?"

Is this mean we can not use Seek method on any Access table/Field? Or is there something wrong with my coding? Pls. help.

cnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=C:\MyDB.MDB"

dim cn as ADODB.Connection
Set cn = New ADODB.Connection

cn.Open cn

dim rs as ADODB.RecordSet
Set rs = New ADODB.RecordSet

rs.Index = "BothFields"

SrchKey = TxtAccID.Text & DTPIcker1.Value

rs.Seek (SrchKey)

If rs.EOF() then
Msgbox "Not Found"
Else
MsgBox "Found"
Endif

Thanks