Problem with disappearing index in a table

  • Thread starter Thread starter rs0905
  • Start date Start date
R

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
 
rs0905 said:
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
Click to expand...

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"
. . .
 
Back
Top