how to work with an index in a module?

  • Thread starter Thread starter Mr. T
  • Start date Start date
M

Mr. T

Hi,

how can i programmatically set my table to be in a certain order determined
by an index?

The index in this case isn't the Primary Key, but an index on another field.
The purpose is merely to move to the previous or next record.

In the help i found the Index attribute, but the provided sample didn't
really enlighten me.

Can anyone give me a clue as to how to obtain this?

Regards,

Mr. T
 
I'm going to assume we're talking about the DAO Index property here, as this
is the DAO newsgroup (ADO has an Index property too).

If you look at the Employees table from the Northwind sample database in
design view, and choose Indexes from the View menu, you'll see that it has
three indexes, LastName, PostalCode, and PrimaryKey, each index using one
field, LastName, PostalCode, and EmployeeID, respectively. The following
example will Debug.Print the names and postal codes from this table, first
using the LastName index, then using the PostalCode index ...

Public Sub DemoIndex()

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Employees", dbOpenTable)
With rst
.Index = "LastName"
.MoveFirst
Do Until .EOF
Debug.Print !LastName, !FirstName, !PostalCode
.MoveNext
Loop
Debug.Print
.Index = "PostalCode"
.MoveFirst
Do Until .EOF
Debug.Print !LastName, !FirstName, !PostalCode
.MoveNext
Loop
.Close
End With

End Sub

Note that we could have acheived the same result by opening the recordset
using a SQL Statement with an ORDER BY clause ...

Set rst = db.OpenRecordset("SELECT LastName, FirstName, PostalCode FROM
Employees ORDER BY LastName")

.... and that this may be more efficient.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Mr. T said:
how can i programmatically set my table to be in a certain order determined
by an index?

The index in this case isn't the Primary Key, but an index on another field.
The purpose is merely to move to the previous or next record.

In the help i found the Index attribute, but the provided sample didn't
really enlighten me.
Click to expand...


You can not "set a table to be in a certain order".
However, that's probably irrelevant because you can set the
order of the data when you retrieve it. The most common way
is to open a recordset (or a form, combo/list box, etc) by
using a query instead of just the table.

strSQL = "SELECT table.* FROM table ORDER BY field"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

For a recordset opened with dbOpenTable you can also set the
recordset's Index property which will determine the order of
the records in the recordset:

Set rs = db.OpenRecordset("table", dbOpenTable)
rs.Index "nameofindex"
 
Back
Top