Run-time error '3251' index problem ?

[SheldonMopes]

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.

 

[Allen Browne]

For a local table, OpenRecordset() defaults to the dbOpenTable type. For an
attached table, it defaults to dbOpenDynaset, and dbOpenTable is not
available. The Seek method can only be used with a dbOpenTable type
recordset, i.e. you cannot use this with an attached table.

So, even though Seek is very fast, many of us have given up using it.
Although it is possible to OpenDatabase directly on the back end file and
use Seek, it is usually much simpler to use a SQL statement to OpenRecordset
on only the records and fields you actually want, sorted the way you need.

You will therefore code something like this:
Dim strSql As String
strSql = "SELECT SegmentID, LinkID, SegmentName FROM tblSegments WHERE
SegmentID = " & nlDvariable & " ORDER BY LinkID;"
Set rst1 = Currentdb.OpenRecordset(strSql)