check index "Sort Order"

[Solaris]

Dear Expert
I want to check "SORT ORDER" of a specific Table's Index.
I write this code :

Sub acc2010_11()
Dim result As Boolean
result = False
Dim i As Integer
For i = 0 To CurrentDb.TableDefs("Services").Indexes.Count - 1
If UCase(CurrentDb.TableDefs("Services").Indexes(i).Name) = "MFT" Then
If Not CurrentDb.TableDefs("Services").Indexes(i).Primary Then
result = CurrentDb.TableDefs("Services").Indexes(i).Fields = "+ServiceName"
MsgBox CurrentDb.TableDefs("Services").Indexes(i).Properties("sort")
End If
End If
Next i
MsgBox result
End Sub

but this line has a error:
MsgBox CurrentDb.TableDefs("Services").Indexes(i).Properties("sort")

any solution available for showing SordOrder of a Index?

 

[vbaInet]

I don't think this is available with DAO. Perhaps you will find this property in the ADOX Catalog.

 

[Solaris]

[SOLVED]

When set Index Ascending a PLUS sign (+) apear before field name.

Sub acc2010_11()
Dim result As Boolean
result = False
Dim i As Integer
For i = 0 To CurrentDb.TableDefs("Services").Indexes.Count - 1
If UCase(CurrentDb.TableDefs("Services").Indexes(i).Name) = "MFT" Then
If Not CurrentDb.TableDefs("Services").Indexes(i).Primary Then
result = CurrentDb.TableDefs("Services").Indexes(i).Fields = "+ServiceName"
End If
End If
Next i
MsgBox result
End Sub

 

[vbaInet]

Good job! Then for DESC a minus precedes the name.

I've never needed to know this property value. Why are you needing this information?

 

[gemma-the-husky]

I don't think this is available with DAO. Perhaps you will find this property in the ADOX Catalog.

I think it is available in DAO, you know - although some things are often hidden away ...

I think you will find this one in the fields collection of the index.

the property you want is the attributes property of the relevant field of the index. Since attributes are additive, you need to mask it with the dbdescending constant

there are various other useful attributes, including dbautoincrfield (autonumber)

so something like

DescendingIndex = currentdb.tabledef("sometable").indexes("someindex").fields("somefield").attributes AND dbdescending = dbdescending

you will probably need to instantiate set db=currentdb to get this to work.

I may well be wrong, but I would be surprised if there is a plus or minus in the name to represent this attribute. That may just be a custom/usage used by whoever designed your table


Hit f1 while you are over attributes in the code editor, to get at the help ....

 

[vbaInet]

I may well be wrong, but I would be surprised if there is a plus or minus in the name to represent this attribute. That may just be a custom/usage used by whoever designed your table

It sure does have that prefix.

 

[gemma-the-husky]

It sure does have that prefix.

are we talking DAO (DAO 3.6 to be precise) or not here. I have just tried this with a table - added two indexes, both same single field, just ascending and descending

the indexes just show as (iterating tabledefs/indexes/fields collection)

indexname: DataUp: ..... field: "fieldname"
indexname: DataDown: ..... field: "fieldname"

no plus or minus.


I verified ascending/descending with

descending = fld.Attributes And dbDescending = dbDescending

 

[vbaInet]

Write it just like Solaris mentioned in post #3:

Currentdb.TableDefs("SomeTable").Indexes("SomeIndexName").Fields

Or

currentdb.TableDefs("SomeTable").Indexes(0).Fields

Just tested it on DAO 3.6.

 

[gemma-the-husky]

that's really odd - i thought fields was just a collection of the field items - but it seems to be both a collection and a multi-valued field list.

I was sure I had tried the exact syntax you quoted, and not got it to work - but obviously not. I can't get it to work with currentdb though - I have to set a database variable first.

Out of interest, I have O'Reilly DAO Object Model book (Jan2000 version), and I can't see the behaviour discussed in this thread described anywhere in the book.

 

[vbaInet]

that's really odd - i thought fields was just a collection of the field items - but it seems to be both a collection and a multi-valued field list.

That was my intial thought before my first post. I ran a quick code to check all the properties and values of the Indexes collection and I did notice the "+" prefix but didn't take note of it. Turned out to be meaningful.

Out of interest, I have O'Reilly DAO Object Model book (Jan2000 version), and I can't see the behaviour discussed in this thread described anywhere in the book.

I know the O'Reilly publications on other subjects/languages. I haven't really read or got a book on DAO or VBA for that matter