I'm starting database programming and I would like to know what is the fastest
way to search a large database for some record field value?

I know of at least three ways to do searches:
1. Recordset.Find method
2. SQL SELECT-FROM-WHERE-LIKE command
3. Manually searching all records using While Wend loop


What is the fastest way to do it?

The database is optimized for search thru its data so #2 is definately what I'd use. #3 is the worst.

Thanks, if I wanted to get the index of the found record, it is always stored in Recordset.AbsolutePosition? There's also something
called CursorLocation, but that value doesn't seem to change.

I've never used AbsolutePosition so I can't say what it does.

CursorPosition has to do with where the record/row cursor lives (client or server). It has to do with how the data is navigated. I'm not real sure about how it works or what different ways to use it. Visit http://www.adopenstatic.com for more. The guy that runs that site is on here occassionaly and he's much more knowledgable than me regarding the functioning of the ADODB objects.

Peter
------------------------------------------------------
Work smarter, not harder.

AbsolutePosition is often used in combination or navigation. It allows you to jump to a specific record in the recordset.

Say you are paging, and have defined a page size of 10 records. The user requests page 3. The following code would prepare the recordset to output the records for that page:

MyRecordset.AbsolutePosition = 3 * MyPageSize + 1

If MyPageSize = 10, this would position the recordset at record 31, so you can display records 31 through 40 on the page.

CursorLocation has indeed to do with where the cursor lives. With a client side cursor, basically the server takes care of navigating the data. Som with a CursorLocation of adUseServer, setting the AbsolutePosition to 250 would mean that no records prior to 250 are sent back to the client. The server sets the position at the required record. Any call to MoveNext would return a record to the client.

With a client side cursor (adUseClient) *all* records are transferred to the client. So, setting AbsolutePosition to 250 would also bring over the first 249 records. Performance wise, a client side cursor is slower than a server side cursor. However, in certain scenario's a client side cursor can do things that a server side cursor can't (disconnecting the recordset from the connection for example).

So, when you are using paging, it's usually better to use a server side cursor. Say that you have 1000 records returned from a query, but you only want to show 10 at the time. A client cursor would retrieve all 1000 records for each page every time, while the server side cursor would only retrieve the requested 10 records.

Does this help?

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Well, I was asking more if the AbsolutePosition or CursorLocation value was
automatically updated after a search? I'm not setting it to anything.

I don't think so, or it is reset to 0 or -1.

Why don't you try it out? AFAIK, it's a read/write property, so you could do something like this:

' Search Here
Response.Write(rs.AbsolutePosition)

But why bother with AbsolutePosition if you're not going to set it?

The cursor location is used for the entire life-span of the recordset. You can't change the cursor location half way down the road, AFAIK.

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

I'm using it because I'm reading it. After I do a search, I read it. Then I use it
as an index into an array. I was just wondering if this is a safe practice, that is
to say, AbsolutePosition is always valid.

It depends.

AFAIK, it will always be valid, but it may return a useless number.
For example, right after you have executed a SELECT statement against a connection, it will be -1. That makes it valid, but not useful as an array index.

Also, not all recordsets support it. Fire hose cursors (read-only, forward only recordsets) won't allow you to change the AbsolutePosition.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

quote from ADO documentation: