Access pass-through query datasheet view sort fails but some work

Question

I am working on a project migrating the back-end of an Access system to SQL Server.  One of the processes is to do a query with no order by clause and let the user sort the data the way they want using the Access query datasheet view sort facilities.  Some of the pass-through queries process the user-selected sorts but others get the "The Microsoft Access database engine cannot find the input table or query <name of SQL Server view being used>.  Make sure it exists and that its name is spelled correctly." error message.  The queries themselves run fine so I am a bit confused how it can run the pass-through using a view than when it tries to sort via the UI it can't find it.

For example the following pass-through query runs find and allows the user to sort any field:

SELECT Contractor, [Total Commitments]

FROM DI_OCE_CommitmentsView;   <-- SQL Server view

but the following runs fine but does not allow the user to sort any field and if attempted gets the error mentioning above (simplified to the simplest case) :

SELECT ProjectID

FROM EECBG_DatesView;  <-- SQL Server view

Of course the queries go after different data on the SQL Server side but Access should not care.

Using Access 2013, ODBC SQL Server Native Client 11.0, and SQL Server 2012 Express.

Any thoughts on what could make Access do this would be great.

Thanks.

Answer 1

Not an answer but a work-around.  I discovered if I have a regular Access query use the pass-through query as its source, I am able to use the the Access query UI to sort by different columns.  It adds one level of indirection and I am not sure of the inefficiencies as a result of this indirection, but it seems to work and is acceptable in my case.