A2K Run-time error 3251, index problem ?

[SheldonMopes]

Sorry for the repost, forgot to mention: version is A2K
I have the following segment in my code..



Set rst1 = CurrentDb.OpenRecordset("tblSegments")
rst1.Index = "nLinkID" ' the debugger points to this line
rst1.Seek "=", nIDvariable

It works fine and gives the result I want, until I split the DB and
move the tables to a back-end file. When the tables are linked, I get
the following message:

Run-time error '3251'
Operation is not supported for this type of object

What am I missing ? If I don't have the tables linked, everything is
fine. But when I link to a back end DB, (same tables that were in the
single .mdb file), I get that error message. When I look at the
indexes they are visible in both the _be file and the front end
(linked of course). This is causing big problems for me, any help
would be greatly appreciated. Thanks.

 

[Marshall Barton]

Set rst1 = CurrentDb.OpenRecordset("tblSegments")
rst1.Index = "nLinkID" ' the debugger points to this line
rst1.Seek "=", nIDvariable

It works fine and gives the result I want, until I split the DB and
move the tables to a back-end file. When the tables are linked, I get
the following message:

Run-time error '3251'
Operation is not supported for this type of object

What am I missing ? If I don't have the tables linked, everything is
fine. But when I link to a back end DB, (same tables that were in the
single .mdb file), I get that error message. When I look at the
indexes they are visible in both the _be file and the front end
(linked of course).

Only table type recordsets can use the Index property and a
Linked table can not be opened as a table type recordset.

With a DAO dynaset type recordset, you can use the FindFirst
method instead (which is more flexible, but a little slower
than Index and Seek):
rst1.FindFirst "nLinkID = " & nIDvariable

If you absolutely must have the Index/Seek speed advantage,
then open the back end database and open a recordset from
that database. You can operate on this recordset as a table
type recordset:

Dim dbBE as Database
With CurrentDb.TableDefs!tblSegments
Set dbBE = OpenDatabase(Mid(.Connect,11))
Set rst1 = dbBE.OpenRecordset(.SourceTableName, dbOpenTable)
End With
rst1.Index = "nLinkID"
rst1.Seek "=", nIDvariable
. . .
rst1.Close : Set rst1 = Nothing
dbBE.Close : Set dbBE = Nothing