SQL way to specify multi-column index name as the ORDER BY?

[Galaxiom]

It is an old programmer's joke but very appropriate here.
There is only one problem with your code. The end is too far from the beginning.

As I asked in another thread, why do you do everything with loops?

When you go to work, do you walk into every building and ask the receptionist for the building name, test their reply against the one on your business card and only go to your office if they match?

 

[mdlueck]

As I asked in another thread, why do you do everything with loops?

There are two loops I (at quick glance) see in the code...

1) Finding the correct Querydef object. From other's posts, there is no way to directly get to a Querydef object you already know the name of. "Must loop" and check the name of each Querydef object.

2) Updating the UI controls... each form may have a variable number of controls to update. Each control needs to be updated. How would you do that WITHOUT a loop?

And your problem with my code is what exactly....??? (shrug)

 

[Galaxiom]

there is no way to directly get to a Querydef object you already know the name of. "Must loop" and check the name of each Querydef object.

Really? What about:

Set db = CurrentDb
db.QueryDefs("myquery").SQL = whatever

 

[mdlueck]

Really? What about:

Set db = CurrentDb
db.QueryDefs("myquery").SQL = whatever

Then I will consider that Rx as I rebuild the database.

I had inquired of a direct syntax and was told there was none.

Thanks!

 

[vbaInet]

1. QueryDefs is a collection. CurrentDb.QueryDefs("QueryName") will return the querydef object corresponding to query name. Loop is not required. In fact, in a lot of cases a loop isn't necessary.

Also, if you want to apply a sort on a form, you don't need to change the form's Record Source, you can simply use the OrderBy and OrderByOn properties of the form.

I've not yet scrutinised your code. I'm just making some comments based on your last post.

Edit: I'm a bit late there.

 

[Galaxiom]

And your problem with my code is what exactly....??? (shrug)

You mean aside from the unnecessary loops?

For example:

If strColName = strThisCol Then
        'Underline on
        MePointer.Controls("Label_rev").FontUnderline = True
        MePointer.Controls("Label_ver").FontUnderline = True
      Else
        'Underline off
        MePointer.Controls("Label_rev").FontUnderline = False
        MePointer.Controls("Label_ver").FontUnderline = False
      End If

An alternative:

 boolUnderline = (strColName = strThisCol) 
          With MePointer
             !Label_rev.FontUnderline = boolUnderline
             !Label_var.FontUnderline = boolUnderline
          End With

 

[mdlueck]

Also, if you want to apply a sort on a form, you don't need to change the form's Record Source, you can simply use the OrderBy and OrderByOn properties of the form.

But that seems to stick in the Form UI and either I reset it, or Forms end up opening / reopening in an undetermined state. So I changed the design to never use Form OrderBy properties, and even put my form "reset" code into the opening of each form, to really really really try to be sure Forms always open in a consistent state.

So yes, now that I have a "reset" at open, I could switch back... but why bother now? This works well.

 

[vbaInet]

But that seems to stick in the Form UI and either I reset it, or Forms end up opening / reopening in an undetermined state. So I changed the design to never use Form OrderBy properties, and even put my form "reset" code into the opening of each form, to really really really try to be sure Forms always open in a consistent state.

If you have other code that you reset in the Form's Open or Load event why not include that in there? Unless you haven't applied any code in that event.

 

[Galaxiom]

1. QueryDefs is a collection. CurrentDb.QueryDefs("QueryName") will return the querydef object

That doesn't work. It confuses many but the reason is simple.

CurrentDb is not an object but a Method of the Application object. (Application.CurrentDb)

Essentially a function that returns the object defined by Workspaces(0).Databases(0)

Consequently one must Set an object variable to the return value of the function. Then the QueryDefs collection of that object can be addressed.

 

[vbaInet]

I should have mentioned dbObject.QueryDefs("QueryName").

I expected mdlueck to understand what I meant seeing that he knows some coding principles. It was just pseudocode.

 

[Galaxiom]

I should have mentioned dbObject.QueryDefs("QueryName").

I expected mdlueck to understand what I meant seeing that he knows some coding principles. It was just pseudocode.

