Index with descending field

[Guest]

I am trying to create an index with four fields, one of which is descending.
It appears to have something to do with the "Attribute" of the field, and
there is a value called dbDescending.

Set rnindex = db.tabledefs(ofile).createIndex("NormOrd")
With rnindex
.Fields.Append .createfield("Zone")
.Fields.Append .createfield("Dist")
.Fields.Append .createfield("Node")
.Fields.Append .createfield("Mode")
End With

db.tabledefs(ofile).Indexes.Append rnindex
db.tabledefs(ofile).Indexes.Refresh

I cannot set the attribute of the field "Mode". I get an "Invalid Operation"

The Help for Attributes states that for a field in an index object, it is:

Read/write until the TableDef object that the Index object is appended to is
appended to a Database object; then the property is read-only.

It seems I am trying to do this before appending.

I must be missing something. Any suggestions?

Thanks.

 

[Marshall Barton]

I am trying to create an index with four fields, one of which is descending.
It appears to have something to do with the "Attribute" of the field, and
there is a value called dbDescending.

Set rnindex = db.tabledefs(ofile).createIndex("NormOrd")
With rnindex
.Fields.Append .createfield("Zone")
.Fields.Append .createfield("Dist")
.Fields.Append .createfield("Node")
.Fields.Append .createfield("Mode")
End With

db.tabledefs(ofile).Indexes.Append rnindex
db.tabledefs(ofile).Indexes.Refresh

I cannot set the attribute of the field "Mode". I get an "Invalid Operation"

The Help for Attributes states that for a field in an index object, it is:

Read/write until the TableDef object that the Index object is appended to is
appended to a Database object; then the property is read-only.

It seems I am trying to do this before appending.

I must be missing something. Any suggestions?

You're looking at Help for a Field object in a TableDef, but
you're trying to manipulate a Field object in an Index.
They're not at all the same thing and an index's field does
not have an Attributes property.

 

[Guest]

just click on indexes. in icon bar next to lightning bolt primary key. you
can sleect a multiple field index, and decide whether each element is
ascending/descending

 

[Marshall Barton]

Man, am I all wet. Sheesh. Sorry about wasting your time.

Still, looking at your code, you are referring to
db.tabledefs(ofile)... This clearly implies that the
TableDef object has been appended to the TableDefs
collection. So, according to Help as you quoted, the
Attributes property is read only. In my mind, this makes no
sense, so I had a play with the scenario where the TableDef
already exists.

Fortunately, that Help seems to be inaccurate. I think the
restriction is that the Attribute property is read/write
until the field object is appended to the index object.

Try this:
. . .
Set fld = .CreateField("Node")
fld.Attributes = dbDescending
.Fields.Append fld
. . .

 

[Guest]

Marsh--

Thanks. That did the trick. I did have a little trouble with the
dbDescending variable, but that was because I didn't have a reference to DAO
3.0.6 set. I just used a 1 instead, and that worked. (When I added the DAO
reference, I checked and found dbDescending had a value of 1).

Thanks again.

------

Eddy

Thanks also for your reply. I wanted to keep this automated and use the VBA
scripts. However, after running the modified program based on Marsh's
comments, I did do what you suggested, and found the program had added that
index properly.

 

[Marshall Barton]

DAO 3.0???

What version of Access are you using? That version of DAO
is what, 10 years old (Access 97 used DAO 3.5).

 

[Guest]

Marsh--

Sorry, it's DAO 3.6 with Access 2002.

 

[Marshall Barton]

Marsh--

Sorry, it's DAO 3.6 with Access 2002.

Whew, that's a relief. For a while there I was trying to
figure out how you got it to work at all.

Anyway, problem's solved, so it's all good ;-)