Query - General - SQL error using ORDER BY

Not sure if anyone can assist with this, but here goes.

I am currently connecting to a DB2 Server from the Back End of an Access database using the following:

"SELECT BRANCHID, BRANCH from MDDT_BRANCHES IN 'MDDTS1.MDDDB;' [ODBC;DSN=MDDDB];"

This works fine as is, however I am trying to sort the results on the BRANCH field within this table with:

"SELECT BRANCHID, BRANCH from MDDT_BRANCHES ORDER BY BRANCH IN 'MDDTS1.MDDDB;' [ODBC;DSN=MDDDB];"

Doing this gives the following error:

"In operator without () in query expression 'BRANCH IN 'MDDTS1.MDDDB;' [ODBC;DSN=MDDDB]'."

Is anyone able to help me out with this as I get the same error if I attempt to use a WHERE function as well?

Any help is greatly appreciated.

Any reason you cannot use the Access link tool and then query through the link?

I am not familiar with using [ODBC;...] parameter you are employing, regardless, doesn't the ORDER BY BRANCH have to be at the end of the SQL?

Not sure what you refer to when you say "Access link tool"? But I am trying to avoid having any tables in this database, even linked ones, due to users who cant keep out....

Everything is being linked directly to the DB2 server, and I am trying to pass ALL Processing directly to the server as this database is currently being used in 300+ locations by 500+ concurrent users.

I know that Access is not ideal for this, and up until now, it has all sat within the Access Database itself. I have now been asked to migrate the data and processing to a secure DB2 server (for obvious reasons), but for now, we are still limited to using an Access Application.

I just cant seem to get some of what is usually a simple function to work when I include the ODBC settings in the SQL string?

You are using code in an Access backend to connect to and query DB2 tables? Access has a tool to do the linking then you can query through the link. Are you using Access2007 - on the ExternalData tab select More/dBase. Users can't modify the structure of the linked table, just the data. However, if you still want to do it in code as recordset, did you try the ORDER BY at the end of the SQL string?

Could set up the backend so users can't see the navigation pane when opening (unless they hold the shift key - shhhh!)

We are using Access 2002, and I have tried using ORDER BY at the end of the SQL. I also have the same issue with the WHERE function, and have not been able to get any sort of resolution.

I can hide the Nav Pane when opening, and can also Disable the Shift Key, but this then makes it difficult if I need to get in to make alterations. ( you would also have to hide the Toolbar so they cant use the "Show Database Window" button).

When you setup project with restrictions like hiding the navigation pane and toolbars, can override these project-specific settings by pressing the shift key when executing opening file, at least this works in 2003 and 2007. Then you are free to edit at will.

Stumped on the SQL issue. Suggest you try the Access link utility just as a test, you can eliminate it later.