Error 3251 Operation is not supported

[laura]

I am receiving Error 3251 - "Operation is not supported for this type of
object"

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblEvents")
rs.Index = "EventNo" ' the name of the field which is indexed. This
is where the problem is - I get the above Error Message 3251.

I think that when I originally created the database on Windows 98, Office
2000 it worked. Now I am using Windows XP I get the error message.

I have made sure that Track Name AutoCorrect Info is NOT checked.

I am not sure what 'References' I should have, but this is what I have
selected (checked):-

Visual Basic for Applications
MS Access 9.0 Object Library
OLE Automation
MS DAO 3.6 Object Library
MS Visual Basic for Applications Extensibility 5.3

Any help appreciated as I'm stumped.

Laura TD

 

[Allen Browne]

If tblEvents is a *local* table, then rs will be a recordset of type
dbOpenTable, and you will be able to use the Index. If this is the case,
open the table in design view, and then open the Indexes box (View menu).
You may find that the index name is not the same as the field name. For
example, the index might be called "PrimaryKey", or EventID if that was the
original name of the field.

If tblEvents is an *attached* table, rs will be of type dbOpenDynaset, and
you cannot use an index with that type. Because most non-trivial Access
databases end up being split, most of us don't use the Index in this way.
Instead, use a SQL statement that returns only the records and fields that
you actually need, sorted as desired. Rushmore will use the indexes
automatically.

If you absolutely must use the Index on an attached table, you can
OpenDatabase directly on the back end, and then OpenRecordset on that
database variable.

 

[laura]

Dear Allen,

The database was indeed split a few months ago - it would never have
occurred to me that that would have been why it worked once and now not
again.

Please forgive my ignorance - I was indeed trying to locate one record in a
link table in order to delete it. The criteria had to be the EventID. The
only way I knew how to locate the record was using the code I quoted below
including

rs.Seek "=", Me.EventID
rs.Delete

I can create a delete query using the query builder which will do the job,
but I am sure there must be a way of using SQL, as you say, in my VB code.
Would I still use:-

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblEvents")

I am not sure how to do this without the Query Builder.

Your patience is appreciated, still always so much to learn.
Laura TD

 

[Allen Browne]

Do delete the record where EventID is 999, you could mock up a delete query,
switch it to SQL View (View menu when in Query design), and copy what you
see there into a string in your code. Then Execute the query string.

This kind of thing:
Dim strSql As String
strSql = "DELETE FROM tblEvent WHERE EventID = 999;"
dbEngine(0)(0).Execute strSql, dbFailOnError

 

[laura]

Allen,

I am trying... I get "424 Object required" Error message. Do I need to
Open or Declare the table somehow before I can delete?

Laura TD

 

[Allen Browne]

Replace "tblEvent" with the actual name of your table.

You may need a reference to the DAO library. See:
http://members.iinet.net.au/~allenbrowne/ser-38.html
That reference is automatically there in all versions of Access except for
2000 and 2002.

 

[laura]

Allen,

Once again many thanks - it works. The problem may have been in my SQL
Statement which I had to modify a little as I had to check on two variables
before I could actually delete the record, EventID and MemberID.

I've had a sort of mental block about using SQL in the VBA code as I was not
sure if I could reference tables directly or whether I had to always create
recordsets. I will read up on DAO a bit more too and follow your link that
you recommended.

Thanks for your great support as always.
Laura TD