Solved Indexes and Seek

Dumferling

Member
Local time
Today, 02:05
Joined
Apr 28, 2020
Messages
102
I am trying to locate and then delete a record based on two criteria. For this, I understand I need to use Seek. For Seek to work, I need to define an index. When I run the code though I get "Operation not supported by this type of object" when I hit the line that defines the index. The table is native, not external. The code examples seem to indicate that this is the correct code but it still doesn't work (Access 365). The code is:
Dim dbsLMD As Database
Dim rstLink As Recordset
Set dbsLMD = CurrentDb
Set rstLink = dbsLMD.OpenRecordset("tblCPList")


With rstLink
' Set the index.
rstLink.Index = "ID"

What am I missing to make the index supported?
 
you must open your recordset using dbOpenTable as second parameter.
the Index is the Name of the index not the field where you Index it.

if your table is a Linked table, you need to Open the Source database first
and use that db to open your recodset:

Dim db As DAO.Database
Dim rs As DAO.Recordset

set db = OpenDatabase("ExternaldbPathAndName, False, False)
set rs = db.OpenRecordset("tblNameHere", dbOpenTable)
rs.Index = "TheIndexName"

also, why are you using recordset when you can use a Query
to update/retrieve the value from your table.
 
Last edited:
I am trying to locate and then delete a record based on two criteria. For this, I understand I need to use Seek. For Seek to work, I need to define an index. When I run the code though I get "Operation not supported by this type of object" when I hit the line that defines the index. The table is native, not external. The code examples seem to indicate that this is the correct code but it still doesn't work (Access 365). The code is:
Dim dbsLMD As Database
Dim rstLink As Recordset
Set dbsLMD = CurrentDb
Set rstLink = dbsLMD.OpenRecordset("tblCPList")


With rstLink
' Set the index.
rstLink.Index = "ID"

What am I missing to make the index supported?
I tried that "Set rstLink = dbsLMD.OpenRecordset("tblCPList", dbOpenTable)" but it gives an error 3219 - Invalid Operation.
 
you must open your recordset using dbOpenTable as second parameter.
the Index is the Name of the index not the field where you Index it.

if your table is a Linked table, you need to Open the Source database first
and use that db to open your recodset:

Dim db As DAO.Database
Dim rs As DAO.Recordset

set db = OpenDatabase("ExternaldbPathAndName, False, False)
set rs = db.OpenRecordset("tblNameHere", dbOpenTable)
rs.Index = "TheIndexName"

also, why are you using recordset when you can use a Query
to update/retrieve the value from your table.
Using a recordset because it picks up criteria from different columns in listboxes and I don't know if you can use a variable in a SQL statement. The Seek statement looked fairly straightforward until now
 
the table a Linked (MS Access table) table?
you need to open the parent database of this link table.
 
Just create a dynamic sql String and execute it?

Code:
strSQL = "DELETE * FROM MyTable WHERE MyID = " & Me.MyIDControl  & " And MyOtherCriteria = " & Me.Lstbox2
debug.print strSQL
currentdb.execute strSQL

The above assumes both criteria are numbers.
Adjust if necessary if the are text values.
 
you must open your recordset using dbOpenTable as second parameter.
the Index is the Name of the index not the field where you Index it.

if your table is a Linked table, you need to Open the Source database first
and use that db to open your recodset:

Dim db As DAO.Database
Dim rs As DAO.Recordset

set db = OpenDatabase("ExternaldbPathAndName, False, False)
set rs = db.OpenRecordset("tblNameHere", dbOpenTable)
rs.Index = "TheIndexName"

also, why are you using recordset when you can use a Query
to update/retrieve the value from your table.
In the end I with with Find and Find Next and a bunch of if statements rather than try to sort the Seek out. The tables were native and not external and I couldn't see why it didn't work. Current solution is maybe a bit slower but it isn't a big set of records so works fine. Thanks for the help!
 
you must open your recordset using dbOpenTable as second parameter.
the Index is the Name of the index not the field where you Index it.

if your table is a Linked table, you need to Open the Source database first
and use that db to open your recodset:

Dim db As DAO.Database
Dim rs As DAO.Recordset

set db = OpenDatabase("ExternaldbPathAndName, False, False)
set rs = db.OpenRecordset("tblNameHere", dbOpenTable)
rs.Index = "TheIndexName"

also, why are you using recordset when you can use a Query
to update/retrieve the value from your table.
Because recordsets typically make use of listbox columns to collect criteria, they are preferable to variables when writing SQL statements. Up until this moment, the Seek statement appeared to be quite straightforward.
 
You can't use seek on a linked table, just findfirst and findnext.
 

Users who are viewing this thread

Back
Top Bottom