A Problem with .index property with a linked table

[accesser2003]

I have a linked table to a SQL server table called AllAttendanceEvents.
the AllAttendanceEvents table has index called "AttEvents". I wrote the following code:

set AllAttendanceEvents=currentdb.openrecordset("AllAttendanceEvents")
AllAttendanceEvents.movefirst
AllAttendanceEvents.index="AttEvents"

When it executes the read line statement, it generates the following error:
Operation is not supported for this type of object

Is there any way to get around of this problem with the linked tables?

 

[RoyVidar]

What you're probably doing, is preparing to use the .Seek method, right?

This isn't available on ODBC linked tables, I think. I suggest to rewrite using the recordset find methods (.FindFirst ...).

 

[accesser2003]

I am using .index just to sort the field called Entrytime Desc.
I use this to save the time

So I dont use it to seek only to sort the table. Do you think because it is a read only database, and this error will not appear if I open the database in a read/write mode, using the statement:

set MyDb=dbengine.workspace(0).opendatabase("",,false,"ODBC;DSN=''')

Do you think this will work?

Thanks,

 

[RoyVidar]

I don't know, but I think I'd try opening a recordset based on an sql string with an ORDER BY clause, or test whether usage of the the recordsets .Sort property suits. There's seldom any good reasons for opening a whole table anyway, using a WHERE clause to restrict the actual returned records is more usual.

Perhaps someone else will chime in?

 

[pbaldy]

I'll chime in, but I don't know what else to say. I also use SQL Server, and I never use the .Index with recordsets. As Roy suggests, I typically open recordsets with a WHERE clause that restricts the records, and if appropriate an ORDER BY clause to put them in the order I need.