I know that you know but we have to be careful. Others see that kind of thing and wonder why it doesn't work for them.

It is also good opportunity to provide explanation for the casual reader to understand why we must set the object first. I know I used the code more than once while scratching my head as to why something so similar to so many other code samples didn't work for CurrentDb in a single line.

 

[mdlueck]

You mean aside from the unnecessary loops?

An alternative:

 boolUnderline = (strColName = strThisCol) 
          With MePointer
             !Label_rev.FontUnderline = boolUnderline
             !Label_var.FontUnderline = boolUnderline
          End With

Interesting technique...

The loop is still necessary to update all of the controls mentioned in the Array. So this does not remove the need for a loop, rather simplify if logic tree.

 

[jdraw]

As I see your code, you are using vba to construct the Order By. This is just the field names. Not the index name, at least as I see it.

You could have a unique index on 3 fields

UnqIndex of fld1 + fld2 + fld3

I don't know of any way to tell SQL to use UnqIndex. In my view, and I may be misguided, how the SQL processor behind Jet/Access determines what indexes exist, and then what indexes to use in actually performing the query, is an algorithm within the SQL processor. And not all processors work the same way. For example, you said Paradox offers a mechanism to identify an index....

After posting I saw this - it isn't Access /Jet but in concept terms I think this is the behind the scenes activity
http://www.simple-talk.com/sql/performance/index-selection-and-the-query-optimizer/

 

[vbaInet]

currentdb.TableDefs("TableName").Indexes(0).Name

...gives you the name of the first index in TableName.

 

[jdraw]

vbaInet,
Agreed that is the index name. Can you tell the sql processor, via SQL, to use that index? Or is it part of the SQL Processor to determine if an index exists that will be "beneficial" to the query involved, and to use it, or others, or create something on the fly?

Perhaps I'm misunderstanding the poster, but I think he is saying-- How do I tell "the SQL system" to use Index X?

 

[mdlueck]

currentdb.TableDefs("TableName").Indexes(0).Name

...gives you the name of the first index in TableName.

But can you switch to an index by name?

Oh oh oh, that is not even a form object, you are going right to the table, right?

"'tis a long way back... I will not change the design of my app at this point"

 

[Galaxiom]

The loop is still necessary to update all of the controls mentioned in the Array. So this does not remove the need for a loop, rather simplify if logic tree.

I didn't say that no loops were necessary, simply that you use them unnecsarily.

On the other thread I mentioned you used a loop to requery a form in exactly the same way as you looped through the querydefs collection. You replied saying that the same code had worked for you in many other places.

In the same thread you repeatedly called an Event Procedure an API. Bob Larson explained the difference to you in detail and you defended your terminology saying they were the same thing to you. And you are still calling your procedure an API on this thread.

You seem more interested in defending your way of doing things than actually learning. Little wonder you write clumsy code.

Contributors eventually lose interest in those who display that kind of attitude because we feel we are wasting our time explaining.

 

[mdlueck]

Perhaps I'm misunderstanding the poster, but I think he is saying-- How do I tell "the SQL system" to use Index X?

"Rx to switch the form UI sort based on a named index ala Paradox."

 

[vbaInet]

But can you switch to an index by name?

Oh oh oh, that is not even a form object, you are going right to the table, right?

"'tis a long way back... I will not change the design of my app at this point"

Indexes exist in the table so there's no way of referencing them without "calling" the table object. Indexes can take either the name (a string value) or the integer value. This really is a general programming concept.

@jdraw: The Jet Engine knows what fields are indexed based on the tables it's presented with. So no you can't instruct the query to use a particular index unless you alter the index in code before opening the query. I don't think this is what the OP is after though.

 

[vbaInet]

In the same thread you repeatedly called an Event Procedure an API. Bob Larson explained the difference to you in detail and you defended your terminology saying they were the same thing to you. And you are still calling your procedure an API on this thread.

Right there GalaxiomAtHome. It definitely causes a lot of confusion when incorrect terminologies are used.

Contributors eventually lose interest in those who display that kind of attitude because we feel we are wasting our time explaining.

We do lose interest.