RecordSets and Indexes, etc.

[Shep]

I am attempting to get comfortable with the Seek Method.

The sample for the Northwind database uses this code (much simplified):

Dim dbs as Database
Dim rst as Recordset
Set dbs=OpenDatabase(path to Northwind database)
set rst=dbs.OpenRecordset("Customers")
rst.Index = "LastName"
Do While Not rst.EOF
MsgBox !LastName
rst.MoveNext
Loop
rst.Close
dbs.Close

This works fine but I have a couple questions.

If I Set dbs = CurrentDb and open a table-type Recordset, it doesn't work (Operation not supported for this type of Object).

If I Set dbs = OpenDatabase(my database), then open a table-type RecordSet, it does work.

Why can I not simply use CurrentDb as the database?

Also:
If I mimic the sample code, and 'Open' my database, it works fine.
I am having trouble understanding why I must 'Open' a database file for this to work.

AND...
I have found that some of my 'Indexed' fields issue an error stating that 'this field is not a member of the Indexes Collection', and that some work fine.

When creating a table, do not the fields that you 'Index' become members of the Indexes Collection?

What gives?

Confusion...blah

 

[The_Doc_Man]

Did you convert a database to get to the point where you are?

Like, convert from AC97 to AC2K or something like that?

Because I open recordsets based on CurrentDB as my starting point all of the time. But in AC2K vs. AC97, you might have to qualify it a bit...

Dim dbs as DAO.Database

This one seems to rear its ugly head a lot. You could also try to change the order of your references so that DAO is above ADO (or vice versa...)

 

[Shep]

I am working in 97, and the database was created in 97.

I use CurrentDb as the base for Recordsets all the time too...but attempting to declare the .Index is when I get the message here.

I will play with the order of references and try it again.

Thanks a lot.

 

[Shep]

Well, the only references I list are:

Visual Basic for Applications
Access 8.0 Object Library
Microsoft DAO 3.51 Object Library

The DAO 3.51 reference cannot be moved any higher in priority.

Doc Man, do you often use Seek method, or do you rely upon Findfirst...or do you simply open Recordsets which are filtered already?

I wanted to get familiar with Seek because it seems like such a nice way to filter the exact record one might want to work with.

Shep

 

[Shep]

From Access help:
You can't use the Seek method on a linked table because you can't open linked tables as table-type Recordset objects. However, if you use the OpenDatabase method to directly open an installable ISAM (non-ODBC) database, you can use Seek on tables in that database.

Never mind...I was attempting to Seek on my backend (linked) database. Sheesh.

That leaves me with one question.

When you create a table and set the Indexed property to 'Yes' on one or more fields, shouldn't those fields become members of the Indexes collection?

I tried to use the primary key (it's an autonumber and certainly 'indexed') as an index and I'm told it's not a member of the Indexes collection.

Some other ('Indexed') fields DO work...but not all of them.

 

[Shep]

I have found the cause of my problem, for any who might be interested.

Most of the tables in my database were imported. Of course, all baggage comes along with imported objects.

I understand now why I saw such craziness when enumerating the Indexes Collection. I saw index names with spaces; some I recognized as old field names in the source tables, some as current indexed fields, others I did not recognize at all.

I did not realize there was an 'Indexes' button on the toolbar, nor that indexes may be named arbitrarily.

Now, if I can figure out how mulitple primary keys fit into this picture, the confusion (which I dislike) will subside.

 

[Shep]

Index craziness continued...

Working in the backend here now...

Those old Indexes cannot be removed in the Index design window[thing]. I would assume that this is primarily because they can't be seen there.

A data definition query (DROP INDEX...etc...) was also unable to remove them ('...is the primary key or is used in a relationship...').

None were the primary key, nor were any of them part of any relationships.

An ALTER TABLE...DROP CONSTRAINT data definition query succeeded in removing them. As I understand it, a Contraint is more or less a Relationship. Yet, these are listed in the Indexes Collection. Confusion...

After all, I decided to create new tables and throw those imported tables as far away as possible.

Interesting observations:

When adding fields to a table in Table Design view...if you begin or end the field name with the letters 'id', Access automatically creates an Index for that field, no matter the data type.

If you later designate one of the fields beginning or ending with the letters 'id' as the Primary Key, Access adds an additional (likely unnecessary) Index. I suppose it is best to add all your fields except for the primary key field, let Access create it, then rename and configure it.

If you rename a field for which there is an existing Index, Access retains the Index and retains the original Index name as well.

As one who dislikes confusion (did I mention that?), I shall no longer import tables unless necessary.

I hope this exercise helps others, as it did myself.

Shep