FindFirst: quick suggestion

[cnlai]

Snippets
--------
Dim mydb As Database, myset As DAO.Recordset

Set mydb = CurrentDb
Set myset = mydb.OpenRecordset("abc")
myset.FindFirst ("[S_SenderID] = 'S02'") '<< problem here

Reference: MS DAO 3.6 Object Library
Question
--------
Why does MS Access 2000 responds that "the operation is not supported
for this type of object" ?
(the method is listed when the recordset name is entered followed by a dot)

Thanks.

[Trevor Best]

cnlai wrote:

Snippets
--------
Dim mydb As Database, myset As DAO.Recordset

Set mydb = CurrentDb
Set myset = mydb.OpenRecordset("abc")
myset.FindFirst ("[S_SenderID] = 'S02'") '<< problem here

Reference: MS DAO 3.6 Object Library
Question
--------
Why does MS Access 2000 responds that "the operation is not supported
for this type of object" ?
(the method is listed when the recordset name is entered followed by a dot)

It's how you opened the recordset, I guess "abc" is a local table so
with no defining parameters on the OpenRecordset method it has defaulted
to a table type recordset, try:

Set myset = mydb.OpenRecordset("abc", dbOpenDynaset)

Then .FindFirst will work.

Hint: FindFirst is the slowest method to get to the record you want, if
you just wanted that one record then you've wasted a load of resources
opening the entire table and used a method to get a record that's barely
better than a sequential scan, instead try:

Set myset = mydb.OpenRecordset("select * from abc where s_senderid='S02'")
if myset.recordcount then...

If you have lots of records to go to, then consider opening a query that
contains just the ones you want or look into the .Index and .Seek
methods for table type recordsets, .Seek is the fastest access method
bar none, if .FindFirst was a sloth, .Seek is a tachyon
(http://dictionary.reference.com/search?q=tachyon) but with the
following caveats:

1. Only works on Access tables
2. Only works on table type recordsets, which you cannot open on linked
Access tables but you can get around that using OpenDatabase to get at
the back-end tables directly.
3. Due to the first two, any code written to take advantage of this
method is not portable, a major re-write would be necessary to upgrade
to SQL Server for instance.
--
This sig left intentionally blank