Problem with disappearing index in a table

[rs0905]

I have a table that I use as a recordset in my code. The table is a linked
table. There is a Primary Key / Index defined in the table, but once I
access the recordset, the index "disappears" and I receive an error (method
doesn't support object). Here is the code:

set db = CurrentDB
set rs = db.OpenRecordset("tblData")
'''I stopped the code here to look at the design view of the table, and
the
'''Primary Key and Index property appeared correctly

With rs
.Index = "PrimaryKey"
'''This is where I get the error. When I look at the table at this
point, the
'''Primary Key is gone and the Index property is set to No

 

[Marshall Barton]

I have a table that I use as a recordset in my code. The table is a linked
table. There is a Primary Key / Index defined in the table, but once I
access the recordset, the index "disappears" and I receive an error (method
doesn't support object). Here is the code:

set db = CurrentDB
set rs = db.OpenRecordset("tblData")
'''I stopped the code here to look at the design view of the table, and
the
'''Primary Key and Index property appeared correctly

With rs
.Index = "PrimaryKey"
'''This is where I get the error. When I look at the table at this
point, the
'''Primary Key is gone and the Index property is set to No

Index and Seek only apply to Table type recordsets. Since
tblData is a linked table, your recordset is a Dynaset. You
can use FindFirst instead or, if the speed of Seek is
critical, open the backend database and then the recordset:

With db.TableDefs!tblData
BEpath = Mid(.Connect, 11)
Set BEdb = OpenDatabase(BEpath)
Set rs = BEdb.OpenRecordset(.SourceTable, dbOpenTable)
End With

With rs
.Index = "PrimaryKey"
. . .