Seek Method in Access

[comart]

This code is not working. Any suggestions?

Dim cnBiblio As Connection
Dim rsAuthors As New Recordset
Dim mycommand As Command
Set cnBiblio = New Connection
Set mycommand = New Command

cnBiblio.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = c:\Program Files\Microsoft Visual Studio\VB98\Biblio.mdb"
mycommand.CommandText = "Create Index MyIndex on Authors(author)"
'Set rsAuthors = New Recordset
Set rsAuthors = mycommand.Execute
rsAuthors.CursorType = adOpenKeyset
rsAuthors.LockType = adLockOptimistic
rsAuthors.CursorLocation = 3
rsAuthors.Open "Authors", cnBiblio, , , adCmdTableDirect
MsgBox rsAuthors.Supports(adIndex)
rsAuthors.Index = "MyIndex"

rsAuthors.Seek "Author = 'Boddie, John'", adSeekFirstEQ

[phunkydude]

After you've created the Index try closing and destroying the recordset. Then instantiate it again, populate it and see if it works then.

Also, try only provide then criteria value when calling the seek method
rsAuthors.seek "Boddie,John"



Hope that helps.

[comart]

It still doesn't work. I think the Seek method just won't work with Microsoft Access.

[phunkydude]

you don't tell me where it falls over, sorry I can't help you.

[dfwade]

Apparently, after doing further research on this one you need to check your provider for MsgBox rsAuthors.Supports(adSeek). The seek method also requires that you use a Server Side Cursor. It turns out that the Jet OLEDB 4.0 and 3.51 and the ODBC providers do not support the seek method. To call the Seek method in your scenario you would have to call it like this:
rsAuthors.Seek array("Boddie", "John"), adSeekFirstEQ

[comart]

rsAuthors.Seek array("Boddie", "John"), adSeekFirstEQ (This code doesn't work in my app because I'm using Jet OLEDB 4.0).

Thanks for researching this. It is good to know why it is failing. I have a tendency to NOT give up trying until I know for sure that something isn't going to work.

[dfwade]

A further comment on this code, when you open a table which is a requirement of the seek command every row and every column of the data is returned each and every time you open the table. This will severely impact performance if you are running this frequently. I would suggest that you look at the find command especailly since the seek command is not supported. Even when using the find command, I would select my data as
SELECT Author, Au_ID FROM Authors ORDER BY Author
This will return the minimun of data. When a user selects an author to work on I would run another query like
SELECT Author, Au_ID, [Year Born] FROM Authors WHERE Au_ID=1

You would already have the Au_id from the previous query.

Hope this helps

[comart]

Great advise. Thanks so much!