ADO - SQL Server - Index - Seek 1

[baugie]

I am using ADO recordsets to access a DB using an OLE DB connection string. The DB is a SQL Server 2000 and it has an Index call IX_FieldIndex. Here is the sample code

Set rstFD = New ADODB.Recordset
rstFD.ActiveConnection = gvCnn
rstFD.LockType = adLockReadOnly
rstFD.CursorType = adOpenKeyset
rstFD.CursorLocation = adUseServer
rstFD.Source = "tblFieldSysSettings"
rstFD.Open Options:=adCmdTableDirect

rstFD.Index = "IX_FieldIndex"
rstFD.Requery

I am getting an error stating that:
Current provider does not support the necessary interface for Index functionality.

I have used this code for Access DBs, but never SQL Server.

I have heard rumors that SQL Server doesn't support the Index-Seek Method. Is this true or am I doing something incorrectly? I have access to the SQL Server so I can adjust the Index properties if that is the problem.

Thanks in advance for the help!

baugie

 

[CCLINT]

To test if a provider, and the recordset, supports this use:

Debug.Print rstFD.Supports(adIndex) And rstFD.Supports(adSeek)

 

[baugie]

Thanks for the reply CCLINT....

however, I have tried this, and I know that it is not supported. I am trying to find out what provider that I can use that does support the Index - Seek Method. I am currently using the SQLOLEDB connection, but have also tried a MSDASQL and a DSN connection. Neither seemed to work.

So my question is...

Is there a way to connect to a SQL Server 2000 that allows for the Index-Seek Method?

Thanks if advance for your help!

baugie

 

[CID8]

No i don't believe there is, microsft recommend using the find method instead.

cid8

 

[baugie]

Thanks for the info CID8...You re-affirmed some tidbits of info. that I had found.

Does anybody else think it is a joke that Microsoft would not support their own functionality? Cheap old Access can support indexed seek methods, but SQL Server can't! Nice one Bill.

Thanks all!

 

[CID8]

Have you checked whether ADO2.8 which has recently been released has fixed this?

CID8