+1

Micromine's ODBC Linking: Defining an order when creating buidling a database

Andrew Esmaili 9 years ago in General updated by luke burlet 5 years ago 2

When you use Micromine’s ODBC link (not the Microsoft Access link)
you can include an SQL instruction to sort the data. This should prevent any problems with order/sequence of the data. 


At the bottom of the ODBC dialog window a SQL instruction can be added to sort the data by  toggling on the "Append a clause to the SQL statement” option box and adding a Sort instruction at the end of the SQL query.


An example of a ANSI-standard SQL syntax database clause is:


ORDER BY HOLE,FROM;


The above example sorts the data by the fields "HOLE" and "FROM" respectively. It is a useful example as the majority of sorting issue Micromine users have faced has been resolved by adding this clause to this clause to the end of the SQL query.



Image 107

This is very helpful since in two recent cases a client has taken the concept of relational database--all tables tied by the key index hole_id field--to extremes, not bothering to sort anything in the database. My opinion is that this is not good practice, but it nevertheless is how I receive their data. This short SQL instruction will make ODBC linking possible. Is it possible to add the capability to sort an ACCESS link as well? This is the product I am usually given for linking or importing.

hi Donald - not sure if you have sorted this out yet, but you can create a ODBC link (instead of a Access one) to your MSAccess file and then use the above as Andrew has noted. I find the ODBC link to a MDB/ACCDB is more stable and MM tech support put me on to it. Alternatively, you could try creating a select query in the MDB/ACCDB that has a sort built in  (HoleID, From etc) and then link that query instead of the table. This still does to work for me, I just tried it now ... but MM may ignore the sort part of the query so use with a grain of salt ...

...