Access Form that uses SQL Server not sorting

[mdProgrammer]

Not sure if this goes under MS Access or MS SQL...
Not sure if this should go under SQL Server, MS Access, or even VB6 (if such a forum even still exists).

Here's a brief history here -

I have a client with a 15-year-old front end program (Asset Tracking) written in VB6, contains zero documentation, and the company that wrote it, is long disfunct. This program used to use tables in MS Access until they were moved to SQL server 2000 (yes, that will be upgraded eventually, too). On a side note, I may even wind up re-writing this if VB6 is not supported in the next version of Windows (or they will have to resort to using Microsoft Virtual PC). Now, here's where the problem lies -

There is a form that used to sort asset numbers for looking at asset history (asset numbers are not unique). I'm guessing this is because the original table had an index on the asset field.

(note, I haven't used indexing much in SQL Server, so I may or may not be doing this right) The table in SQL has a clustered index on the primary key. I tried to add an index for the asset field, but the form is still showing the numbers out of order. It seems that it's sorting the clustered index first, then the asset field.

I even tried selecting the asset field (with "Unique field" + "Ignore" selected and deselected) within the clustered index and moved it above the HistoryID field, and get this error -

Server: Msg 1907, Level 16, State2
Cannot re-create index... The new index definition does not match the constraint being enforced by the existing table.

What should I do to get this to work?

Now, this is assuming that the VB6 form is even looking at the table index in the first place (which I think it is, since when I select "Sort Order (DESC)", the asset order in the form list changes.

 

[mdProgrammer]

To add, I do not have the code for this form in VB6.

 

[RiverGuy]

The only way to reliably order results in SQL Server is to use an ORDER BY clause. SELECT * FROM Assets ORDER BY AssetNo

 

[gmmastros]

The notion of "supported" is a bit fuzzy. The VB6 IDE may not be supported in a (near) future operating system, but I am sure that the VB6 run-times will continue to be supported for a long time to come. This means... you will still be able to run a VB6 app on new operating systems. Also... even though it may not be supported, that does not imply it will suddenly stop working. It's like the warranty on your car. Your car won't stop running the minute it's no longer covered under warranty (even though it may seem like it).

Anyway.... I would suggest that you run profiler to see exactly what query the VB6 app is sending to the database. This may help you to determine the most appropriate course of action.

Some people are confused about indexes in SQL Server, and it seems like you may fall in to this category. Having an index does not guarantee anything. Yup. You heard it from me first. There are no guarantees. Often times, when you run a query against tables with a clustered index, you will get a result set that appears to be sorted. The only reason this happens is that it happens to be the fastest way to return the data. If SQL Server were to determine that there is another (faster) way to return the data, then the result set would no longer appear to be sorted.

The ONLY way to guarantee a sorted result is to use an ORDER BY clause on the query.

By putting additional (non-clustered) indexes on the table, you may speed up the query, but there still wouldn't be any guarantees about the sort order.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mdProgrammer]

It's been decided to just re-do this program as a web application since the client's been having other issues with it (not related to this issue). Changing indexes only met with limited results.

 

[JoeAtWork]

Changing indexes only met with limited results.

As George already pointed out, indexes have nothing to do with what order records are retrieved in. An ORDER BY clause is the only thing that will guarantee the sort order.