Access/SQL Server referencing recordset index - error

[emuti]

Hi,
Does anyone know of a way of referencing an index using ADO? I have a front-end Access datbase , with SQL Server 2000 back-end tables. I'm using the following, rstCaleaADO.Index = "Master"

See snapshot of code:

Set conCaleaADO = New ADODB.Connection
With conCaleaADO
.ConnectionString "Provider=SQLOLEDB;_
Server=Snowhite\DDSQLSERVER2000;UID=sa;PWD=christy;Database=CALEATrackDev;"

.Open

End With

Set rstCaleaADO = New ADODB.Recordset

rstCaleaADO.Open "FEE", conCaleaADO, adOpenKeyset, adLockOptimistic

rstCaleaADO.Index = "Master"


I keep getting an error message saying that it is not supported. Is there any way of getting around this?

Thanks in advance.

 

[tlbroadbent]

Could you explain why you want to reference an index?

 

[emuti]

I need to reference the index, since it appears significantly faster than using the "ORDER BY". Basically what the procedure does is assign a number to a record according to the order of 3 fields. I was using it in the Access database before we decided to use SQL Server tables as the back-end.

Thanks.

 

[tlbroadbent]

As I understand it, you want to open the table and use an index to order the data in the resulting record set. That may work OK in Access but in SQL Server the Order By clause should be much faster. Why do you believe Order By is slower?

 

[emuti]

I used the ORDER BY clause, and compared to the original way I had it, with Access, it took 1 second, vs. 1 minute using SQL server. Any ideas? Thanks.