Pass-through query not sorted

[seaport]

A table tblCompany is in a SQL server databaes. A view vwCompany is created as "select companyid, companyname from tblcompany order by companyname." A pass-through query in Access is created as "select * from vwCompany."

However, the pass-through query is sorted by the CompanyID, not CompanyName.

Could anyone help me figuring this out?

I can certainly create a query in Access to sort the pass-through query. But I think this is not the right way to do it since I want to leave the processing job on the server as much as possible.

Thanks in advance.

Seaport

 

[dhookom]

I wasn't aware that a view could be sorted without specifying [blue] TOP 100 PERCENT[/blue]. Are you seeing this order in the datasheet of the pass-through?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[seaport]

dhookom,

I tried to simply my question but I missed some important info.

Actually, the view vwCompany is "select top 100 percent companyid, companyname, city, state from tblcompany order by companyname".

The company table has one cluster index - CompanyID, and one non-cluster index on CompanyName, City, and State combined.

Forget about the pass-through query. When I ran "select * from vwCompany" in SQL server (2005), the result was not sorted at all. Then I thought it must have something to do with the index. So I re-created the non-cluster index as CompanyName, City, State, and CompanyID combined. The problem was solved.

However, I was still confused. Based on my limited knowledge on using index, a non-cluster index does not need to include the primary key. Am I right?

Seaport

 

[dhookom]

I'm not sure what is going on. My expectation would be the same as yours.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[seaport]

I just found out that the primary key of the company table was set up wrong. Stupid me.

Seaport