How to use ADO & Index

I want to use SEEK method to search in my table. I have an index and I know that SEEK will work faster than FIND. But I don't know why I can't set the index. The recordset defined in my program doesn't support INDEX property and I don't know what kind of recordset to use for this.

This is what I have now:

Set rsStoc = New ADODB.Recordset
With rsStoc
.CursorLocation = adUseClient
.CursorType = adCmdTable
.LockType = adLockBatchOptimistic
End With

I tried to open the recorset in 2 ways:
rsStoc.Open "Stoc", myConnection
or
rsStoc.Open "SELECT * FROM stoc", myConnection

but in both cases I've got an error when I did:

rsStoc.Index = "ID"

Is anybody here to tell me how to open this table?

a question for you, Anca. Did you put an index in the table as well. If not, then assigning an index in the recordset will cause a failure

If it helps:

http://msdn.microsoft.com/library/e...mdapro03_12.asp

Yes, I have an index on this table. I created it when I created the table. I guess it's something with the way I am opening the table.

That link, from Doug, doesn't say much about the cause of the error.

Maybe is somebody there who can send me a small example - a working one?

Is that any chance that "Microsoft.Jet.OLEDB.4.0" as provider doesn't support indexes? In this case which should be the provider for my MDB file?

I don't believe that the JET provider would have any conflicts there. Can you post the code where you are opening the table?

The code is on the first message of this thread. I found something on Doug's link - there is a good example - but I still don't understand why mine isn't work. I need to have write access to my table and I would like to keep that "batch" mode because looks to be very useful sometimes.

Does anybody know a link where I can find details about all parameters and related constants from OPEN method of ADODB.recordset? I want to understand them, not only find something that works.

you can use an if statement to check for support of the index property like so (I got this off of DevGuru)

Here's a link to the information you asked for. Try using a server-side cursor instead of a client side one. I don't know if that will have any bearing or not, but I'm just curious as to the outcome.

I know that and I tested it.... it doesn't support index.... the big question is "why"??

From your experience - what is the better way to open a table if i want to have full access to change it?
- I have network and other users may want to do the same maybe
- I want to be able to edit record, AddNew or Delete
- I would like to be able to "cancel" some things like AddNew or edit

Thanks

if you are wanting to open the table for editing, and other users may be editing the same table at the same time, you will most likely want to use either adLockOptimistic or adLockBatchOptimistic. Usually, people go for the first one, because most programmers don't know when to use batch-type processing. Personally, as a generality and not knowing the ins and outs of your particular situation, I would say that a CursorType of adOpenDynamic, a CursorLocation of adUseServer, and a LockType of adLockOptimistic might work best. That's not based on an informed decision, though. One thing I would tell you, though - unless you have reason to open an entire table at a time, I would recommend you select specific records for processing using Select SQL statements based on information gained somewhere else in your program.